JSON Escape a CSV Column
JSON escaping turns a cell into the text you can safely paste inside a JSON string: quotes, backslashes and control characters all become escape sequences. Unescaping does the reverse. The control characters are the part that matters, because a real tab or line break inside a cell produces invalid JSON. It runs in this browser tab.
Turning the whole file into JSON instead? The CSV to JSON converter does that.
The characters that break it are the ones you cannot see
Everyone remembers to escape the double quote. Almost nobody remembers the control characters, and those are the ones that actually bring a pipeline down.
A CSV cell can legally contain a line break and a tab. Quote the field and the parser puts them straight into the value, which is correct and which most people never think about because a spreadsheet renders a multi-line cell as one tall row. Copy that value into a JSON string as-is and you have produced invalid JSON: the specification forbids a raw control character inside a string, so the parser at the other end fails on a character nobody can see in the source file.
The failure surfaces three systems downstream, in a log line that names a byte offset. Tracing it back to a product description that somebody pasted out of a Word document with a line break in it takes an afternoon.
The escaping here runs through JSON.stringify, which is the reference implementation and handles the whole set: the quote, the backslash, the forward slash where required, the backspace, the form feed, the newline, the carriage return, the tab, and every other control character below U+0020 as a \u sequence. Getting that list right by hand is exactly the kind of job to hand to the standard library.
Worked example: what actually changes
Take a cell holding a two-line note with a tab in it and a quoted phrase:
source cell line one
line two tabbed, he said "hi"
escaped "line one\nline two\ttabbed, he said \"hi\""
body only line one\nline two\ttabbed, he said \"hi\"The two-line cell becomes one line, which is the point: the escaped form is safe to put anywhere a single line of text is expected. The surrounding quotes are there by default because the result is then a complete JSON value you can paste straight into a payload. Switch Surrounding quotes to Escape only when you are pasting into a template that already has the quotes, such as a config file or a code generator.
And with Non-ASCII switched on:
Café Müller → Caf\u00e9 M\u00fcllerValid JSON either way, since a JSON string may hold any Unicode character directly. The escaped form exists for the systems that disagree: older parsers, log shippers that assume ASCII, and any pipeline where a value is going to pass through something with an opinion about encoding. If you have ever seen an accented character arrive as two garbled ones, this switch is the insurance against it.
Unescaping, and what it refuses
Going the other way is stricter than it looks, and deliberately so.
A cell that starts and ends with a double quote is treated as a JSON string literal and parsed as one. A cell that does not is treated as the body of a string literal, wrapped, and parsed the same way, so a value like caf\u00e9 that arrived without its quotes still unescapes to café. Both forms turn up in real files and both work.
What it will not do is hand you something that is not a string. Parse a cell holding {"a":1} and JSON gives you an object; a tool that accepted that would be quietly turning a text column into a column of stringified objects with different formatting from the source. So only string results are accepted, and a cell holding an object or a number comes back exactly as it was, treated as text, which is what it is as far as this page is concerned.
A genuinely broken escape sequence, such as "bad\qescape", is a failure: the cell keeps its original text and is counted, with the first few quoted back in the warning. That count is worth reading, because a column where a few rows fail to unescape usually holds two formats mixed together.
When you want this and when you want something else
This page escapes values. It is the right tool when the CSV is a source of strings that are going to be dropped into JSON by something that will not escape them for you.
- Building a payload by hand or with a template. A shell script, a spreadsheet formula, a code generator, an API test fixture. Anything where the quotes are written by you rather than by a serializer.
- Preparing localization strings or seed data that will be pasted into a JSON file.
- Flattening multi-line cells so a column is safe to put through a system that treats a newline as a record separator. The escaped form is always one line.
- Reading a column that arrived escaped, which is common when a value has been through a log pipeline or an API response that was flattened into a spreadsheet. Unescape it and the text is legible again.
What it is not is a CSV to JSON converter. If the goal is to turn the whole file into an array of objects, the CSV to JSON converter does that properly, escaping every value for you as part of serializing. Escaping a column here and then converting would escape everything twice, and you would get visible backslashes in the output.
Details
- Escaping always succeeds. Any string can be represented as a JSON string, so the escape direction has no failure mode. Only unescaping can refuse, and it says so when it does.
- Surrogate pairs are handled. An emoji is two code units and each escapes on its own into a
\usequence, which is exactly what a JSON parser expects to see and reassembles correctly. - Blank cells stay blank. They are skipped before the transform runs, so a column of missing values does not come back full of empty quoted strings.
- The CSV stays valid. An escaped value contains double quotes, which the CSV writer doubles up correctly on the way out, so the file still parses in any spreadsheet. The escaping and the CSV quoting are separate layers and neither interferes with the other.
- The original is kept by default, in a new column named
<column>_jsonbeside its source, so you can compare the pair before committing to it.
Frequently Asked Questions
Why does JSON escaping matter for a CSV?
Because a quoted CSV cell can legally hold a real line break or a tab, and JSON forbids a raw control character inside a string. Copy such a value into a JSON payload unescaped and you produce invalid JSON that fails at a byte offset three systems downstream, on a character nobody can see in the source file.
What exactly gets escaped?
Everything JSON.stringify escapes: the double quote, the backslash, backspace, form feed, newline, carriage return, tab, and every other control character below U+0020 as a unicode sequence. Using the standard library rather than a hand-written character list is the point, because the list is longer than people remember.
Should I keep the surrounding quotes?
Keep them when the result is a complete JSON value you will paste straight into a payload. Strip them when you are pasting into a template that already supplies the quotes, such as a config file or a code generator, where a second pair would break the syntax.
When would I escape non-ASCII characters?
When the receiving system is old or opinionated about encoding. JSON allows any Unicode character directly, so an accented letter is valid as it stands. The escaped form is insurance for older parsers, log shippers that assume ASCII, and pipelines where a value passes through something that might mangle the encoding.
Why did unescaping leave my cell unchanged?
Most likely because the cell holds a JSON object or a number rather than a string. Only string results are accepted, since parsing an object would quietly turn a text column into a column of re-serialized objects with different formatting. Cells like that come back exactly as they were.
What happens to a broken escape sequence?
The cell keeps its original text and is counted, with the first few quoted back in the warning. A column where a handful of rows fail to unescape usually holds two formats mixed together, which is worth knowing before the file goes anywhere.
Is this the same as converting the CSV to JSON?
No. This escapes values inside a column and leaves the file a CSV. The CSV to JSON converter turns the whole file into an array of objects and escapes every value as part of serializing. Running both would escape everything twice and leave visible backslashes in the output.
Does the file get uploaded?
No. There is no upload endpoint on this page. The file is read, escaped and rewritten by JavaScript in your own tab, and nothing is kept between visits.
Related
Safe to paste anywhere
Free, no account, no upload. Pick the column, pick the direction, take the CSV.
Back to the escaper