Answered

CSV remove comma between double quotes in flow

(0) Share

Report

Posted on by 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.

Screenshot 2023-03-16 131850.png

Categories:

General topics

I have the same question (0)

All responses (3)

Answers (1)

v-dezhili-msft Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!
Hi @leu0202 ,

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

Best Regards,
Dezhi

Was this reply helpful? Yes No
Chriddle 8,525 Super User 2026 Season 1 on at

Like (1)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
Verified answer

leu0202 7 on at

Like (0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No