Copy multiple rows from CSV to existing Excel table

[Dark4499]

Hi all,

I am setting up a flow to copy data from a CSV and populate it into a table in an existing Excel.
So far I already have the below/attached flow.

This has worked without any problems as our CSV files contain only one line.

However, now we also get files where there are more than one line of data in them.

The number varies each time. Sometimes it can be just one line, other times it can be 100.

So now I am looking for a method to make it possible for all the rows to be copied & completed.

Does such a method exist? Does anyone have an idea how I can go about this?

I thought about using "Apply to each", but this fails in combination with the "initialise variable" actions.

Thanks in advance for your help!!

[v-mengmli-msft]

Hi @Dark4499 ,

Could you show me the CSV table?

Looking forward to your response!

Rimmon

[Dark4499]

Hi @v-mengmli-msft 

Sure, pls find below.
I did anonymise the data somewhat, but file always follows the following format.

[v-mengmli-msft]

Hi @Dark4499 ,

I'm guessing that your current flow is slicing through the data in the CSV file and then putting the data obtained in each column into the corresponding Excel table, but this is difficult to do when there are multiple rows of data in the CSV table.

Now, what you need is:

When there are multiple rows in a CSV file, save the data to an Excel table, then we can understand it as convert the CSV file into an Excel file.

Reference documentation on this method:

How to convert CSV files to Excel in Power Automate Flow, Azure Logic Apps, and PowerApps — Plumsail...

Thank you for your reply and look forward to your feedback!

Best regards,

Rimmon

[Dark4499]

Hi @v-mengmli-msft ,

Yes indeed, current flow separates data based on the ";" & then adds the data into the corresponding column in the table.

I am afraid your proposed solution will not work, as I cannot use paid APIs.

Would there be an alternative solution?

I was possibly thinking of this solution:
https://learn.microsoft.com/en-us/power-automate/desktop-flows/how-to/convert-csv-excel

But then it would be necessary to set up a this flow so that it converts the last document added, and not one specific document as now.

Appreciate your help.

[grantjenkins · Accepted solution]

Assuming you have a comma separated CSV file and don't have commas within your values then this should get what you're after.

Below is the CSV I'm using for this example.

And I'm assuming you already have an Excel file with a Table setup. For this example I created the following Excel Table.

See full flow below. I'll go into each of the actions. Note that I've just used a manual trigger and getting the CSV file directly from SharePoint. I'm assuming your trigger is when a file is created in OneDrive and you then get the content from the trigger. The concept should be exactly the same once you have the CSV content.

Get file content using path retrieves the CSV file from my SharePoint Library. You would use your Get file content for OneDrive here.

Compose converts the CSV content to plain text using the following expression. I'm assuming you are doing the same thing here.

base64ToString(outputs('Get_file_content_using_path')?['body']?['$content'])

Filter array splits the data by carriage return and new line, then uses an expression to remove any empty rows.

//From
split(outputs('Compose'), decodeUriComponent('%0D%0A'))

//Condition
item()

Select uses the output from Filter array and the following expressions. Note that I'm splitting each value by comma. You can just as easily split on semi-colon if that's how your CSV is delimited. You can also use whatever column headers you want. I've just use Col1, Col2, etc. as didn't know what headers were in your Excel file.

split(item(), ',')?[0]
split(item(), ',')?[1]
split(item(), ',')?[2]
split(item(), ',')?[3]
split(item(), ',')?[4]
split(item(), ',')?[5]
split(item(), ',')?[6]
split(item(), ',')?[7]

If we ran the flow now the Select would get the following output.

Apply to each uses the output from our Select so it iterates over each of the objects.

Add a row into a table outputs the data from each row using the following expressions. Note that the names I use here need to match the headers we used in our Select.

item()?['Col1']
item()?['Col2']
item()?['Col3']
item()?['Col4']
item()?['Col5']
item()?['Col6']
item()?['Col7']
item()?['Col8']

After running the flow, all the rows of data should be saved into our Excel table as shown below.

[Dark4499]

Hi @grantjenkins 

That is exactly what I was looking for!

I implemented your solution, with some small changes, and it works perfectly!

Thanks a lot!

[smeadowstci]

Fantastic @grantjenkins.

