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

User · Answer

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+"\"" )

User · Answer

There are several libraries  Here are two examples       x2750  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       x2750  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

User · Answer

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

User · Answer

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

User · Answer

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     rdeslonde mydomain com Richard  This is what I think    gets transformed into this   Email Fname Quoted   rdeslonde mydomain com Richard    This is what I think

User · Answer

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

User · Answer

String stringWithQuates          your comma separated string            this will retain the comma in CSV file

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

Examples related to java

Examples related to regex

Examples related to excel

Examples related to oracle

Examples related to csv