topic Re: Python Read csv - Don't consider comma when its within the quotes, even if the quotes are not immediate to the separator in Data Engineering

Python Read csv - Don't consider comma when its within the quotes, even if the quotes are not immediate to the separator

ASN — Thu, 09 Jun 2022 21:39:24 GMT

I have data like, below and when reading as CSV, I don't want to consider comma when its within the quotes even if the quotes are not immediate to the separator (like record #2). 1 and 3 records are good if we use separator, but failing on 2nd record

Input:

col1, col2, col3

a, b, c

a, b1 "b2, b3" b4, c

"a1, a2", b, c

Output:

Re: Python Read csv - Don't consider comma when its within the quotes, even if the quotes are not immediate to the separator

Anonymous — Thu, 09 Jun 2022 23:39:01 GMT

https://spark.apache.org/docs/latest/sql-data-sources-csv.html#data-source-option

Escape quotes is the config you're looking for.

Re: Python Read csv - Don't consider comma when its within the quotes, even if the quotes are not immediate to the separator

ASN — Fri, 10 Jun 2022 13:36:53 GMT

Hi Joseph... I tried but a, b1 "b2, b3" b4, c row needs to convert to 3 columns as below (Expected output), but b series data are divided into 2 columns instead of single column - requirement is to ignore the comma inside quotes in 2nd column.

Expected output:

1) a

2) b1 "b2, b3" b4

3) c

Actual output:

1) a

2) b1 "b2

3) b3" b4

Thanks,

Satya

Re: Python Read csv - Don't consider comma when its within the quotes, even if the quotes are not immediate to the separator

dhara1314 — Wed, 29 Jun 2022 22:19:06 GMT

Following approach can be taken -

Replace your delimiter from comma to something else like pipe , semicolon
Provide escapeQuote option as true when you use spark.read

Re: Python Read csv - Don't consider comma when its within the quotes, even if the quotes are not immediate to the separator

jose_gonzalez — Fri, 29 Jul 2022 18:40:50 GMT

Hi @SATYANARAYANA ALAMANDA,

Just a friendly follow-up. Did any of the responses help you to resolve your question? if it did, please mark it as best. Otherwise, please let us know if you still need help.

Re: Python Read csv - Don't consider comma when its within the quotes, even if the quotes are not immediate to the separator

Pholo — Thu, 04 Aug 2022 14:06:15 GMT

Hi, I think you can use this option for the csvReadee

spark.read.options(header = True, sep = ",",  unescapedQuoteHandling = "BACK_TO_DELIMITER").csv("your_file.csv")

especially the unescapedQuoteHandling. You can search for the other options at this link

https://spark.apache.org/docs/latest/sql-data-sources-csv.html