How to escape comma and double quote at same time for CSV file?

71
votes

I am writing a Java app to export data from Oracle to csv file

Unfortunately the content of data may quite tricky. Still comma is the deliminator, but some data on a row could be like this:

| ID    |   FN    |   LN   |  AGE   |  COMMENT                   |
|----------------------------------------------------------------|
| 123   |  John   |  Smith |   39   | I said "Hey, I am 5'10"."  |
|----------------------------------------------------------------|

so this is one of the string on the comment column:

I said "Hey, I am 5'10"."

No kidding, I need to show above comment without compromise in excel or open office from a CSV file generated by Java, and of course cannot mess up other regular escaping situation(i.e. regular double quotes, and regular comma within a tuple). I know regular expression is powerful but how can we achieve the goal with such complicated situation?

7
javaregexexceloraclecsv
I'm pretty sure Oracle (and the rest of the major RDBMSs) has an 'export' function that performs this function. Barring that, there's probably an open-source version that would interface with pretty much anything through JDBC, given how common this type of functionality is needed. – Clockwork-Muse
Thanks X-Zero but here the job actually relies on the controller side. But still very helpful idea :) – Dreamer
For Swift, github.com/Daniel1of1/CSwiftV is rfc4180 – Fattie

7 Answers

116
votes

There are several libraries. Here are two examples:

❐ Apache Commons Lang

Apache Commons Lang includes a special class to escape or unescape strings (CSV, EcmaScript, HTML, Java, Json, XML): org.apache.commons.lang3.StringEscapeUtils.

  • Escape to CSV

    String escaped = StringEscapeUtils
    .escapeCsv("I said \"Hey, I am 5'10\".\""); // I said "Hey, I am 5'10"."

System.out.println(escaped); // "I said ""Hey, I am 5'10""."""

  • Unescape from CSV

    String unescaped = StringEscapeUtils
    .unescapeCsv("\"I said \"\"Hey, I am 5'10\"\".\"\"\""); // "I said ""Hey, I am 5'10""."""

System.out.println(unescaped); // I said "Hey, I am 5'10"."

* You can download it from here.

❐ OpenCSV

If you use OpenCSV, you will not need to worry about escape or unescape, only for write or read the content.

  • Writing file:

    FileOutputStream fos = new FileOutputStream("awesomefile.csv"); 
OutputStreamWriter osw = new OutputStreamWriter(fos, "UTF-8");
CSVWriter writer = new CSVWriter(osw);
...
String[] row = {
    "123", 
    "John", 
    "Smith", 
    "39", 
    "I said \"Hey, I am 5'10\".\""
};
writer.writeNext(row);
...
writer.close();
osw.close();
os.close();

  • Reading file:

    FileInputStream fis = new FileInputStream("awesomefile.csv"); 
InputStreamReader isr = new InputStreamReader(fis, "UTF-8");
CSVReader reader = new CSVReader(isr);

for (String[] row; (row = reader.readNext()) != null;) {
    System.out.println(Arrays.toString(row));
}

reader.close();
isr.close();
fis.close();

* You can download it from here.

35
votes

Excel has to be able to handle the exact same situation.

Put those things into Excel, save them as CSV, and examine the file with a text editor. Then you'll know the rules Excel is applying to these situations.

Make Java produce the same output.

The formats used by Excel are published, by the way...

****Edit 1:**** Here's what Excel does
****Edit 2:**** Note that php's fputcsv does the same exact thing as excel if you use " as the enclosure. 

[email protected]
Richard
"This is what I think"

gets transformed into this:

Email,Fname,Quoted  
[email protected],Richard,"""This is what I think"""
15
votes

Thanks to both Tony and Paul for the quick feedback, its very helpful. I actually figure out a solution through POJO. Here it is:

if (cell_value.indexOf("\"") != -1 || cell_value.indexOf(",") != -1) {
    cell_value = cell_value.replaceAll("\"", "\"\"");
    row.append("\"");
    row.append(cell_value);
    row.append("\"");
} else {
    row.append(cell_value);
}

in short if there is special character like comma or double quote within the string in side the cell, then first escape the double quote("\"") by adding additional double quote (like "\"\""), then put the whole thing into a double quote (like "\""+theWholeThing+"\"" )

3
votes

You could also look at how Python writes Excel-compatible csv files.

I believe the default for Excel is to double-up for literal quote characters - that is, literal quotes " are written as "".

3
votes

If you're using CSVWriter. Check that you don't have the option 

.withQuotechar(CSVWriter.NO_QUOTE_CHARACTER)

When I removed it the comma was showing as expected and not treating it as new column

2
votes
"cell one","cell "" two","cell "" ,three"

Save this to csv file and see the results, so double quote is used to escape itself

Important Note

"cell one","cell "" two", "cell "" ,three"

will give you a different result because there is a space after the comma, and that will be treated as "

-1
votes
String stringWithQuates = "\""+ "your,comma,separated,string" + "\"";

this will retain the comma in CSV file