cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Kani
Level: Powered On

Get rows from Excel file run time and insert it in another Excel file in One Drive for Business

Hello Everybody,

Below is the scenario which I am trying to create a flow and got stuck up in it.

 

When a file is created in one drive in a path -> Get rows from the created excel file -> Insert that rows into destination excel file in One Drive for business in another path

 

This is my flow.

 Flow.jpgFLow

 

When I run the flow, it is successful but rows are not getting updated in destination file.

 

My questions are:

1. I am not sure of how to map the columns between source and destination files.

2. In Get rows action, how can I restrict to read only the rows with data. Here it reads the entire table even without data in it.

Flow Output.jpgFlow Output

 

Dest.jpgSource & Destination file

 

Highly appreciate any help on this.  Thanks in advance.

2 ACCEPTED SOLUTIONS

Accepted Solutions
Community Support Team
Community Support Team

Re: Get rows from Excel file run time and insert it in another Excel file in One Drive for Business

Hi @Kani,

 

Do you want to insert rows of one file to corresponding columns of another file?

For your first question, you could use an expression to map the columns between source and destination files.

 

For your second question, you could add a “Filter array” action to filter the rows with data.

Please take a try with the following workaround:

  • Add “When a file is created” action, specific Folder
  • Add “Get rows” action,
  • Add “Filter array” action, From set to output of “Get rows” action, click “Edit in advance mode”, type the following formula:
@and(not(empty(item()?['Col1'])),not(empty(item()?['Col2'])),not(empty(item()?['Col3'])) )
  • Add “Apply to each”, input parameter set to output of “Filter array” action.
  • Within “Apply to each”, add “Insert row” action. Col1 set to “@item(‘Col1’)”, Col2 set to “@item(‘Col2’)”, Col3 set to “@item(‘Col3’)”.

Image reference:3.JPG

 

4.JPG

 

The flow works successfully as below:5.JPG

 

Best regards,

Kris

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Highlighted
Community Support Team
Community Support Team

Re: Get rows from Excel file run time and insert it in another Excel file in One Drive for Business

Hi @Kani,

 

Please take a try with the following steps:

  1. In "Insert row" action, click "Add dynamic content" of Col1(Col2,Col3) input box.
  2. In right panel, select Expression tab. In the formula box, type the following formula:
    item()?['Col1']
  3. Click Ok button.
  4. Repeat above steps in Col2 and Col3 input box.

Image reference:3.JPG

 

 

Best regards,

Kris

 

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

7 REPLIES 7
Community Support Team
Community Support Team

Re: Get rows from Excel file run time and insert it in another Excel file in One Drive for Business

Hi @Kani,

 

Do you want to insert rows of one file to corresponding columns of another file?

For your first question, you could use an expression to map the columns between source and destination files.

 

For your second question, you could add a “Filter array” action to filter the rows with data.

Please take a try with the following workaround:

  • Add “When a file is created” action, specific Folder
  • Add “Get rows” action,
  • Add “Filter array” action, From set to output of “Get rows” action, click “Edit in advance mode”, type the following formula:
@and(not(empty(item()?['Col1'])),not(empty(item()?['Col2'])),not(empty(item()?['Col3'])) )
  • Add “Apply to each”, input parameter set to output of “Filter array” action.
  • Within “Apply to each”, add “Insert row” action. Col1 set to “@item(‘Col1’)”, Col2 set to “@item(‘Col2’)”, Col3 set to “@item(‘Col3’)”.

Image reference:3.JPG

 

4.JPG

 

The flow works successfully as below:5.JPG

 

Best regards,

Kris

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Kani
Level: Powered On

Re: Get rows from Excel file run time and insert it in another Excel file in One Drive for Business

Thanks so much for your reply.

 

Half of the flow is working fine now. But still I didn’t get the expected output. Still see problem in the insert row action.

Have tried with two ways.

  1. Simply selecting the item() from expression for the columns (as shown in your screenshot)

                After flow run the Output is as below. Entire values got inserted in each column.Way1 Output.jpg

 

    2. Directly typed the value @item(‘Col1’) in the respective columns in Insert Row action

               After flow run the Output is  as below. 

Way2 Output.jpg

     

My expected output is something like below. 

Expected Output.jpg

 Sorry if i have understood your solution wrongly. Please shed some more light on fixing Insert row action to get the desired output.

 

Thanks

Highlighted
Community Support Team
Community Support Team

Re: Get rows from Excel file run time and insert it in another Excel file in One Drive for Business

Hi @Kani,

 

Please take a try with the following steps:

  1. In "Insert row" action, click "Add dynamic content" of Col1(Col2,Col3) input box.
  2. In right panel, select Expression tab. In the formula box, type the following formula:
    item()?['Col1']
  3. Click Ok button.
  4. Repeat above steps in Col2 and Col3 input box.

Image reference:3.JPG

 

 

Best regards,

Kris

 

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Kani
Level: Powered On

Re: Get rows from Excel file run time and insert it in another Excel file in One Drive for Business

Thanks a lot for the detailed steps. It worked fine now.

BobG
Level: Powered On

DRe: Get rows from Excel file run time and insert it in another Excel file in One Drive for Business

Kris, I'm trying to solve a problem somewhat similar to that of the Subject matter. The difference is that, after using a Flow to get the contents of a row from an Excel spreadsheet (on a OneDrive), I'd like to pass the values to a Pwr App app as opposed to inserting them into the Excel file.  

Note: I've been able to update my Excel spreadsheet from my app using a flow so I know that the connection to the spreadsheet on the OneDrive is working and that my app is, for that function, working as well.   Right now it is a simple test Excel spreadsheet with 4 columns (plus the Rowid column) with 12 rows and a header.

Your help would be greatly appreciated.   Your instructions on the subject matter referenced were extremely clear and easy to understand.

Cheers,

Bob G.

hjaf
Level: Powered On

Re: Get rows from Excel file run time and insert it in another Excel file in One Drive for Business

Is this possible on using the Excel Online (Business) connector? I have files in a sharepoint location and the result keeps being

{
    "status": 400,
    "message": "The expression \"drives('...XXXX...')/items/Path/To/File.xlsx\" is not valid.\r\nclientRequestId: ..XXX-XX..",
    "source": "excelonline-ne.azconn-ne.p.azurewebsites.net"
}

I have tried to use this connector with runtime variables before without success, because of a known limitation where the parameters could only be set at designtime. I hoped they fixed this limitation as I saw this post, but your solution only works with the Excel Online (OneDrive) connector and not the  Excel Online(Business)?

 

abigailudo
Level: Power Up

Re: Get rows from Excel file run time and insert it in another Excel file in One Drive for Business

help i cant find the Excel connector and the  Excel- Get rows in microsoft flow  Action 

 
 

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

firstImage

Incoming: New and improved badges!

We've given our badges an overhaul and also added some brand new ones!

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

sixthImage

Power Platform World Tour

Find out where you can attend!

seventhimage

Webinars & Video Gallery

Watch & learn from the Power Automate Community Video Gallery!

Top Kudoed Authors (Last 30 Days)
Users online (7,805)