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.

Difinity Conference 2022

Difinity Conference 2022

Register today for two amazing days of learning, featuring intensive learning sessions across multiple tracks, led by engaging and dynamic experts.

European SharePoint Conference

European SharePoint Conference

The European SharePoint Conference returns live and in-person November 28-December 1 with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Top Solution Authors
Top Kudoed Authors
Users online (2,893)