cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
drake
Advocate III
Advocate III

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

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!

1 ACCEPTED SOLUTION

Accepted Solutions

@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...

VJR_0-1648034317761.png

 

 

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.

View solution in original post

5 REPLIES 5
VJR
Super User
Super User

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?

 

VJR_0-1647661034465.png

 

 

 

@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!

@drake 

 

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

 

VJR_0-1647925316568.png

 

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

 

VJR_1-1647925384808.png

 

 

@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:

drake_0-1647956702105.png

 

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!

 

 

@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...

VJR_0-1648034317761.png

 

 

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.

Helpful resources

Announcements
Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Top Solution Authors
Top Kudoed Authors
Users online (1,497)