Removing Specific Commas from a String

(0) Share

Report

Posted on by ProfessorEgg28

Hello Community,

I'm very new to Power Automate and would like to request some help.

I'm trying to create a flow in which when I upload a CSV file, it will auto-populate the values into a SharePoint list. The issue that I'm having is that some of the CSV items have extra commas in them and I'm having a hard time trying to remove those specific commas.

For example my CSV file will consist of this:

Reciept Number	Item	Purchaser
1	Shoes	Chris, Joe
2	Toothbrush, Toothpaste	Amanda, Phil
3	Ice Cream	Eric
4	Ham, Cheese, Bread	Michelle

There is an extra comma between toothbrush and toothpaste and ham and cheese and bread that makes my current flow not work. If possible, I would like to eliminate all commas in the item column. As for the purchaser column, I would like to keep those. There will never be more than 2 purchasers and I have an extra column on my Sharepoint list to capture it as well.

Current output of the filter array before creating items in SharePoint:

[

"1,Shoes,\"Chris, Joe\"\r",

"2,\"Toothbrush, Toothpaste\",\"Amanda, Phil\"\r",

"3,Ice Cream,Eric\r",

"4,\"Ham, Cheese, Bread\",Michelle\r",

]

Ideal output of the filter array before creating items in SharePoint (no comma between toothbrush and toothpaste):

[

"1,Shoes,\"Chris, Joe\"\r",

"2,"Toothbrush Toothpaste",\"Amanda, Phil\"\r",

"3,Ice Cream,Eric\r",

"4,"Ham Cheese Bread",Michelle\r",

]

The basic structure of my flow is:

Upload to the CSV, convert it to a string, create a new line, split the string with the new line to create an array and remove the header, filter the array to remove blank rows and then finally take final set of arrays and populate them in the share based on their column index.

The image of my current flow, the output I have been getting and the output I want is attached as well.

If anyone has any ideas, I would love to hear it. I'm sure there's a much simpler way to do this that I'm just not aware of. I can also get the file in excel form as well if that makes things easier. I just haven't looked into that quite yet.

Thanks!

Categories:

General topics

All responses (6)

Answers (1)

Sort by

SudeepGhatakNZ 14,396 Most Valuable Professional on at

Like
a
(1)

Report
Copy link

Link copied!
@ProfessorEgg28 ,The issue lies in the fact that the data contains commas within quotation marks. This complicates the parsing process because commas are typically used as delimiters, but in this case, they're part of the data itself.

I'll begin by replacing commas with semicolons in the CSV file. This tweak will cleanly distinguish between data and delimiters.

After applying the replace function, a typical row might look like this:

"2;\"Toothbrush;Toothpaste\";\"Amanda, Phil\"\r"

From there, you can proceed with the rest of your operations. If you'd like, I can provide a sample to illustrate the process further.
ProfessorEgg28 8 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi Sudeep,

It would be helpful to see a sample operation. When I try to use a replace function:
replace(outputs('64_to_String_Conversion'),',',';')

to replace all the colons with semi-colons after the string conversion step, I end up with:

Reciept Number;Item;Purchaser
1;Shoes;"Chris; Joe"
2;"Toothbrush; Toothpaste";"Amanda; Phil"
3;Ice Cream;Eric
4;"Ham; Cheese; Bread";Michelle

It would make sense if there was a way to just replace the item column data but this would replace all of them and then there would be no delimiters. When I run through the rest of the flow I end up with all the data in the first column (due to the fact that we have no commas). Were you proposing that I manually go into my CSV and change the colon to the semi-colon? Is it possible to not have to do that?
SudeepGhatakNZ 14,396 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

Hmmm!

Are you able to share the CSV file with me?
ProfessorEgg28 8 on at

Like
a
(0)

Report
Copy link

Link copied!
I can't seem to send .csv files on this reply option but perhaps seeing a sample would shed some light.

My final output before creating the items is:

"1;Shoes;\"Chris; Joe\"\r", "2;\"Toothbrush; Toothpaste\";\"Amanda; Phil\"\r", "3;Ice Cream;Eric\r", "4;\"Ham; Cheese; Bread\";Michelle\r"

If this is what is expected then I'm wondering if it's the create item function. I currently have it set to:

split(item(),',')?[1]

In which the number in the closed brackets indicates the column.

Thanks again for your help!
SudeepGhatakNZ 14,396 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!
Please refer the attached flow.

I have replaced the Delimiter from Comma to ; using the following expression

replace(replace(item(), ',"', ';\"'), '",', '\";')

So,

"2,\"Tooth Brush, Toothpaste\",\"Chris, Jowe\"\r",

gets transformed to

2;\"Tooth Brush, Toothpaste";\"Chris, Jowe"

Now if you use split function with semi colon, you can separate out the individual column values

CSV_2024041801030...
Verified answer

takolota1 4,980 Moderator on at

Like
a
(1)

Report
Copy link

Link copied!

@SudeepGhatakNZ @ProfessorEgg28

You can use this template to parse a CSV with in-data commas: https://powerusers.microsoft.com/t5/Power-Automate-Cookbook/CSV-to-Dataset/td-p/1508191

If you just want to change the CSV delimiters, then there is this with the same core logic: https://powerusers.microsoft.com/t5/Power-Automate-Cookbook/Change-CSV-Delimiter/td-p/1442954