How to escape commas in a CSV File [with Examples]

Borislav Hadzhiev

Last updated: Apr 5, 2024
3 min


# How to escape commas in a CSV File [with Examples]

To escape commas in a CSV file so they don't break the formatting, wrap the entire field that contains a comma in double quotes "".

one,"two, three",four,five

The second field (two, three) contains a comma, so we had to wrap it in double quotes to treat the comma as a literal character and not as a separator.

Make sure to use standard double-quote characters "" and not custom ones “”.

If you encountered the issue in Excel, you can directly wrap the fields in which you want to escape commas in double quotes.

wrap excel fields in double quotes

# There should be a space before the double quote

There shouldn't be a space before the double quote.

In other words, the following is correct:

# ✅ correct one,"two, three",four,five

And, the following is incorrect.

# ⛔️ incorrect one, "two, three",four,five

The example above has a space before the opening double quote which may lead to parsing problems on macOS, Linux and Windows.

Here is an example in Python.
# ✅ correct a = 'one,two' b = 'three,four' c = 'five,six' employee = f'"{a}","{b}","{c}"' # "one,two","three,four","five,six" print(employee)

Notice that there isn't a space before the double-quote characters.

The following is incorrect because it would cause Excel to treat the opening double quote as a literal character and it won't be used to escape the comma.
# ⛔️ incorrect a = 'one,two' b = 'three,four' c = 'five,six' employee = f'"{a}" ,"{b}", "{c}"' # "one,two" ,"three,four", "five,six" print(employee)
Excel only uses double-quote characters to escape commas if the column is not preceded by a space.

Here are some of the Excel rules that you should follow:

  1. If the value contains a comma, newline or a double quote, then it should be wrapped in double quotes.

  2. If you want to escape double quote characters in the value, escape them with another double quote.

# The double quotes should be added around each field in which you want to escape the commas

The double quotes should be added around each field in which you want to treat commas as literal characters.

Here is an example of how to do this in JavaScript.

const employee = '"Bobby Hadz, programmer"'

We had to open the string with single quotes to be able to use double quotes inside it without having to escape them.

In JavaScript, you can also use backticks.

const employee = `"Bobby Hadz, programmer"`

Notice that we used backticks to wrap the string and not single quotes.

Template literal strings are most commonly used to interpolate variables into strings.

const job = 'programmer'; const employee = `"Bobby Hadz, ${job}"`; console.log(employee); // "Bobby Hadz, programmer"

The dollar sign and curly braces syntax enables you to embed and evaluate variables and expressions directly in a string.

You can read more about template literal strings in JavaScript in this article.

In most programming languages, you can escape quotes with a backslash character \.

const employee = "\"Bobby Hadz, programmer\""

Escaping the inner double quotes is necessary because otherwise, we'd terminate the string prematurely.

Note that a single, literal double-quote character " is interpreted as a pair of double quotes in CSV.

The following sheet:

Sheet: |One, Two|Three "four" Five|

Gets translated to the following in CSV.

CSV: "One, Two","Three ""four"" Five"

# Changing the separator to another character

If you don't want to wrap the fields in which you want to escape commas in double quotes, you'd have to change the separator character.

In most programming languages and Excel, you can set the sep parameter to a value different than a comma.

Commonly used separators are:

  • a semicolon ;
  • a pipe |

# Replacing the commas with a lower quote

If you're looking for a hacky solution, you can replace all commas in the strings with a custom comma.

Here is an example that uses the String.replaceAll method in JavaScript.

const employee = 'Bobby Hadz, programmer, 30'; const result = employee.replaceAll(',', '‚'); console.log(result); // 👉️ Bobby Hadz‚ programmer‚ 30

Notice that the second parameter we passed to replaceAll() is not standard comma.

Here is an equivalent example in Python.
employee = 'Bobby Hadz, programmer, 30' result = employee.replace(',', '‚') print(result) # 👉️ Bobby Hadz‚ programmer‚ 30

However, this solution is not recommended as it can lead to confusion.

# Additional Resources

You can learn more about the related topics by checking out the following tutorials:

I wrote a book in which I share everything I know about how to become a better, more efficient programmer.
book cover
You can use the search field on my Home Page to filter through all of my articles.