CSV headache: Remove semi-colon from inside double quotes

[AndreasO]

Hello. I have a bit of a CSV headache. I receive a CSV from an API containing an array of integers and strings, with a semi-colon separators. I can't change the separator the CSV file is delivered with.

The values in the CSV files arrive like this: 

5;5;5;5;5;6;2;;;;5;3;"Text";5;5;5;"Text";"Text";"Text field where users occasionally use semi-colons";23487;2024-02-14 00:00:00;"RD";"User";"email@domain.com";0;0;0;1;0;;;;;

When I do a Compose action to start to turn the values into an array, the Compose action adds another set of double quotes to every value. OK, I received help on how to deal with that with this formula.

split(replace(outputs('HTTP_-_Get_Surveys')?['body'], '""',''), decodeUriComponent('%0D%0A'))

This removes all the empty double quotes which prevented me from passing the empty value fields as null values, and passing empty text fields as "" every time. Wonderful. 

However, in 7 out of 800 records, the users have used semi-colons inside the text fields. When I pass the records through a semi-colon split like below, it breaks each of those records into the next field. (And if I wasn't doing it in a Apply To Each, would probably cause a cascade error for all records, but that's fortunately avoided)

split(item(), ';')

So what I need to do is remove every instance of ; that is inside a double quote. Is that possible? I haven't been able to find an if or replace function that does what I need, but I am very new at this. (If I remove all instances of double quote, it prevents the results from being passed later as a string.) Here's a test case:

5;5;5;5;5;6;2;5;3;"Text";"Remove only this ; please";

[Paulie78]

Parsing tricky CSV files is not really easy in Power Automate. I created a custom connector for parsing CSV Files:

The Ultimate Solution for Parsing CSV Files in Power Automate or Power Apps 

I created a test flow and ran your data through it:

It produces this output:

[
  {
    "Field1": "5",
    "Field2": "5",
    "Field3": "5",
    "Field4": "5",
    "Field5": "5",
    "Field6": "6",
    "Field7": "2",
    "Fiel8": "5",
    "Field9": "3",
    "Field10": "Text",
    "Field11": "Remove only this ; please"
  }
]

If you wanted to remove the semi-colon from Field 11, you could run the output through a select action and replace ; with nothing. like this:

It will make your flow much less complex, if you think it could work for you send me a PM.

Blog: tachytelic.net

YouTube: https://www.youtube.com/c/PaulieM/videos

If I answered your question, please accept it as a solution 😘

[AlexEncodian]

@AndreasO 

You could use a regex query to extract the text with the colon inserted.

Regex query

;"([^"]*;[^"]*)";

The query matches any text between ;" and "; which also includes a ; somehwere in the text. If there is no return (you can check this with a length() function), then proceed. If there is a return:

Then extract the text between the "" (ref: Utility - Extract Text between Values) which will give you 

Text field where us;ers occasionally use semi-colons\...remove the ; character and concatenate everything back together.

For some reason there are some \ inserted in the regex query....possibly a bug.

[AlexEncodian]

@AndreasO 

PS its inserting the \ as an escape character for the "...so you can feed that into the Extract Text between values too i.e. extract text between \" and \"