I used your basic approach but since my column names exist in the first row, I used the following code to name each of the Array's fields dynamically (rather than the approach of Col'x').  (Where FilterArrayOfCSVRows contains the array of Rows created from the original CSV - which is the analog of your "Filter_array" action.)

                    "SelectNonBlankRowsToTabularArray": {

                        "inputs": {

                            "from": "@body('FilterArrayOfCSVRows')",

                            "select": {

                                "@{split(outputs('FilterArrayOfCSVRows')['body'][0],',')[0]}": "@Split(item(),',')[0]",

                                "@{split(outputs('FilterArrayOfCSVRows')['body'][0],',')[10]}": "@Split(item(),',')[10]",

                                "@{split(outputs('FilterArrayOfCSVRows')['body'][0],',')[11]}": "@Split(item(),',')[11]",

                                "@{split(outputs('FilterArrayOfCSVRows')['body'][0],',')[12]}": "@Split(item(),',')[12]",

                                "@{split(outputs('FilterArrayOfCSVRows')['body'][0],',')[13]}": "@Split(item(),',')[13]",

                                "@{split(outputs('FilterArrayOfCSVRows')['body'][0],',')[14]}": "@Split(item(),',')[14]",

                                "@{split(outputs('FilterArrayOfCSVRows')['body'][0],',')[15]}": "@Split(item(),',')[15]",

                                "@{split(outputs('FilterArrayOfCSVRows')['body'][0],',')[16]}": "@Split(item(),',')[16]",

                                "@{split(outputs('FilterArrayOfCSVRows')['body'][0],',')[17]}": "@Split(item(),',')[17]",

                                "@{split(outputs('FilterArrayOfCSVRows')['body'][0],',')[18]}": "@Split(item(),',')[18]",

                                "@{split(outputs('FilterArrayOfCSVRows')['body'][0],',')[19]}": "@Split(item(),',')[19]",

                                "@{split(outputs('FilterArrayOfCSVRows')['body'][0],',')[1]}": "@Split(item(),',')[1]",

                                "@{split(outputs('FilterArrayOfCSVRows')['body'][0],',')[20]}": "@Split(item(),',')[20]",

                                "@{split(outputs('FilterArrayOfCSVRows')['body'][0],',')[21]}": "@Split(item(),',')[21]",

                                "@{split(outputs('FilterArrayOfCSVRows')['body'][0],',')[22]}": "@Split(item(),',')[22]",

                                "@{split(outputs('FilterArrayOfCSVRows')['body'][0],',')[23]}": "@Split(item(),',')[23]",

                                "@{split(outputs('FilterArrayOfCSVRows')['body'][0],',')[24]}": "@Split(item(),',')[24]",

                                "@{split(outputs('FilterArrayOfCSVRows')['body'][0],',')[25]}": "@Split(item(),',')[25]",

                                "@{split(outputs('FilterArrayOfCSVRows')['body'][0],',')[26]}": "@Split(item(),',')[26]",

                                "@{split(outputs('FilterArrayOfCSVRows')['body'][0],',')[27]}": "@Split(item(),',')[27]",

                                "@{split(outputs('FilterArrayOfCSVRows')['body'][0],',')[28]}": "@Split(item(),',')[28]",

                                "@{split(outputs('FilterArrayOfCSVRows')['body'][0],',')[29]}": "@Split(item(),',')[29]",

                                "@{split(outputs('FilterArrayOfCSVRows')['body'][0],',')[2]}": "@Split(item(),',')[2]",

                                "@{split(outputs('FilterArrayOfCSVRows')['body'][0],',')[30]}": "@Split(item(),',')[30]",

                                "@{split(outputs('FilterArrayOfCSVRows')['body'][0],',')[31]}": "@Split(item(),',')[31]",

                                "@{split(outputs('FilterArrayOfCSVRows')['body'][0],',')[32]}": "@Split(item(),',')[32]",

                                "@{split(outputs('FilterArrayOfCSVRows')['body'][0],',')[33]}": "@Split(item(),',')[33]",

                                "@{split(outputs('FilterArrayOfCSVRows')['body'][0],',')[34]}": "@Split(item(),',')[34]",

                                "@{split(outputs('FilterArrayOfCSVRows')['body'][0],',')[3]}": "@Split(item(),',')[3]",

                                "@{split(outputs('FilterArrayOfCSVRows')['body'][0],',')[4]}": "@Split(item(),',')[4]",

                                "@{split(outputs('FilterArrayOfCSVRows')['body'][0],',')[5]}": "@Split(item(),',')[5]",

                                "@{split(outputs('FilterArrayOfCSVRows')['body'][0],',')[6]}": "@Split(item(),',')[6]",

                                "@{split(outputs('FilterArrayOfCSVRows')['body'][0],',')[7]}": "@Split(item(),',')[7]",

                                "@{split(outputs('FilterArrayOfCSVRows')['body'][0],',')[8]}": "@Split(item(),',')[8]",

                                "@{split(outputs('FilterArrayOfCSVRows')['body'][0],',')[9]}": "@Split(item(),',')[9]"

                            }

                        },

                        "runAfter": {

                            "FilterArrayOfCSVRows": [

                                "Succeeded"

                            ]

                        },

                        "type": "Select"

                    }

                }