Dreamer Dreamer - 2 months ago 13
Java Question

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

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

There are several libraries. Here are two examples:


❐ The YAML file 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.

Comments