Change CSV Data Delimiter 04-08-2022 14:28 PM

[takolota]

Hello All,

I ran into some issues when pulling CSV data in from Power Automate Desktop because of commas in the actual data. I wanted to change the delimiter so I could more easily parse the data in a single Select action without commas in the actual data messing things up. I also may be parsing CSV files with hundreds or thousands of rows, so I didn’t want to use all my daily actions on this in a slow Apply to each loop.

Attached is the scope/flow I built so anyone can easily select their CSV data that has quotes around the comma-containing records, enter a new delimiter, and get the new delimiter separated data from the final compose action without the usual errors. And it only takes a few actions to do this, even on very large files.
I've found that many CSV files don't put quotes around their records with in-data commas, and this only works when there are quotes around those records. But if the file is saved as a text file, then it often puts quotes around the right records.
If you are using Power Automate Desktop, program the file to be saved as .txt and read that into your output variable.

It’s currently set to handle up to 50 comma-containing columns, but you can expand that to as many columns as needed by adding extra lines & expressions to the 1st Select action. Just follow the pattern by replacing some of the array numbers, like [50] with [51].

Also if your data has more unique values like an array with mixed quoted string data, Ex: ["string1", 2, 03/05/2022, "string3"], then this will create errors in the output.

The template for parsing CSV to JSON & entering it into a dataset uses the same change delimiter set-up: https://powerusers.microsoft.com/t5/Power-Automate-Cookbook/CSV-to-Dataset/m-p/1508191#M584

*Copying the template scope into another flow may not work as it may mess up the expressions. You may need to start with a copy of this template and copy & paste the rest of your flow into this template flow.

Thanks for any feedback, & please subscribe to my YouTube channel (https://youtube.com/@tylerkolota?si=uEGKko1U8D29CJ86)

Version 3 Uploaded 03/26/2022 (Adjusted the 1st Select input so it can now also deal with in-data commas in the 1st column. Added more lines to the 1st Select so it can now handle up to 50 columns with commas in them.)

Google Drive Link: https://drive.google.com/file/d/11uT15hXY0VjnOKDvFxdVgkuMtXqTmA0c/view?usp=sharing 

Version 4 Uploaded 04/09/2022

(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.)

Google Drive Link: https://drive.google.com/file/d/1ZbhFGVKHSpaH2Duv8qXwMnNww8czdgc4/view?usp=sharing 

Version 5

More adjustments for comma edge cases and for instances with one comma-containing value following another in the file data.

Google Drive Link: https://drive.google.com/file/d/1il_wI9fJRk11YaI4EPQvk2efrbBNRBwr/view?usp=sharing 

Update 06/01/2022
Microsoft Power Platform & Paul Murana recently did a video demonstration of how to handle CSV & other files in dataflows: https://youtu.be/8IvHxRnwJ7Q
But it currently only outputs to a dataverse or Dataverse for teams table.

[takolota]

Version 3 Now Available

I adjusted the 1st Select input so it can now also deal with in-data commas in the 1st column. I also added more lines to the 1st Select so it can now handle up to 50 columns with commas in them.

Google Drive Link: https://drive.google.com/file/d/11uT15hXY0VjnOKDvFxdVgkuMtXqTmA0c/view?usp=sharing 

[harrisonnz]

Hi @takolota ,

Thanks again for taking effort working on and providing the 3rd version, really appreciated. I just tried with it and found that it appears having issue with any cell ending with a comma (e.g. 'Suite 101 Albert St. Landing,' ). Is this version supposed to cope with it?  Thanks heaps for keeping improving this, you are a legend! 

[takolota]

@harrisonnz 

Thanks for mentioning that. I added it to the note about potential errors. It may not be something I can get around with just the flow expressions as this set-up relies on a split of the data wherever there is a comma & quote ( ," ). I don't currently see an easy way to differentiate end of value commas with delimiter comma quotes with the more immediate tools in Power Automate. 

However, I did recently find this really interesting post on creating custom actions for things like this: http://johnliu.net/blog/2021/9/parse-csv-through-code-in-power-automate-custom-connection
There may be a way to better work these things out with the custom C# code and actually package it into a single custom action. But I'd need to get a better idea of how John's code works, what capabilities are available with that code, and if it would have similar issues as my current version here.
Also, I don't see a very user-friendly way to share the custom actions other than the end-user recreating everything themselves & copying the C# code or getting Microsoft to certify the custom action (and Microsoft probably wouldn't certify it if it couldn't handle like 99% of edge cases).

[mtbaker28]

This solution was a big help. Thank you

[takolota]

Version 4
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.
@harrisonnz, I also looked over a problem with in-data trailing commas again. 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.

Google Drive Link: https://drive.google.com/file/d/1ZbhFGVKHSpaH2Duv8qXwMnNww8czdgc4/view?usp=sharing 

[harrisonnz]

Thank you so much @takolota , absolutely a legend! I will try out and let you know. I think the leading comma would be quite rare anyway. 

Cheers!

[takolota]

Version 5

More adjustments for comma edge cases and for instances with one comma-containing value following another in the file data.

Google Drive Link: https://drive.google.com/file/d/1il_wI9fJRk11YaI4EPQvk2efrbBNRBwr/view?usp=sharing 

[DickyFB]

@takolota this is an awesome bit of code and saved hours of painful head scratching, so thank you. However I do have a follow on question - Once formatted correctly how can I then add each row of the output CSV to create a new SharePoint list item? Googling a bit people seem to parse to JSON but it's getting the CSV info to work.

Hope you can help 😁

[takolota]

@DickyFB 

Yes, the template to parse the data to JSON so it can be used in dynamic content in Create item actions is listed on the main post. I re-worded the description to be more clear.

Link: https://powerusers.microsoft.com/t5/Power-Automate-Cookbook/CSV-to-Dataset/m-p/1508191#M584

[harrisonnz]

Hi @takolota ,

V4 works like a treat, great job! Will try V5 a bit later as well, looking forward to it 🙂 

Thanks!