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

2 REPLIES 2
Paulie78
Super User III
Super User III

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.

View solution in original post

Helpful resources

Announcements
MPA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

V3_PVA CAmpaign Carousel.png

Community Challenge - Giveaways!

Participate in the Power Virtual Agents Community Challenge

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Solution Authors
Users online (1,272)