4

I am using OpenCSV.

I have a CSVReader trying to parse a CSV file.
That file has quote char " and separator char , and escape char also ".

Note that the CSV contains cells like: 

"ballet 24"" classes"
"\"

which actually represent these values: 

ballet 24" classes
\

Example: 

"9/6/2014","3170168","123652278","Computer","2329043290","Bing and Yahoo! search","22951990789","voice lesson","Broad","0.00","0","1","3.00","0.00","0.00","0.00","7","0","",""
"9/6/2014","3170168","123652278","Smartphone","2329043291","Bing and Yahoo! search","22951990795","ballet class","Broad","0.00","0","1","1.00","0.00","0.00","0.00","0","0","",""
"9/6/2014","3170168","123652278","Smartphone","2329043291","Bing and Yahoo! search","22951990797","ballet 24"" classes","Broad","0.00","0","1","1.00","0.00","0.00","0.00","0","0","",""
"9/6/2014","3170168","123652278","Smartphone","2329043291","Bing and Yahoo! search","22951990797","ballet classes","Broad","0.00","0","1","1.00","0.00","0.00","0.00","0","0","",""
"9/6/2014","3170168","123652278","Computer","2329043291","Bing and Yahoo! search","22951990817","\","Broad","0.00","0","1","1.00","0.00","0.00","0.00","5","0","",""
"9/6/2014","3170168","123652278","Computer","2329043293","Bing and Yahoo! search","22951990850","zumba classes","Broad","0.00","0","1","7.00","0.00","0.00","0.00","5","0","",""
"9/6/2014","3170168","123652278","Smartphone","2329043293","Bing and Yahoo! search","22951990850","zumba classes","Broad","0.00","0","4","1.00","0.00","0.00","0.00","5","0","",""
"9/6/2014","3170168","123652278","Computer","2329043293","Bing and Yahoo! search","22951990874","zumba lessons","Broad","0.00","0","1","2.00","0.00","0.00","0.00","0","0","",""

My problem is that I cannot specify " for escape char to the CSVReader constructor
(i.e. make it the same as the quote char).
If I do so, the CSVReader simply goes crazy, and it reads the whole CSV line as a single CSV cell.

Has anyone else encountered this bug and how to get around it?!

peter.petrov
  • 38,363
  • 16
  • 94
  • 159

3 Answers3

4

It will work if you go with the default settings for CsvReader.

Check this open bug they have: sourceforge.net/p/opencsv/bugs/83:

Actually, it works fine, just not the way you think. Its defaults are comma for separator, quote for the quote character, and backslash for the escape character. However, it understands two consecutive quote characters as an escaped quote character. So, if you just go with the defaults, it will work fine.

By default, it is able to escape double quote with double quote, but your 'true' escape character must still be something else.

So the following works:

CSVReader reader = new CSVReader(new FileReader(App.class.getClassLoader().getResource("csv.csv").getFile()), ',','"','-');
  • comma as separator
  • double quote as quote char
  • dash (any other character) as escape character

At first I put '\' as escape character, but then, your field "\" would need to be modified to escape the escape character.

alexbt
  • 16,415
  • 6
  • 78
  • 87
  • 1
    at first I defined the \ as the escape character, but one of your field uses this character as a normal character. So I changed it to another dummy character (ie: dash) and it worked. – alexbt Jul 12 '16 at 11:01
  • I already figured that approach myself (I used some exotic Unicode char /which would never appear in my CSV files/, and not dash), but that's nonsense, right? Why is there no option to have the quote char be also the escape char? Thanks for the bug link. So it seems they don't even understand it's a bug. As one person wrote in the bug "OpenCSV can write CSV files which it cannot read", that's the big problem here. CSVWriter is more flexible than CSVReader, there's quite some asymmetry there. – peter.petrov Jul 12 '16 at 14:02
2

The CSVReader is not fully RFC4180 compliant. Use their newer CSV reader (RFC4180Parser):

RFC4180Parser rfc4180Parser = new RFC4180ParserBuilder().build();
CSVReaderBuilder csvReaderBuilder = new CSVReaderBuilder(
    new FileReader("input.csv"));

CSVReader reader = csvReaderBuilder
    .withCSVParser(rfc4180Parser)
    .build();

To read a String line formatted as a CSV:

String test = "ballet 24\"\" classes";
String[] columns = new RFC4180Parser().parseLine(test);

To use the reader (an alternative is reader.readNext()):

for (String[] line : reader.readAll()) {
  for (String s : line) {
    System.out.println(s);
  }
}

See http://opencsv.sourceforge.net/#rfc4180parser for more details.

Code taken from GeekPrompt

A Jar of Clay
  • 5,622
  • 6
  • 25
  • 39
-1

It cannot be done through CSVReader

from pyspark.sql.session import SparkSession

spark = SparkSession(sc)
rdd = spark.read.csv("csv.csv", multiLine=True, header="False", encoding='utf-8', escape= "\"")
Val
  • 345
  • 2
  • 14