cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
DevadathanK
Advocate I
Advocate I

Alternate way to convert a CSV (.csv) file to an EXCEL file (.xlsx) without using Encodian, Plumsail documents or PREMIUM connectors etc.

Hi Community!

 

I am trying to find a solution to convert a CSV file to an Excel, but all the direct steps that I can find are of Encodian, Plumsail Documents or other PREMIUM connectors. I could not find any other result that could help me so I'm hereby posting this query. 

 

My target is to convert a CSV file(s) to an Excel format by using Power Automate. The CSV files contain data in a table like structure with headers but I don't think a CSV file identifies them as Tables. 

So in theory, I require two steps -

 

Step 1- I place a couple of CSV Files into a SharePoint Folder

Step 2 - I need to get these files as .xlsx (Excel) files in another SharePoint Folder 

 

The Steps that I tried/created in Power Automate are -

 

Step 1 (Trigger) -  When a file is created in a folder (SharePoint)

Step 2 - Create File (SharePoint) 

In this step, I gave the Site Address and Folder Path as Static items.

The File Name and File Content I gave as Dynamic content from the earlier step( I added the extension with File Name as <Dynamic File Content>.xlsx, so that the result file would be excel.)

 

The result I got was an excel file, but the name was 'Test.csv.xlsx' and the file was corrupted. 

I tried to remove the dynamic content from the name and simply gave it as 'One.xlsx' 

This time I got the file as 'One.xlsx' but the file was still corrupted. 

 

I also tried some other ways by using Create table function and Create an Excel file, but none of them turned out well.

 

Other articles in the community are from 2017's and 2019's and in them I could not find a definite answer to weather I can achieve this by not using any PREMIUM Connectors. 

 

If you know of a way to accomplish this, Directly or indirectly, Please let me know. It would be a big help!

 

Thanks in advance. 

 

1 ACCEPTED SOLUTION

Accepted Solutions

Thank you @Paulie78 

I went through your video. Thank you for the clear explanation about converting the CSV data to JSON format. 

 

In addition to the steps you showcased in the video (https://www.youtube.com/watch?v=sXdeg_6Lr3o), I added another Compose step after the 'Get File Content' to convert it into String (As the step I used is of SharePoint and not OneDrive, it threw me an error stating that the output of the 'Get File Content' was an object).

 

After the step where we parsed the data as a JSON, I added a Loop (Apply to Each) using the Body from the Parse JSON step and then in the loop added a step to 'Add a row into a table'.

 

I already had the Excel table file ready with the headers, so I mapped the data to it and it worked.

View solution in original post

3 REPLIES 3
Paulie78
Super User
Super User

Please check out this video I did:

https://www.youtube.com/watch?v=sXdeg_6Lr3o

You're going to have to parse the CSV and turn it into JSON before you can do anything useful with it. Once you have completed the above, then you can move on to adding the rows to Excel.

Thank you @Paulie78 

I went through your video. Thank you for the clear explanation about converting the CSV data to JSON format. 

 

In addition to the steps you showcased in the video (https://www.youtube.com/watch?v=sXdeg_6Lr3o), I added another Compose step after the 'Get File Content' to convert it into String (As the step I used is of SharePoint and not OneDrive, it threw me an error stating that the output of the 'Get File Content' was an object).

 

After the step where we parsed the data as a JSON, I added a Loop (Apply to Each) using the Body from the Parse JSON step and then in the loop added a step to 'Add a row into a table'.

 

I already had the Excel table file ready with the headers, so I mapped the data to it and it worked.

I've followed your video line by line but am getting an error. It seems my .csv file is returning as an object:

{
  "$content-type""application/octet-stream",
  "$content""S24354542JKLSDFHDSAF5hbCBJZCxOYW1lLEVtYWlsLFRva2VuLEV4ZWN1dGVkLFN0YXR1cyxDb3VudHJ5IDogUmVzZWFyY2ggICBQcm9wb3NhbCAgIFN1Ym1pc3Npb24gMSxDb3VudHJ5IDogUmVzZWFyY2ggICBQcm8908SDFHLKASDFpb24gMixDb3VudHJ5IDogUs08909WDFHJSDKSDHFALKSwxLCwsT2xpdmVyIFNlcmdlYW50LG9saXZlcnNlcmdlYW50QGhvdG1haWwuY29tLCwwMy8wNi8yMDIxLEFOU1dFUkVELDI0LDI4LDMzLDM5DQo="
}
Not sure what octet-stream is but when I open the file in sharepoint it opens as a csv with the appropriate data. Not sure where the disconnect is but hopefully you can help point me in the right direction. 
I attached some screenshots as well...

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.

Community Calls Conversations

Community Calls Conversations

A great place where you can stay up to date with community calls and interact with the speakers.

Power Automate Community Blog

Power Automate Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Top Kudoed Authors
Users online (4,543)