What is the recommended approach to reading CSV files that contain double-quotes in the data?

(0) Share

Report

Posted on by drake

139

Example Data:

One|Two|Three
1|"2"|3
4|"5"|6

The "2" and "5" values will result in a delimiter error when reading with the Read From CSV File action in Power Automate Desktop. I found that replacing " with /" works (/ is an escape character) but then /" remains in the data and has to be removed later.

Is there a better way? Thanks!

Categories:

Power Automate Desktop

I have the same question (0)

All responses (5)

Answers (1)

Sort by

VJR 7,635 on at

Like
a
(1)

Report
Copy link

Link copied!

Hello @drake

Could you help me understand a bit more.

Is your delimiter a pipe symbol | as shown above, or is it a comma or a double quote?

This is how it is reading into a datatable if the default comma is given as a delimiter.

And then after that what do you want (or do not want) in the data?

Was this reply helpful? Yes No
drake 139 on at

Like
a
(0)

Report
Copy link

Link copied!

@VJR Thanks for the reply and the delimiter is the pipe symbol: |

The double quotes are causing the problem. To get around this, we are currently having to escape the double quotes by replacing all " with \"

This causes issues, however, because then the escape character is now in our data, so it has to be removed again.

I was wondering if there is a better way. Thanks!

Was this reply helpful? Yes No
VJR 7,635 on at

Like
a
(1)

Report
Copy link

Link copied!

@drake

The CSV file as you have described looks as below in Notepad

An after reading in PAD giving | as delimiter, it reads as below.

So could you tell me what problem are you facing?

I am using PAD 2.17

Was this reply helpful? Yes No
drake 139 on at

Like
a
(0)

Report
Copy link

Link copied!

@VJR Please use this in your csv file:

1|2|3|4|5|6|7|8|9|10|11|12|13|14
4342222|ABA dalph AZW|aba_dalph_azq "AGEE", "A", "21-FEB-2020", "22-FEB-2020", "cccc.aaaaa@abc.com", 1, 3, 12355334.00|abdf_fkdjfd_wkgjskd|"AAAAAAAAAA", "D", "21-FEB-2022", "24-FEB-2020", "dddd.caaaa@abc.com", 9, 7, 312312321312.00|2020-01-20T02:01:01-06:11|2020-02-20T01:22:33-44:55|2020-02-20T33:44:55-66:77|AAA Bagedd|efdsfdsfd|fds_fdd_fdsfdfd_fdfd_dfd|3444345|ABA_BB|YY

Here are the settings I am using in the Read From CSV File action in Power Automate Desktop:

Using the above data for the csv file will fail. But when you change:

|"AAAAAAAAAA",

To what is listed below (remove first double quote), this resolves the issue.

|AAAAAAAAAA",

We have data that includes |" so we are 1) confused why this error is happening and 2) wondering how to resolve it without having to replace all " with \" (this is messy because then you have to go back and remove the \ from the data after the csv is imported. Thanks!

Was this reply helpful? Yes No
Verified answer

VJR 7,635 on at

Like
a
(1)

Report
Copy link

Link copied!

@drake , I now see what you are trying to say and was also able to recreate the error you are facing.

Tried a few things - did not work as expected. Also manually imported the csv into excel and it was automatically removing the " before and after the AAA...

Maybe you can report a bug on the forum.

And in the interim I hope you have considered the option of using the Replace Text action on the imported csv to get it back to its original state.

Was this reply helpful? Yes No