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

csv, excel, java, oracle, regex

Solution

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.

Problem

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?

Original source