Copying rows from newly created Excel File to Master File

[Bigshow]

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

[chrissahagun]

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

[Bigshow]

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

[chrissahagun · Accepted solution]

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

[Bigshow]

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 · Accepted solution]

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.

[Bigshow]

Brillant! Thank you!

[chrissahagun]

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

[Bigshow]

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]

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.

[Bigshow]

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]

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

[Bigshow]

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]

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