Unanswered

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

(0) Share

Report

Posted on by DevadathanK

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.

Categories:

Building flows

I have the same question (0)

All responses (3)

Answers (1)

Paulie78 8,422 Moderator on at

Like (1)

Report

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.

Was this reply helpful? Yes No
Verified answer

DevadathanK 56 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
kgreen8790 189 on at

Like (0)

Report

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

debug2.PNG

debug1.PNG

Was this reply helpful? Yes No