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

# 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 "".

Copied!
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.

# 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:

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

And, the following is incorrect.

Copied!
# ⛔️ 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.

Copied!
# ✅ 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.

Copied!
# ⛔️ incorrect
a = 'one,two'
b = 'three,four'
c = 'five,six'


employee = f'"{a}" ,"{b}", "{c}"'

# "one,two" ,"three,four", "five,six"
print(employee)

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.

Copied!
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.

Copied!
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.

Copied!
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 \.

Copied!
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:

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

Gets translated to the following in CSV.

Copied!
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 |

Copied!
sep=|
ID|NAME|AGE|SALARY|COUNTRY|PROFESSION

# 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.

Copied!
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.

Copied!
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:

• Module 'csv' has no attribute 'reader' or 'writer' in Python
• IndexError: list assignment index out of range in Python
• csv.Error: line contains NULL byte Python error [Solved]
• _csv.Error: iterator should return strings, not bytes [Fix]
• Skip the header of a file with CSV Reader in Python
• Python: Compare two CSV files and print the differences