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

Question

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?

Answer 1

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.}