cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Ahammad_Riyaz
Super User
Super User

In MS Flow is it possible to convert CSV into Excel

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

1 ACCEPTED SOLUTION

Accepted Solutions
Ahammad_Riyaz
Super User
Super User

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.

View solution in original post

6 REPLIES 6
tom_riha
Super User
Super User

Hello @Ahammad_Riyaz ,

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



[ If I have answered your question, please Accept the post as a solution. ]
[ If you like my response, please give it a Thumbs Up. ]

[ I also blog about Power Automate solutions even for non-IT people. ]
Rhiassuring
Community Champion
Community Champion

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

Rhiassuring_0-1650517025713.png

Rhiassuring_1-1650517036193.png

 

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

 

Rhiassuring_1-1650526810847.png

 

 

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.
 
Rhiassuring_2-1650527116837.png

 

Rhiassuring_3-1650527304816.png

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

 

Rhiassuring_4-1650527344239.png

 

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

R

Ahammad_Riyaz
Super User
Super User

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
Community Champion
Community Champion

@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
Super User
Super User

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/ 

Yes, for me column name will be constant.

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

New Ideas Forum MPA.jpg

A new place to submit your Ideas for Power Automate

Announcing a new way to share your feedback with the Power Automate Team.

MPA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

MPA Licensing.jpg

Ask your licensing questions at the Power Automate AMA!

Join Priya Kodukula and the licensing team, super users and MVPs to find answers to your questions on Power Automate licensing.

Users online (1,483)