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

[Kani]

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

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

Source & Destination file

Highly appreciate any help on this.  Thanks in advance.

[v-xida-msft · Accepted solution]

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:

The flow works successfully as below:

Best regards,

Kris

[Kani]

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.

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

               After flow run the Output is  as below. 

My expected output is something like below. 

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

Thanks

[v-xida-msft · Accepted solution]

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:

Best regards,

Kris

[Kani]

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

[BobG]

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]

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