CSV To New Excel Table 10-12-2023 00:29 AM - last edited 10-12-2023 00:30 AM

[takolota]

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!

[takolota]

@Dennis_ 

I’m not sure on that initial issue that pops up, since it is clearly an object parameter, not an array.

But the bigger problem is none of this will work if you are just passing each base64 of each file to the CSV input piece. You need to use the base64ToString( ) expression to get the CSV data in a text format.

Also if some of your attachments may not be CSV files, please apply a Filter array action before looping to filter out any files in the JSON array with a file name parameter that does not end with .csv 

[Dennis_]

I filtered out all non-csv-files but I couldn't manage to aply the "base64ToString( )" formula. I tried it on the red marked spots and as a compose action directly after the filter but I get an error everytime I try. 

Update: I successfully implemented the base64toString formula but the initial "array-issue" remains. I will attach a Screenshot and would be very thankful if you could help me @takolota !

[takolota]

@Dennis_ 

You can’t only put the settings action in the Apply to each loop. With multiple files you need to put all the actions after it in the loop too.

[Dennis_]

I tried but I get the message "This action cannot be dragged into foreach as an action outside foreach depends on it." or "This action cannot be dragged above actions it depends on." depending on which action I try to get into the loop. Therefore I can,t drag them into the Loops. Or Do I have to create a new loop for each individual action?

[takolota]

@Dennis_ 

No I’d already say you have an unnecessary extra loop because the base64 compose action in its own loop could have been avoided by making its From input the From input for the Settings loop & just do the base64tostring expression directly in the settings input.

You may need to click the 3 dots on the actions, copy to clipboard, delete the action, then go inside the loop to the create a new action there below settings, go to My clipboard, then select the copied action to paste it.

Or you could start from where the Settings action & everything else is outside the loop, create a Scope action above the Settings action, drag each action into the Scope, then create the Apply to each loop & drag the Scope with all the actions into the loop.

[Dennis_]

@takolota 

It worked! That's amazing! I only have one small problem left. My CSV has two identical Column names so the "create Blank Exel" Action fails. I feel like there has to be a simple solution but since I am very new to all of this I don't know. Do you have a solution in mind? If not that would be fine, you already helped me so much!

[takolota]

@Dennis_ 

No Excel table will allow two columns with the same name.

Is this a file you regularly get with the same column names each time? Or are you asking more for a general solution to handle this any time it comes up for any CSV?

[Dennis_]

@takolota 

Yes I thought more about something like "If Columnname is already used then rename with "name" & "2"". But like in a working formula and not just my mind.

Yes it's a file I get regulary with the same column name each time therefore I think a solution for renaming a specific column whose name I know would be totally sufficient. 

[takolota]

Okay. Then after the transformation of the file content to a CSV string (after base64tostring( ) expression) you could use expressions to replace the 1st instance of the column name with like ColumnName0.

concat(split(InsertStringCSV, 'ColumnName')[0], 'ColumnName0', join(skip(split(InsertStringCSV, 'ColumnName'), 1), 'ColumnName'))

[Dennis_]

@takolota 

It perfectly worked! Now I have a (hopefully the last) new problem on the Http request. I get the attached error "Die Anzahl der Zeilen oder Spalten in der Eingabematrix entspricht nicht der Größe oder den Dimensionen des Bereichs." which translates to "The number of rows or columns in the input matrix does not correspond to the size or dimensions of the range." I tried manually setting the size (in the "create table" action) of the table on exactly the size my Data should need but it didn't resolve the issue. Do you have an Idea what else I could try?

Maybe it has something to do with some columns having no data besides the column name or that some rows are empty?