Quoting and escape rules by dialect
RFC 4180 / Excel / PostgreSQL COPY CSV
Quote a field when it contains a delimiter, a quote, or a CR/LF. Inside a quoted field, a literal quote is written as two adjacent quote characters.
id,name,note
1,"Smith, John","She said ""hello""."
2,Bob,"Line 1
Line 2"
A field with no special characters can be left unquoted. Producers that quote every field unconditionally also conform; consumers must accept both forms.
MySQL SELECT INTO OUTFILE
Default escape is backslash; the FIELDS ESCAPED BY clause overrides it.
id,name,note
1,"Smith, John","She said \"hello\"."
2,Bob,"Line 1\nLine 2"
Backslash-escaped CSV is not RFC 4180. Tools that round-trip via MySQL OUTFILE and then load with PapaParse default settings will mis-parse any field with a literal backslash.
TSV (IANA vs Unix)
The IANA spec backslash-escapes tab, newline, and backslash inside fields. The looser Unix convention forbids those characters entirely and uses no quoting at all, which is what awk -F'\t', cut, and sort assume.
Semicolon-delimited European CSV
Same quoting and escape rules as RFC 4180; only the field separator changes. Often paired with a decimal comma ( 1,50 instead of 1.50 ) in numeric fields, which stays as a string after parsing.
JSON output formats and where each is used
| Format | Specification | MIME type | File extension | Typical consumer |
|---|
| Array of objects | RFC 8259 / ECMA-404 | application/json | .json | JavaScript apps, REST API request bodies |
| JSONL (JSON Lines) | jsonlines.org | application/jsonl or application/x-ndjson | .jsonl, .ndjson | BigQuery bq load, DuckDB read_json_auto, OpenAI fine-tuning, streaming pipelines |
| Array of arrays | RFC 8259 | application/json | .json | Chart libraries (Plotly, Highcharts), tabular React components |
| Keyed object | RFC 8259 | application/json | .json | Lookup-table consumers, ID-indexed cache loads |
The four formats are not interchangeable. Each one trades a property the others keep.
| Property | Array of objects | JSONL | Array of arrays | Keyed object |
|---|
| Preserves all rows when keys collide | yes | yes | yes | no |
| Preserves header names | yes | yes | first row only | yes |
| Parseable line by line | no | yes | no | no |
| Parseable as a single JSON value | yes | no | yes | yes |
| Round-trips back to CSV cleanly | yes | yes | yes | no (collision lossy) |
| Preserves row order in strict JSON consumers | yes | yes | yes | not for numeric-string keys |
| Supports streaming insert | no | yes | no | no |
JSONL is the only line-orientable format; the other three require the consumer to buffer the entire document before parsing. For files larger than a few hundred megabytes destined for a data warehouse, JSONL is the only viable choice.