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.
@Mark_Venture
I'm not able to replicate that when I download the flow & apply it to my OneDrive folder in a loop.
Did you import the flow & create or copy the rest of your flow around the CSV delimiter change piece, or did you copy & paste the delimiter change scope into your existing flow?
You may want to download the template & build the rest of your flow around it.
Step 1 was download and import the flow, and get it working with a single file. It is working using Get File Content [OneDrive business] and specifying a single file (using the picker).
After the Scope Change CSV delimiter step, I was able to add a condition evaulating if "Field 1" contains "abc" = YES Send an email with YES in subject, if No = send email with NO in subject. For the body, I listed each field name and the values determined from Scope Change CSV step. This worked. I got the right email based on the field, and the body of the email properly listed the values for each of the fields.
Step 2 was to take that working flow and do a Saved As... to create a copy. The copy worked too. So I began working on the copy to add the List Files In Folder followed by Apply to each into the flow (that is the screen shot in my post).
For Get File Content, I tried to copy the step to clipboard, and selecting from "My Clipboard" when adding an action into the Apply Each, as well as just creating one in the apply each.
On this flow i was editing, I did manage to get output that matched the "get file content" output on the working flow..
Next up was to To get Scope Change CSV Delimiter under the new apply to each. For that I again used the Copy to my Clipboard, and elected My Clip Board when adding the action. As soon as I paste it in, I get that error in Flow Checker.
On the Get File Content in the Apply to each loop, I have tried ID, Name, Path, Value, Item, etc. as the input (for List files in folder) on the new Apply to each.
@Mark_Venture
Does the "Compose CSV data + Set-up" action name match the references made to it in the "Select Reformat file data, replace in-data commas" expressions?
For example, have you pasted this scope to the flow more than once, so is there now a number at the end of the action name, like ""Compose CSV data + Set-up 2" and that name change wasn't automatically copied to the expressions in the "Select Reformat file data, replace in-data commas" action?
@takolota No, I've pasted once. If it failed and I need to change things, I removed the step before pasting it again.
With this flow this way, I get that error from Flow checker Fix invalid expression input...
IF I leave everything the same, and remove the Scope Change CSV delimiter step, the error goes away, and the output of the Get File Contents step shows the content for all 4 files in folder as I step through. The output matches the working flow running against the single, specified file.
@Mark_Venture
Can you do me a favor & open the "Select Reformat file data, replace in-data commas" action & send a screenshot of that?
And hover over one of the expression inputs if you can to see what the expression says.
I'm wondering if the expressions got messed up and pasted as some kind of plain text or something. That sometimes happens.
@takolota Sorry for the delay. Yes, something got messed up with the copy/paste. Something in the formula for "Select Reformat file data...." step inside the Scope Change CSV delimiter process.
I started to paste each step of the Scope Change CSV separately, and watched the flow checker. That is how I found it. And I was able to Peek Code, copy/paste.
Now its working great!! Thank you!
Hello Takolota
Thanks for your work, it's great.
I have a text file with spaces as a delimiter (I think it's spaces, could be tabulation)
It can be one space or multiple spaces, I would like to use this little flow, in this CSV To New Excel Table flow.
Get the text file, replace the spaces with commas and then follow the flow.
How can I handle the multiple spaces ? thanks.
I would 1st really check if it’s tab or space delimited.
If it is tab delimited, it’s much less likely you’ll see an in-data tab, so you should be able to use the part of that workflow that skips over the comma delimiter change & goes straight to splitting on your unique delimiter (like a tab character)
Unfortunately it seems that it is multiple spaces delimited, I tried to enter the tab as a delimiter, getting the same error "too many columns"
What can be done? add a "trim or replace" function before or in the composer ?
Everything that is more than two spaces should be replaced by a comma, I have no idea how to put that in your composer.
Can you paste some of the text in something like this uri encoding site so we can see what the actual characters are?
Then we can use a decodeuricomponent() expression to get that character in the delimiter input.