I have a report that my IT ticketing system spits out twice a week that currently has to be manually taken and formatted to before it goes out to leadership. The report comes as a CSV. I am building a flow that would automatically update take the CSV file convert it to JSON and then apply it to a template on SharePoint before sending it out to leadership. The issue I am having is that some of the fields have commas within the value either due to location or the way the ticketing system reports multiple assignees on a ticket. Is there a way to ignore or remove those extra commas before the it converts to Excel. Below is a screenshot of my flow as I have it, in the current state the values get broken apart if they have commas within the value.
This template also deals with in-data commas
https://powerusers.microsoft.com/t5/Power-Automate-Cookbook/CSV-to-Dataset/td-p/1508191
So you’ll have to get a bit creative if you can’t change the output to replace Assignees from being comma separated to semicolon separated. I would ask to see if they can modify their output or even give you an excel document instead.
a manual step of opening the CSV in excel and replacing the comma with semicolon would work. Alternatively SharePoint has a ‘import from CSV’ option that works to handle your scenario and should read the commas in the column. Manual steps so not ideal.
This video by Reza Dorrani shows the process to read and parse a CSV file, but also explains the comma has to be replaced from a single column beforehand. https://youtu.be/b3aQf17ptAs?si=jBpjRDgIqyTlAs8X
the one trick you may be able to do - force the assignees to be the last column of your data rows. Once you have separated every time by the ‘new line’ character, you use the Split() function to separate the columns. Your Assignees column would start on index 8 for example. Therefore you would know that if your row contains an index 9, you have another assignee. You can conditionally check indexes 8-12 to support up to five assignees in your import flow. Again, this will only work if you have assignees in the last column and that is the only column with this issue.
another solution would be to parse the row for any quotes, record your first instance of the quote, use that as the starting point to search for another quote. The substring function would help you here. Anything inside the Quotes, do a replace() in power automate to change the coma to a semicolon. It’s a lot of substring manipulation and you’ll likely add another set of loops, so be careful properly parsing string this way. But if you get it working, then you can fully automate your CSV file and it will support any column having extra commas, as long as they are enclosed within quotes.
You are right, it does have the double quotes. So what would be the best way to get the data from the CSV so I can move it into my excel spreadsheet/template as I am thinking the way I am trying to do it is not going to work
try to view the CSV outside of excel - open it in notepad or VSCode and see how it is formatted. Excel took the liberty of removing the comma separation but it did understand 'Assignees' is one big string.
It does not the file is formatted as you see in the screenshot below where it just puts a comma between each person's name.
Normally a CSV file would have the column that has commas in it surrounded by quotes. Similar to this -
test, "test2, test", test3, test4
Is that not how your CSV is?
Michael E. Gernaey
497
Super User 2025 Season 2
David_MA
436
Super User 2025 Season 2
Riyaz_riz11
244
Super User 2025 Season 2