Properly escape a double quote in CSV

Question

I have a line like this in my CSV:

"Samsung U600 24"","10000003409","1","10000003427"

Quote next to 24 is used to express inches, while the quote just next to that quote closes the field. I'm reading the line with fgetcsv but the parser makes a mistake and reads the value as:

Samsung U600 24",10000003409"

I tried putting a backslash before the inches quote, but then I just get a backslash in the name:

Samsung U600 24\"

Is there a way to properly escape this in the CSV, so that the value would be Samsung U600 24" , or do I have to regex it in the processor?

Just double your quote. That's all – Your Common Sense Jul 23 '13 at 11:15 — Your Common Sense, Jul 23 '13 at 11:15

score 418 · Accepted Answer · answered Jul 23 '13 at 11:19

418

Use 2 quotes:

"Samsung U600 24"""

answered Jul 23 '13 at 11:19

user4035

21,015
10
54
86

155

RFC-4180, paragraph "If double-quotes are used to enclose fields, then a double-quote appearing inside a field must be escaped by preceding it with another double quote." – tommed Feb 18 '15 at 16:35
5

As tommed says you only need to add a single double quote to escape a double quote. You can use a command-line tool called csvfix to detect any lines which don't conform: csvfix check -nl -v [filename] – Sam Critchley Jun 30 '16 at 14:51
4

@SamCritchley I only see a single double quote being used to escape here. By "Use 2 quotes", user4035 means that 1 quote should be replaced with 2 quotes. By escaping double quotes with double quotes, you're effectively creating pairs of double quotes (2 double quotes). The final quote you see on the end is to terminate the field. – Zenexer Sep 09 '16 at 03:11
Really nice approach. But this requires me to modify the CSV file before parsing. – remy727 Sep 20 '20 at 15:43
if my csv string like: world,",hello – GarfieldCat Dec 26 '20 at 22:12
1

@GarfieldCat Try this: `"world,"",hello"` – user4035 Dec 27 '20 at 09:54
@codemicky that basically because your original file was not a properly formatted CSV. If you need to make that file work using an RFC-4180-compliant parser, you could try setting the quote character to something other than " (a null character works well) so that the parser will give you the unprocessed string `"Samsung U600 24""`, and then you could do the work to unquote it yourself. – xanderflood Dec 30 '20 at 16:38
This fixed the issue. Thanks –––– – –––– – –––– – Bishal Paudel Jan 19 '22 at 02:17
This is the proper answer – Brethlosze Apr 29 '22 at 23:05

score 12 · Answer 2 · edited Sep 12 '14 at 17:08

12

Not only double quotes, you will be in need for single quote ('), double quote ("), backslash (\) and NUL (the NULL byte).

Use fputcsv() to write, and fgetcsv() to read, which will take care of all.

edited Sep 12 '14 at 17:08

Peter-Paul van Gemerden

6,791
27
36

answered Feb 05 '14 at 11:37

Angelin Nadar

8,556
9
41
52

3

[This comment](http://php.net/manual/en/function.fputcsv.php#104980) on [the documentation page of `fputcsv()`](http://php.net/manual/en/function.fputcsv.php) shows how you could use `fputcsv()` when you want to output in csv format to the browser instead of an actual file. – Dennis van der Schagt Jan 07 '15 at 00:49
21

@Angelin Nadar, could you please add a source to your claim about the need to double single quote, backslash and NUL? I didn't find it in [RFC-4180](https://tools.ietf.org/html/rfc4180). – Petr 'PePa' Pavel Aug 13 '16 at 17:39
4

You don't need to actually escape single quotes etc. A proper CSV file doesn't even need to add double quotes around a field which contains only single quotes. If the CSV reader is properly implemented it should read the file correctly even with those symbols. – xji Jan 08 '18 at 23:06
11

Why was this answer ever voted up? The comment about escaping characters was never backed up and the original question doesn't ask about PHP. This only seems to be true for the string delimiter (and only for the chosen delimiter) when a program, such as Open Office, allows you to change it. – Dave F Dec 01 '18 at 22:07
2

*If you actually read it* RFC4180 notes that **there are various [other] specifications and implementations for the CSV format** and lists **at least 4**. The OP does not specify which particular format, so I feel it's unfair to downvote this answer based on presumptions about a specific document. – c z Aug 13 '20 at 11:40

score 2 · Answer 3 · edited Oct 12 '21 at 05:39

I have written in Java.

public class CSVUtil {
    public static String addQuote(
            String pValue) {
        if (pValue == null) {
            return null;
        } else {
            if (pValue.contains("\"")) {
                pValue = pValue.replace("\"", "\"\"");
            }
            if (pValue.contains(",")
                    || pValue.contains("\n")
                    || pValue.contains("'")
                    || pValue.contains("\\")
                    || pValue.contains("\"")) {
                return "\"" + pValue + "\"";
            }
        }
        return pValue;
    }

    public static void main(String[] args) {
        System.out.println("ab\nc" + "|||" + CSVUtil.addQuote("ab\nc"));
        System.out.println("a,bc" + "|||" + CSVUtil.addQuote("a,bc"));
        System.out.println("a,\"bc" + "|||" + CSVUtil.addQuote("a,\"bc"));
        System.out.println("a,\"\"bc" + "|||" + CSVUtil.addQuote("a,\"\"bc"));
        System.out.println("\"a,\"\"bc\"" + "|||" + CSVUtil.addQuote("\"a,\"\"bc\""));
        System.out.println("\"a,\"\"bc" + "|||" + CSVUtil.addQuote("\"a,\"\"bc"));
        System.out.println("a,\"\"bc\"" + "|||" + CSVUtil.addQuote("a,\"\"bc\""));
    }
}

Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). — Community, Oct 12 '21 at 05:42

score -2 · Answer 4 · answered Dec 13 '18 at 12:13

If a value contains a comma, a newline character or a double quote, then the string must be enclosed in double quotes. E.g: "Newline char in this field \n".

You can use below online tool to escape "" and , operators. https://www.freeformatter.com/csv-escape.html#ad-output

score -2 · Answer 5 · edited Oct 12 '21 at 05:41

I know this is an old post, but here's how I solved it (along with converting null values to empty string) in C# using an extension method.

Create a static class with something like the following:

    /// <summary>
    /// Wraps value in quotes if necessary and converts nulls to empty string
    /// </summary>
    /// <param name="value"></param>
    /// <returns>String ready for use in CSV output</returns>
    public static string Q(this string value)
    {
        if (value == null)
        {
            return string.Empty;
        }
        if (value.Contains(",") || (value.Contains("\"") || value.Contains("'") || value.Contains("\\"))
        {
            return "\"" + value + "\"";
        }
        return value;
    }

Then for each string you're writing to CSV, instead of:

stringBuilder.Append( WhateverVariable );

You just do:

stringBuilder.Append( WhateverVariable.Q() );

doesn't this miss the doubling up of embedded quotes? – Martin Smith Dec 07 '20 at 13:00 — Martin Smith, Dec 07 '20 at 13:00

Properly escape a double quote in CSV

5 Answers5

Linked

Related