Handle commas, quotes and line breaks in CSV
Published and reviewed · Based on the current LocalShift implementation
A comma or line break inside a cell needs CSV quoting. A literal double quote inside a quoted cell is written as two double quotes. Use a parser rather than splitting the text on every comma.
Three cases that simple splitting breaks
A person's address may contain a comma, a note may contain a line break, and a quotation may contain quote characters. These are field contents, not necessarily record or column boundaries. RFC 4180 documents common CSV quoting conventions; actual producers can still differ.
In the example, each record has two columns even though one note spans two physical lines. Paste it into CSV to JSON to inspect the resulting strings.
name,note
Alex,"hello, world"
Sam,"He said ""yes"""
Lee,"line 1
line 2"Match the delimiter to the source
If your file uses semicolons between fields, select Semicolon. A comma that appears as punctuation inside a semicolon-separated file is not the column separator. LocalShift also offers a pipe separator for CSV tools.
For TSV, the delimiter is an actual tab character. Typing a backslash followed by t does not insert a tab into a file.
Diagnose a row-count error
When rows have different numbers of fields, first inspect the delimiter and any unclosed quotes. Adding arbitrary commas to make the count match can move values into the wrong columns.
With headers enabled, column names must be nonempty and unique. If the first line is data rather than a header, disable the header option; CSV to JSON then exports arrays of values. Blank-only rows are skipped.
Export and inspect
CSV to TSV parses cells before changing separators, preserving a quoted comma as part of its original value. Output fields may still need quotes when they contain tabs or line breaks. Confirm your destination supports that form.
For spreadsheet-bound output, review formula-like cells and the spreadsheet-safe option. Keep an untouched copy of the source so you can compare important records after conversion.
References
General format references; LocalShift-specific behavior is documented and tested separately.