cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Bigshow
Helper II
Helper II

Copying rows from newly created Excel File to Master File

Hi

 

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

2 ACCEPTED SOLUTIONS

Accepted Solutions
chrissahagun
Resolver II
Resolver II

chrissahagun_2-1616170840463.png  Note that "Table1" is entered as custom value in the first 2 steps

chrissahagun_3-1616170902241.png

 

View solution in original post

chrissahagun
Resolver II
Resolver II

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.

View solution in original post

13 REPLIES 13
chrissahagun
Resolver II
Resolver II

Let me try...

 

  1. 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.
         chrissahagun_1-1616168949176.png

     

  2. 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.
  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>
  4. 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']

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

chrissahagun
Resolver II
Resolver II

chrissahagun_2-1616170840463.png  Note that "Table1" is entered as custom value in the first 2 steps

chrissahagun_3-1616170902241.png

 

Bigshow
Helper II
Helper II

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

chrissahagun
Resolver II
Resolver II

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.

Brillant! Thank you!

chrissahagun
Resolver II
Resolver II

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! 😉

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

 

 

chrissahagun
Resolver II
Resolver II

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.

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

 

chrissahagun
Resolver II
Resolver II

There’s a Delay step... you can have the flow wait for a few seconds before proceeding to the next step

Rather than a delay step I changed the settings of the copy step. I put in a retry count of 5 with a 5 minute interval. This seems to work ok

chrissahagun
Resolver II
Resolver II

Nice. Didn’t know we could do that. Now I do. Thanks!

Helpful resources

Announcements
Power Automate News & Announcements

Power Automate News & Announcements

Keep up to date with current events and community announcements in the Power Automate community.

Power Automate Community Blog

Power Automate Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Users online (3,323)