Skip to main content
Community site session details

Community site session details

Session Id : o89G7Vg3Fb0EmVHAyS0yUf
Power Automate - Building Flows
Unanswered

Parse a CSV file with comma in the value

Like (0) ShareShare
ReportReport
Posted on 14 Feb 2024 19:56:22 by 76

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.

IncidentManage_0-1707940574249.png

 

 

  • takolota1 Profile Picture
    4,898 Moderator on 15 Feb 2024 at 23:11:41
    Re: Parse a CSV file with comma in the value

    This template also deals with in-data commas

    https://powerusers.microsoft.com/t5/Power-Automate-Cookbook/CSV-to-Dataset/td-p/1508191

  • ivan_apps Profile Picture
    2,187 Moderator on 15 Feb 2024 at 05:29:17
    Re: Parse a CSV file with comma in the value

    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.

     

  • Incident-Manage Profile Picture
    76 on 14 Feb 2024 at 21:36:36
    Re: Parse a CSV file with comma in the value

    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

  • ivan_apps Profile Picture
    2,187 Moderator on 14 Feb 2024 at 21:31:48
    Re: Parse a CSV file with comma in the value

    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.

  • Incident-Manage Profile Picture
    76 on 14 Feb 2024 at 21:16:24
    Re: Parse a CSV file with comma in the value

    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.

    IncidentManage_0-1707945359308.png

     

  • ivan_apps Profile Picture
    2,187 Moderator on 14 Feb 2024 at 21:03:58
    Re: Parse a CSV file with comma in the value

    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?

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Announcing our 2025 Season 2 Super Users!

A new season of Super Users has arrived, and we are so grateful for…

Paul Stork – Community Spotlight

We are honored to recognize Paul Stork as our July 2025 Community…

Congratulations to the June Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > Power Automate

#1
Michael E. Gernaey Profile Picture

Michael E. Gernaey 497 Super User 2025 Season 2

#2
David_MA Profile Picture

David_MA 436 Super User 2025 Season 2

#3
Riyaz_riz11 Profile Picture

Riyaz_riz11 244 Super User 2025 Season 2

Loading complete