cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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.
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.
6 REPLIES 6
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.
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

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.
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)?

 

Helpful resources

Announcements
firstImage

Microsoft Business Applications Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

Power Platform 2019 Release Wave 2 Plan

Power Platform 2019 Release Wave 2 Plan

Features releasing from October 2019 through March 2020

thirdimage

Flow Community User Group Member Badge

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

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 Flow Community Video Gallery!

Top Kudoed Authors
Users Online
Currently online: 42 members 4,907 guests
Please welcome our newest community members: