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.

Ausgleich

Answer 1

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.

Ausgleich

This is a problem I have run into before and I always forget about it. Excel should discontinue this behavior, as it simply creates confusion and is not a helpful rule to have in the first place. That or csv's should have mandatory no whitespace in between commas. – Rearmost

Answer 2

Below are the rules if you believe it's random. A utility function can be created on the basis of these rules.

1. If the value contains a comma, newline or double quote, then the String value should be returned enclosed in double quotes.

2. Any double quote characters in the value should be escaped with another double quote.

3. If the value does not contain a comma, newline or double quote, then the String value should be returned unchanged.

Ernestinaernestine

Does it hurt anything to quote-wrap something that doesn't have commas, double-quotes or newlines? – Stilla

No ErikReppen I don't think that would hurt normally. I belongs to a java background where its recommended not to create new strings by replacing text in old one's as they have their own share inside heap memory. If you want, you can unconditionally replace all values and tell us if it causes any issue although it shouldn't. – Ernestinaernestine

A field that begins or ends with a space should be quoted. – Preterit

Answer 3

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, '"') > 0;
    v_has_comma := instr(str,',') > 0;
    v_has_newline := instr(str, C_NEWLINE) > 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;

Fridafriday

Answer 4

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)

Constance

Answer 5

Even after double quotes, I had this problem for a few days.

Replaced Pipe Delimiter with Comma, then things worked fine.

Gley

You should give a sample of what worked for you so the OP could copy and paste it if needed. – Damiendamietta