In MS Flow is it possible to convert CSV into Excel

[Ahammad_Riyaz]

Hi All,

I have Some scenario i need to convert csv file into Excel file, without using premium connectors like Encodian and Plumsail Documents connector.

If anyone knows better solution let me know.

Thanks,

Ahammad Riyaz

[tom_riha]

Hello @Ahammad_Riyaz ,

@DamoBird365 has a video where he does that using Office scripts: https://www.youtube.com/watch?v=ZejstvPiFZw

[Rhiassuring]

Hi @Ahammad_Riyaz 

Yes, this is achievable - I spent way too long testing this out on my side, haha.  I am tired, so this is going to be a quick screenshot dump - with probably a lot of inelegance, as I was just going for function!

My demo CSV...

To start, we get the File Content from your CSV file.
Then, we start processing the File Content. I used these actions:

Split by NewLine: I'm replacing the newlines with a dollar sign, and then splitting on them.

split(replace(outputs('Get_file_content_using_path')?['body'],decodeUriComponent('%0D%0A'),'$'),'$')

Filter array - remove nulls: just what it sounds like. Looking at Compose - Split by NewLine's outputs:

@equals(empty(item()), false)

Compose - get array header values and split on comma

split(first(body('Filter_array_-_remove_nulls')),',')

Compose - array without the header values

skip(body('Filter_array_-_remove_nulls'),1)

Now that we have that going, time to set up the Excel doc.

Create an Excel file, create a Table (using a variable so you have the name, and setting the range to A1), then we use our previous Compose to get the column names, and drop them into the field.

(Note: in my test I did it in a dumb way, so no screenshots, but what you'd do here is just turn your "Compose - get array header values and split on comma" into a string variable and plop them in there.

Now, from here, it's pandemonium. 

In an "Apply to each", I am using the Outputs from "Compose - array without headers..". 

First, I split them by comma so I have them individually. 

Then, in another "Apply to Each" focused on the output from that split, I use the Append to String to build out my values. I set it to 

"outputs('Compose_-_Get_array_header_values_and_split_on_comma')[variables('intIncrement')]": "outputs('Compose_-_Clean_out_the_other_linebreak')" and then I increase my intIncrement by 1. I use that to ensure it goes into the right column.

Theeeeeeen before the Apply to Each starts over for another row, I clear the vars. 

I think the Office Script that @tom_riha mentioned sounds better, but, this was still fun to try.

R

[Ahammad_Riyaz · Accepted solution]

Hi All,

Thanks for all your responses.

I got the solution from this link, https://www.tachytelic.net/2021/02/power-automate-parse-csv/

Not much steps to follow.

[Rhiassuring]

@Ahammad_Riyaz  That solution will only work for you if you have the same column titles in your CSV every time. Is that the case? 

[Paulie78]

Hi @Ahammad_Riyaz great to hear you were able to use my blog post to help you.

You might also want to check this one out to make your flow more efficient when putting the data into Excel:

https://www.tachytelic.net/2021/08/power-automate-export-to-excel/ 

[Ahammad_Riyaz]

Yes, for me column name will be constant.

[takolota]

You can now try automatically converting most CSVs to a new Excel table with the same column names as the CSV headers with this template: 

https://powerusers.microsoft.com/t5/Power-Automate-Cookbook/CSV-To-New-Excel-Table/m-p/1826096#M964