11-22-2022 06:06 AM - last edited 11-22-2022 06:07 AM
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!
That expression is only found in the Select Reformat action. But again, I don’t know why it would be passed through several actions as text like that. Maybe there was one of the import errors that reset that expression to plain text.
Please go to the Scope Change CSV drlimiter & go to the Select reformat action & show what that looks like.
Yup, that’s the issue. Copy that expression, click the map button on the right to get a single textbox entry field & replace the text with the actual expression.
This is where I thought I read the original instructions wrong, and so I have tried both ways. When using the single text field I get an error: "Enter a valid json". I received this error originally which led me to the two text field box error. This is what I'm receiving:
I also tried copy/paste the original code again to double-check.
I tried a couple more things. re-read the instructions (again) and tried toggling the fields back and forth as you suggested (I thought I read that right!)...
I think this is what the code is supposed to look like, correct?
So I explored further and saved the flow again. This time I didn't run a test. I simply went back to my list of Flows and went back into this one to edit again and take a look for any changes. For some reason, it reverts back to two text fields, and of course, the same test results when I run it.
Kind of confusing. Thoughts?
No, there shouldn’t be any text at all. You need to go to the single textbox view, click in the textbox to let the dynamic content/expressions tab to pop up, then switch to the expressions section & enter the expression there as an expression.
Ah yes, I think I understand your instructions. And this is what I get....
and I get this function:
... and it does seem to save properly...
After running a test, this leads me to the next error:
The error window is a replica of the code snippet for the function. Odd.
Appreciate your patience. Thoughts?
@Virtualizeit
That's not how that is supposed to look. This is what it normally looks like...
But, looking at the data flowing into that on your picture, it looks like there are several \n characters. Are you sure your file has the standard \r\n CSV linebreaks in it? If it's actually only using standard \n linebreaks, then you will want to remove the 1st expression in the Linebreak field of the CSV dats + Set-up action so only the \n expression remains, decodeUriComponent('%0A')
I tested the first theory with a third test dataset. A simple dataset created from VS text editor and I got the same results as above. For the next test, I then removed the first function, but yet same failed results.
Why does it appear to see the data correctly BEFORE going to the last failed step where all the \n appear from know where?
Doesn't make sense where the multiple \n entries are coming from.
From your pictures it looks like your new data only has the \r carriage return. So in that case you would remove the 2nd expression from the linebreak setting so it is just splitting on \n.