Quickly & Easily Convert Various CSVs to New Excel Tables
Takes in most types of CSV data, dynamically identifies the header names, and creates an Excel table in a new or existing workbook.
No premium, 3rd party, or Office Script actions required. Only standard connectors.
(Works on comma-separated files where any items with in-data commas are surrounded in double quotes “String1, String2” but comma separated arrays with double quotes inside an item may also cause issues.)
For example, here is a CSV I downloaded from a random SharePoint list:
And I did not create any template doc table, & I did not adjust any names. I just selected the CSV file in my OneDrive, then ran the flow to get this output...
(But if you’re just downloading this template, then you will need to change the “Create table” action document library reference to your OneDrive folder instead of the id that is in there by default)
If needed, you should also be able to dynamically change the destination Excel to an existing workbook & worksheet based on other values in your flow. And the CSV data can automatically come from any preceding actions.
If you use something other than the default OneDrive action to get your CSV data, then add the CSV output to the "CSV_Data" field in the "Compose CSV data + Set-up" action.
If your CSV header row with column names is not the 1st row in your CSV, make sure to adjust the "ColumnNamesRow" value in the "Compose CSV data + Set-up" action to match the correct CSV row number.
This template is really just a combination of...
-The CSV to Dataset template with manual column mapping: https://powerusers.microsoft.com/t5/Power-Automate-Cookbook/CSV-to-Dataset/td-p/1508191
-@RezaDorrani's video on exporting data to an Excel table: https://youtu.be/Kupz71dWYyY
-@Paulie78's video on fast exporting to an Excel table: https://youtu.be/AA-D4lgjR3o
Please 1st try downloading the below zip file & importing the whole flow as described in this post: https://powerusers.microsoft.com/t5/Power-Automate-Cookbook/CSV-To-New-Excel-Table/m-p/1891765/highl...
If the standard zip file import method does not work for you, then you can try an alternative Power Apps Solution import method here: Re: CSV To New Excel Table - Page 15 - Power Platform Community (microsoft.com)
Or an alternative clipboard copy & paste import method from these 3 posts
https://powerusers.microsoft.com/t5/Power-Automate-Cookbook/CSV-To-New-Excel-Table/m-p/1841481/highl...
https://powerusers.microsoft.com/t5/Power-Automate-Cookbook/CSV-To-New-Excel-Table/m-p/1841499/highl...
https://powerusers.microsoft.com/t5/Power-Automate-Cookbook/CSV-To-New-Excel-Table/m-p/1841504/highl...
https://powerusers.microsoft.com/t5/Power-Automate-Cookbook/CSV-To-New-Excel-Table/m-p/1843986/highl...
Warning. The Groups HTTP connector with access to the graph API calls used for this template may be removed soon. You can vote to ask for this not to be removed here: https://ideas.powerautomate.com/d365community/idea/d51463df-6f0c-ed11-a81b-6045bd7c3e3f
You can also try using a different premium HTTP action to make that final call, like what Matt does here: https://www.matthewdevaney.com/quickly-add-multiple-rows-to-excel-table-in-power-automate/
Version 1.1 of the template flow does just this & uses the PREMIUM Azure HTTP action.
To set up the Azure HTTP action connection, use https://graph.microsoft.com for the url & for the Resource URI:
Version 1.1SP is set up to get the CSV from SharePoint & transfer the new Excel file & new table to SharePoint at the end of the flow.
Thanks for any feedback!
@autoperfect93
No.
You shouldn't need to change any of those expressions. There are already inputs in the Compose CSV data + Set-up action to handle that. You should just need to change the ColumnNamesRow & HeaderRowsToSkip inputs to 2.
Hi @takolota ,
I have tested the CSV_To_Excel_V1.1 SP Flows. It works but the file I get in SharePoint is empty.
I think this is due to the action Get file content retrieving the empty excel file created in the "Create file blank Excel"
So the step "Create file" actually creates an empty excel file in SharePoint.
@autoperfect93 I think you input a SharePoint url instead of the MS Graph url as listed in the instructions
Indeed. I did put the SharePoint URL but I did some research and noticed this afterwards.
The only issue left is the question "How to take the content of the file in OneDrive for Business to SharePoint"
I think this is due to the action Get file content retrieving the empty excel file created in the "Create file blank Excel"
Hi @takolota ,
Thank you so much! I have added 30 seconds before the "Get file content" action in order to give some time for the excel file to be updated with the data.
Hello @takolota ,
I have been using the flow to convert the CSV files into XLSX since the beginning of May but now I started facing the following error:
The flow fails at action "Invoke an HTTP request" with error "The response is not in a JSON format."
Here is the raw outputs of the step:
The interesting part is that the error appears randomly. Sometimes the flow finishes in success but it is not stable and the above mentioned error stops the flow and I still am not able to understand what is triggering the issue.
Any advice would be highly appreciated.
Best Regards,
Dzhuneyt
