Change CSV Delimiter

@Amirin Power Automate actions have a maximum content of 100MB to 200MB.

If you are going over that I suggest looking into larger-scale solutions like Azure Data Factory or Dataflows.

@sree813 If your data is tab delimited, then you should not need this change delimiter process as you can split the CSV on tabs

https://www.tachytelic.net/2021/02/power-automate-parse-csv/?amp

@takolota your solution works like a charm. Thanks for that. But I have a new issue now. 
My current CSV file is a tab delimited one i.e.., I have file of 1500 rows where instead of separate columns all data is present in one column in multiple rows. How to convert to excel please?

Thanks.

@takolota Hello

I hope you can help me with this, does this CHANGE DELIMITER can handle 100k plus files? I tried, but successful run is only if the row data is 50k.. 

@takolota 

How sad, Please let us know once you already figured out a solution.

This is a big help, until I encounter this problem. I hope you can come up with a solution soon.. Thank You for your effort.

@Amirin 

No, as you might see above I thought I had a path forward with it, but continued thinking on it & kept finding errors in the logic/things that mean it wouldn’t work.

Hi @takolota  . 

Do you have a solution for lonewalker problem already? I have same problem, in a column there is a line break. your flow will detect it as a new line..

 And this is the result..

@Lonewalker25 

You know, I bet one could do some rough workarounds. Right after all lines are split by the line linebreak, one could use a Select action with the From set to a range(0, length(OutputWithAllLines)) to get a count for each line.

Then use the Map input to iterate over each with OutputWithAllLines?[item()] but when referencing that:

Check if the current item line length is equal to the length of the header line, if yes just return OutputWithAllLines?[item()], if no continue...

... check the length of the previous item count's line to see if it is equal to the header line length, if yes return a blank "", if no then concat(OutputWithAllLines?[item()], OutputWithAllLines?[sub(item(),1)]) to combine the current item line with the previous item line.

Note- this would also require an outer condition to check if the current item() is 0, if yes then only execute if length equals header line length OutputWithAllLines?[item()], else return blank ""

Then have a Filter array action filter out any blanks

That should then handle some cases where there is an in-data line linebreak.
But it would only handle a single instance of that in-data set of characters in a single column. It would still break if there were multiple instances of those in-data characters. To adjust for more instances would require an ever increasing number of expressions if one wanted to keep it fast & in a Select action.
I'm sure there is another way to handle it more slowly & only for smaller datasets with a Do Until loop & something that would only write the next line to an array variable once a concatenation of any number of lines met the length of the header line.

Or maybe something could be done by joining all lines together on the column delimiter & then chunk that big array into an array of arrays the same length as the header line split. That way each chunk should contain each real line that could then be joined in a Select.

I’ll have to check if that can fit/integrate in after the in-data commas are handled though as it relies on a length calculation that relies on only real delimiter characters remaining.

Or maybe something could be done by putting many of the previous lines in each Select Map instance as long as their calculated item number isn’t less than 0 and if the current item line length does not equal the header line length, then check if the next item line length equals the header line length or if the current item is the last item, if yes then join many of the next lines on the column delimiter & take only the header line length number of things from a column delimiter split on that join, if no then return a blank "". All else if the current item line length does match the header length, then just return the current item line.
Then remove blanks "".
But I'd have to see if anything like that could fit in after the column delimiter change, as it relies on all the in-data column delimiters being handled before-hand.

Thanks, takolota for your quick reply. 

The CSV we use is generated from a software application. Column B3 in my screenshot is a "free-text" field from the application. Many people have the habit of doing a carriage return after they type a paragraph of text, and B3 is a result of that. 

In Power Autoamte, the issue occurs right after the native "base64ToSTring" function. As you said, there's no easy way to parse the data correctly, but there could be a way to fix the data after it is parsed. 

This is how the data looks like after the base64ToString step:

data1,I also looked over a problem with in-data trailing comm,ABC123

data2,"decodeUriComponent expression(s)

 ",EDF443

data3,just can't handle both in one dataset,GXG421

@Lonewalker25 

The defaults for the template are set to check for a combination of a carriage return & a linebreak so /r/n to split the rows. 
Does the user-inserted linebreak include a carriage return too? Or did you change the default because the line delimiters for your CSV actually only use a single /n linebreak & thus the actual line delimiter matches a user-entered linebreak?

Unfortunately if the line delimiter exactly matches some text or thing a user has entered, I do not know of a simple way to fix that as I don't see any simple way to distinguish the two instances of characters.