10-19-2022 12:52 PM - last edited 11-23-2023 17:50 PM
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:
Thanks for any feedback!
@dani2
Is the HTTP action at the end of the flow failing with your data? And what does the data look like with & without the column renames?
@takolota It's failing at the end of the flow with an error message "The number of rows or columns in the input array doesn't match the size or dimensions of the range.". Normal column Name is like Spend Table[Supplier Name] , renamed column is Supplier Name . When go into the run history details though, I see the failure is actually happening at the "Scope Get or generate column header names" with this error "Unable to process template language expressions in action 'SelectGenerateDefaultColumnNames' inputs at line '0' and column '0': 'The template language expression 'range(0, length(split(outputs('DataLines')[0], outputs('Compose_CSV_data_+_Set-up')['NewDelimiter'])))' cannot be evaluated because array index '0' cannot be selected from empty array. Please see https://aka.ms/logicexpressions for usage details.'.
The fact that array is empty leads back to the DataLines action & likely further back to the Reformat file data, replace in-data commas action.
Could you please share a screenshot of the actual CSV data with & without renaming?
I’m wondering if there is something wrong with the header delimiters.
Also are you sure it’s the column names with brackets that are working? I could see some added pieces meant to deal with in-data arrays getting messed up if there were array brackets in the headers.
Here are the screenshots, with and without the column rename. Yes, positive that the original file with the brackets was working and the file with the renaming columns was not working. I was in the process of updating my load to SharePoint file to include the brackets so it would read the columns but then neither option was working yesterday so I paused that effort.
@takolota My flow seems to save properly, but fails to complete the test. Errors show up in the Create Table step. "Duplicate column names provided". Not sure where to dig for a solution on this one (and I'm using a very small and simple dataset).
Thoughts?
Can you please scroll to the right on that Column Names output section?
And are your headers & data possibly starting not at the top of the file in this case?
Like is there an extra row or couple of rows above the headers? If so you may need to adjust what row it is trying to take headers from in the “data + Set-up” action.
Here is the Columns Names field:
{"replace(\nreplace(\nreplace(\nreplace(\nreplace(\nif(and(contains(join(item(), ''), '['), contains(join(item(), ''), ']')), \nreplace(replace(replace(\nif(equals(last(join(item(), '')), '\"'), take(join(item(), ''), sub(length(join(item(), '')), 1)), join(item(), '')), '\",,\"', '\"###\"'), '[\"\"', '[\"'), '\"\"]', '\"]'), \nif(equals(last(join(item(), '')), '\"'), take(join(item(), ''), sub(length(join(item(), '')), 1)), join(item(), ''))), \noutputs('Compose_CSV_data_+_Set-up')['InDataLineBreakToReplace'], outputs('Compose_CSV_data_+_Set-up')['InDataLineBreakReplacement']), \n',', outputs('Compose_CSV_data_+_Set-up')['NewDelimiter']), \n'###', ','),\nconcat(outputs('Compose_CSV_data_+_Set-up')['NewDelimiter'], '\",', outputs('Compose_CSV_data_+_Set-up')['NewDelimiter']), concat(outputs('Compose_CSV_data_+_Set-up')['NewDelimiter'], outputs('Compose_CSV_data_+_Set-up')['NewDelimiter'])),\n'\"\"', '\"')":""}
This is the Outputs Body field:
And what does the column name field look like in the flow edit screen?
That looks like it might be passing the expression formula as a text input or something.