How should I escape commas and speech marks in CSV files so they work in Excel

Question

I m generating a CSV file  delimited by commas rather than tabs   My users will most likely open the CSV file in Excel by double clicking it  My data may contain commas and speech marks  so I m escaping those as follows   Reference  Title  Description 1   My little title    My description  which may contain   speech marks   and commas   2   My other little title    My other description  which may also contain   speech marks   and commas     As far as I know that s always been the way to do it  Here s my boggle  when I open this file in Excel 2010 my escaping is not respected  Speech marks appear on the sheet  and the comma causes new columns

User · Answer

Below are the rules if you believe it s random  A utility function can be created on the basis of these rules    If the value contains a comma  newline or double quote  then the String value should be returned enclosed in double quotes  Any double quote characters in the value should be escaped with another double quote  If the value does not contain a comma  newline or double quote  then the String value should be returned unchanged

User · Answer

We eventually found the answer to this   Excel will only respect the escaping of commas and speech marks if the column value is NOT preceded by a space  So generating the file without spaces like this     Reference Title Description 1  My little title   My description  which may contain   speech marks   and commas   2  My other little title   My other description  which may also contain   speech marks   and commas         fixed the problem  Hope this helps someone

User · Answer

Even after double quotes  I had this problem for a few days   Replaced Pipe Delimiter with Comma  then things worked fine

User · Answer

According to Yashu s instructions  I wrote the following function  it s PL SQL code  but it should be easily adaptable to any other language    FUNCTION field str IN VARCHAR2  RETURN VARCHAR2 IS     C NEWLINE CONSTANT CHAR 1          -- newline is intentional      v aux VARCHAR2 32000       v has double quotes BOOLEAN      v has comma BOOLEAN      v has newline BOOLEAN  BEGIN     v has double quotes    instr str        gt  0      v has comma    instr str       gt  0      v has newline    instr str  C NEWLINE   gt  0       IF v has double quotes OR v has comma OR v has newline THEN         IF v has double quotes THEN             v aux    replace str                    ELSE             v aux    str          END IF          return      v aux           ELSE         return str      END IF  END

User · Answer

Single quotes work fine too  even without escaping the double quotes  at least in Excel 2016    text with spaces  and a comma   more text with spaces   spaces and  quoted text  and more spaces   nospaces   NOSPACES1234    Excel will put that in 5 columns  if you choose the single quote as  Text qualifier  in the  Text to columns  wizard

[java] How should I escape commas and speech marks in CSV files so they work in Excel?

Examples related to java

Examples related to excel

Examples related to csv

Examples related to special-characters