CSV to Dataset

takolota · ‎03-18-2022

Template for converting large CSV files to JSON, then sending the data to a table or list.

This get data for CSV works even if there are commas in the CSV data. The only requirement is the the CSV file must follow the standard of placing double quotes “ , “ around any item eith in-data commas.

Set Up

Go to the bottom of this post & download the CSVToJSON_1_0_0_xx.zip file. Go to the Power Apps home page (https://make.powerapps.com/). Select Solutions on the left-side menu, select Import solution, Browse your files & select the CSVToJSON_1_0_0_xx.zip file you just downloaded. Then select Next & follow the menu prompts to apply or create the required connections for the solution flows.

Find the CSV To JSON solution in the list of solutions. Select it. Then find the CSV To JSON flow inside the solution package.

Once in the flow, go to the Get file content action & select the CSV you want to work with. Alternatively you could use a different action to get the CSV content as text.

After selecting your CSV, go to the Compose CSV data + Set-up action. If you used a different action to get the CSV file content as text, then you will need to input the output of that action in the CSV_Data parameter value (The get file content action you use may return the CSV file as base64, in which case you must use a base64tostring( ) expression on it in the input.

Adjust the rest of the parameters for your file. For example if your CSV has extra lines at the header or footer of the sheet/file, then account for those in the HeaderRowsToSkip & FooterRowsToSkip.

After that, move down to the Select CSV to JSON action. Input the header column names on the left side of the mapping & use the expressions outputs('Compose_CSV_data_+_Set-up')['NewDelimiter'])?[INSERT COLUMN NUMBER HERE STARTING FROM 0] on the right side of the mapping to match up their values. For example in the preceding volunteer contacts CSV example, First Name was the 0 index column so its value expression is outputs('Compose_CSV_data_+_Set-up')['NewDelimiter'])?[0] and Email is the 3rd index column so its value expression outputs('Compose_CSV_data_+_Set-up')['NewDelimiter'])?[3]

After that, we need to get the JSON schema for the Parse JSON action. So go & run the flow once. Then in the flow run, go to the outputs of the Select CSV to JSON action & copy the JSON output there.

Go back to the flow editor, go to the Parse JSON action, select Use sample payload to generate schema, & paste the JSON output to the sample payload menu. Select Done.

Now your CSV columns should be available as dynamic content from the Parse JSON action & you can insert them wherever you need in the rest of your flow.

(If you can export as a semicolon, tab, or other delimited file, then you can probably just use a simpler method like Paul’s here: https://www.tachytelic.net/2021/02/power-automate-parse-csv/?amp)

For more information on the delimiter change piece, visit this previous post:

https://powerusers.microsoft.com/t5/Power-Automate-Cookbook/Change-CSV-Data-Delimiter-From-a-Text-Fi...

To make a flow to send any CSV data to a new Excel table with given CSV header names without any column mappings, check this template:

https://powerusers.microsoft.com/t5/Power-Automate-Cookbook/CSV-To-New-Excel-Table/m-p/1826096#M964

*Copying the template into an existing flow may create issues with expressions. You may need to copy the rest of your existing flow into the CSV template flow, then move the template scope where you need it.

CSV To JSON Version 3

(More minor fixes & additions.

I adjusted several expressions so it can now handle a few more scenarios with arrays in the CSV data. It should handle any array that doesn't include double quotes and any array that is all strings with double quotes, so ["String1", "String2", "String3"], but it will have issues if it is a mixed array with some double-quoted strings and some other values, for example ["String", 4, 03/05/2022, "String2"] won't work.

I also adjusted how the LineBreak setting is set-up so it now uses the /r/n for the LineBreak. I also provided this link in the flow so anyone can look up the right string for the decodeUriComponent expression(s) if they happen to have different LineBreak characters. This change also made it possible to differentiate between in-data line-breaks and CSV row line-breaks on the files I tested, so it should now replace the in-data line-breaks, like the multiple-choice fields some sites use, with semi-colons. That should make those records much easier to deal with & parse in later actions.
I also looked over a problem with in-data trailing commas. I added a line in the settings where anyone can toggle whether they want it to adjust for trailing OR leading commas in the data, it just can't handle both in one dataset. So if one column in one row has ",String1 String2" and another column in another row has "String 3 String4," then it will have errors.)

CSV To JSON Auto Columns Version 1

An additional flow version that automatically does the JSON key-value matching. Useful if you may have use-cases with dynamic CSV files that may change each time the flow is run.

If you have any trouble with the standard legacy flow import method, you can also try an alternate Power Apps Solution import method here: Re: CSV to Dataset - Page 8 - Power Platform Community (microsoft.com)

Thanks for any feedback,

Please subscribe to my YouTube channel (https://youtube.com/@tylerkolota?si=uEGKko1U8D29CJ86).

And reach out on LinkedIn (https://www.linkedin.com/in/kolota/) if you want to hire me to consult or build more custom Microsoft solutions for you.

watch?v=-P-RDQvNd4A

jaeiow · ‎06-26-2022

Power automate has a much better performance when what you need to do is use this data. Power query has seemed to need a long amount of time to run to run the queries getting files from folder. But, we can use power automate trigger on power query completion, which can help to automate running of this flow.

jaeiow · ‎06-28-2022

Is there a way to eliminate the parse json step? For example, we have csv data that is generated in excel, but the fields are all different. And in fact, have an Select action edited, like so.

This is because column names are created and updated in excel before saving the CSV through a macro.

I was thinking of various ways, but I can't seem to find one surefire approach.

Edit: PAD is not available for our work pcs. Office scripts is perhaps available, only if the document is saved as a xlsx in another macro step.

takolota · ‎06-28-2022

@jaeiow

The parse JSON is mainly there to make it more user-friendly for people & get stuff into dynamic content. You can always just use the body of the last action as the array input for a loop, then use

items(‘Apply_to_each’)?[‘InsertColumnIdentifier’]

wherever you need to reference values.

But it sounds like you would still need to figure out a way to dynamically get the new column names for that with your use case.

jaeiow · ‎06-28-2022

I have a sort of "flat file" format to have cross compatibility with another system, where each row has the column name, delimiter, and field value joined in Excel. I would be able pick out the column names because each row will have the same columns, ideally. All even values and 0 are column names. Hopefully I can figure out how to insert a coalesce function inside the Select action to determine which is the last element.

jaeiow · ‎06-29-2022

This may be the formula that allows for the different columns (and number of columns) in a select action:

https://powerusers.microsoft.com/t5/Building-Flows/Split-string-into-array-of-characters-empty-delim...

takolota · ‎06-29-2022

@jaeiow

I’ll need to review that & understand if there is a way to index all the columns of a table, then make a Select action that can reference each column index with a variable length of columns & indexes. It could be useful for several of my templates, but my main concern with that type of set-up is always the slower performance to calculate all that and reduced readability of the actions & expressions / code.

probey82 · ‎08-17-2022

I'm trying to get this to work, but I keep getting these errors and have no idea how to fix them.

takolota · ‎08-17-2022

@probey82

Can you show an example of your starting data, any non-template actions in your flow, & the set-up compose action?

takolota · ‎10-19-2022

New addition to this series of CSV templates! Now create new Excel tables from most CSV files with auto-generated column names from the CSV header line!

https://powerusers.microsoft.com/t5/Power-Automate-Cookbook/CSV-To-Excel-Table/m-p/1826096#M964

cdn4lf · ‎11-07-2022

I keep getting blanks when trying to run this, but the file and content is being retrieved correctly. See screenshot.

Any ideas?