Answered

Copying rows from newly created Excel File to Master File

(0) Share

Report

Posted on by Bigshow

Im trying to find a way to take the rows created in a new excel file and copy them across to a master excel file. Files can be stored in Sharepoint or one drive.

If i name the source file the this works. But the source files will have dynamic name and obviously dont exist yet, so i cant add the column names to the flow.

Any ideas how to achieve this?

Phil

Categories:

Building flows

I have the same question (0)

All responses (15)

Answers (2)

Sort by

CS-31031032-0 on at

Like (0)

Report
Copy link

Link copied!
Let me try...

For your source file, do a Create Table step.
Have the File name as a dynamic content.
Table range: give a range that you're sure will cover your entire data.

Next step is "List all rows present in a table" But this will also include blank rows, so, to just get the rows that have content, proceed to #3.
Do a "Filter Array" from the previous step's value.
Column names won't be available, so you'll have to manually type in the expressions:
trim(item()?['column_name_here'] is NOT equal to <leave blank>
Now you can do an "Apply to Each"
From: Body -> output of Filter Array

4.1 Add a row in the table (to your master file)
Note when assigning values to each column in "Add Row To Table", use the expression:

items('Apply_to_each')?['put_your_column_name_here']

Was this reply helpful? Yes No
Bigshow 60 on at

Like (0)

Report
Copy link

Link copied!

Thank you, it’s is possible to produce a screen shot of how this flow would look?

Was this reply helpful? Yes No
Verified answer

CS-31031032-0 on at

Like (0)

Report
Copy link

Link copied!

Note that "Table1" is entered as custom value in the first 2 steps

Was this reply helpful? Yes No
Bigshow 60 on at

Like (0)

Report
Copy link

Link copied!

Thank you again.
Please fogive me as im a bit of a newbie... but i would like this triggered by the new excel file being created in the one drive folder. Do i just add this trigger to the start or are there other steps that would be needed to be added as well....

TIA

Was this reply helpful? Yes No
Verified answer

CS-31031032-0 on at

Like (1)

Report
Copy link

Link copied!

Oh! an easier alternative is before you process your source file which varies in filename, maybe you can just take that source file, copy-overwrite it into a "staging file", which will have a static filename. That way, while you are creating the flow, all the columns are available and you don't have to use expressions heavily.

Was this reply helpful? Yes No
Bigshow 60 on at

Like (0)

Report
Copy link

Link copied!

Brillant! Thank you!

Was this reply helpful? Yes No
CS-31031032-0 on at

Like (0)

Report
Copy link

Link copied!

Am glad you're happy! I'd be too if you mark my response! 😉

As for your trigger question, yes i think it's pretty much straightforward. but if you are doing the "staging file" alternative, you should add a condition right after the trigger. so that if the detected "file created" is just the overwritten "staging file", the flow must Terminate (this is a step) - to avoid infinite loops due to this flow triggered by a file create, but it also creates a file. 😄

P.S. I too am still learning Flow. I was just told that one way of learning would be participating in communities like this and trying to solve other people's problems. That way, I get exposed to real scenarios. Maybe this works for you too! 😉

Was this reply helpful? Yes No
Bigshow 60 on at

Like (0)

Report
Copy link

Link copied!

Just a quick question. How do i rename the file? in the desitnation file path i put the dynamic filepath/stagingfile.xlsx but it errors out with the path\orginalfile.xlsx/stagingfile.xlsx

Was this reply helpful? Yes No
CS-31031032-0 on at

Like (0)

Report
Copy link

Link copied!

i havent tried renaming files yet. But if that error is on the step itself on the flow IDE, maybe be sure to choose “enter custom value” first? Or if it errors on runtime, maybe the file path format is incorrect. Check the run logs for the input and output values. Also it might be better to COPY over the staging file, not rename. This is so that you can still preserve your source file.

Was this reply helpful? Yes No
Bigshow 60 on at

Like (0)

Report
Copy link

Link copied!

I have this working using your “staging file” idea. One thing I had to do is point the “listing rows” step to the staging file, then this would allow me to add the columns in the “add row” step. Once that’s done I went back to the list rows step and replaced the file name with the “file locator” dynamic

This works fine now, apart from if flow is run again to quickly, it errors out with a file lock

Was this reply helpful? Yes No