CSV remove comma between double quotes in flow

[leu0202]

I have a very simple csv sheet. One column is owner name, and one column is their reference ID.

Example of the CSV file content:

Name,Owner ID

123 Test LLC,497
555 Example LLC,485
777 Trythis LLC,541
"Doe, John",349
"Doe, Jane",599

When I Compose-Split by new line: 

split(outputs('get_file_content')?['body'],outputs('Compose-new_line'))

My Json outputs does this:

 {

    "Name": "\"Doe",

    "Owner ID\r": " Jane\""

  },

I am having trouble due to the comma that is in between the quotes. I have tried many things on the internet as well as chat gpt and have had no luck in the understanding on how this should be performed. 

[v-dezhili-msft]

Hi @leu0202 ,

Please try:

split(outputs('get_file_content')?['body'],decodeUriComponent('%0D%0A'))

Best Regards,

Dezhi

[Chriddle]

In general, I don't think there is a reliable way to convert CSV to JSON without third-party actions (or your own custom connector).

However, if your CSV is as simple as your example, you can replace ', ' with '#commaspace#' and all ''' with '' in your CSV string.

After the splits are complete, replace the "#commaspace#" in your JSON with ", ".

And don't do the Compose-new line trick here. Use the advice from @v-dezhili-msft 

[leu0202 · Accepted solution]

Thanks for the help. I ended up coming across an amazing video out there that totally covers it in a different light. Truly a god send for messing with CSV files in power automate. Great video for the community. Thank you, Paul Murana! 

https://www.youtube.com/watch?v=8IvHxRnwJ7Q

Instead of programming power automate to do the work, it covers how you can pull a csv file into power query. You can edit the csv file there (great editing features). It creates a table. When the data flow completes, you can trigger a power automate flow to pull the newly created table with your edits into power automate.

After you do that you can parse json it and create new items in a SharePoint list based off of it. Followed up with flagging items already in the SharePoint list. So I don't get duplicates into my SharePoint list. As well as, if a item falls off the CSV reports to update it inside of my SharePoint list based of the reference ID's in the CSV report. So I always have an updated database that I can then pull into power apps.