Unanswered

"Create CSV Table" with semicolon as delimiter

(0) Share

Report

Posted on by kljunior

I have a Power Automate flow that lists all rows from "Account" table, creates a CSV table and then creates the file on a file server.

The "Create CSV Table" action uses comma as a delimiter and I want to use semicolon instead, transforming the data/changing the delimiter with functions such as "Find and replace" would not work because the column data contains commas so it would mess up the data.

Does anybody has any options on how I can proceed to archive this?

The flow

Categories:

General topics

I have the same question (0)

All responses (8)

Answers (1)

SamLed 2,338 Moderator on at

Like (0)

Report
Hi,

Do it that way:

Customize CSV columns and add '#' after each value except last one.
Use a compose to replace header commas and lines '#,' with following expression:
concat(replace(first(split(body('Create_CSV_table'),decodeUriComponent('%0A'))),',',';'), decodeUriComponent('%0A'), replace(join(skip(split(body('Create_CSV_table'),decodeUriComponent('%0A')),1), decodeUriComponent('%0A')),'#,',';'))

Result:

______________________________________________________________
If I have answered your question, please Accept the post as solution.
If you like my response, please Thumbs Up.

3 people found this reply helpful.

Was this reply helpful? Yes No
wskinnermctc 6,519 Moderator on at

Like (0)

Report

To change the delimeter of a CSV file, you have to start it with sep={delimiter}
sep=; (semi-colon delimiter)
sep=| (vertical bar delimiter)

I made an example that is using a String type Variable. You are basically appending things to the string and then the string will be converted to a CSV file when the file is created.

You need to put a blank row/line break before each new row so that it will know to go to the next row. You can put the blank after the data, but I think it has issues if there is a blank value at the end of the row.

I'm putting screenshots of the steps below.

Example Excel Table DataOverall Example FlowCreate Variable with Delimiter and then Name ColumnsAdd the Data into Columns using the delimiterExample CSV Output

I don't know about performance with this if your table has 1000's of rows, but the logic will be the same however you get it to work. You need the csv to start with sep=; followed by the data. So you might be able to create an array or something and then append sep=; to the top and run that as a csv.

Let me know if this works for you,

Was this reply helpful? Yes No
kljunior 14 on at

Like (0)

Report

I haven't tested it yet, but I believe this solution might not be suitable for our needs. This is primarily due to the fact that the exported file contains over 100 columns, and we frequently make changes to these columns. Your proposed solution appears to be quite labor-intensive and intricate to sustain. Before considering this option, I plan to explore a few alternative methods. Should I eventually choose this solution, I'll be sure to mark it as the accepted one.

Thank you for your response, I genuinely appreciate your input.

Was this reply helpful? Yes No
kljunior 14 on at

Like (0)

Report

I ran a test with this solution, it seems to be partly working but it skips some of the data that contains commas.. For example, I have a field that contains data formatted as (15,31). When the function runs it splits them into to fields is shown below:

You can see that the first and the second column is used because it is splitting that line..

Was this reply helpful? Yes No
Verified answer

SamLed 2,338 Moderator on at

Like (1)

Report

Hi @kljunior,

Yeah, there is a drawback in my expression when adding "#" after a text column in Create CSV table, it includes "#" in quotes, try this one:

concat(replace(first(split(body('Create_CSV_table'),decodeUriComponent('%0A'))),',',';'), decodeUriComponent('%0A'), replace(replace(join(skip(split(body('Create_CSV_table'),decodeUriComponent('%0A')),1), decodeUriComponent('%0A')),'#,',';'), '#",', '";'))

NB: Of course, if "#" is present in your data, use another one. Otherwise, you can use @wskinnermctc method which is a nice one, it guarantees an optimal functioning without having to wonder if the used character is present in data. Even if I prefer less action use in my solutions, his solution is better if you don't have control over data entered.

If I have answered your question, please Accept the post as solution.
If you like my response, please Thumbs Up.

Was this reply helpful? Yes No
kljunior 14 on at

Like (0)

Report

That fixed the problem, thanks a lot!

Was this reply helpful? Yes No
Loevefisk 6 on at

Like (0)

Report

Thanks wskinnermctc, It work for me to. I does have 1 problem, I can't get the date and times right It shows:
452.082.956.944.444

What can I do to make it right?

Another problem, I can't get the name of my CSV file correct. I would like to get the name "RefrenceNo1".CSV the Refrence number is new everytime and it is to fine in the column "RefrenceNo1" A can make it work with the Attachment from email, but not as a refrence no frem a column. What do I do wrong?

Was this reply helpful? Yes No
fpchd 14 on at

Like (0)

Report

Thanks @SamLed ,
this is a wonderful easy and fast way to change delimiter in csv in Power Automate!
Happy to found your solution after some headaches around this problem!

1 people found this reply helpful.

Was this reply helpful? Yes No