Clean a Database CSV Export

Running SELECT * FROM users INTO OUTFILE '/tmp/users.csv' writes a TSV (tab-separated values) — the .csv extension is just a filename choice; the delimiter is \t because MySQL defaults to tab. Excel double-click merges every column into row 1 because Excel uses comma as its default delimiter.

Source MySQL command (no FIELDS TERMINATED BY):
SELECT id, name, email FROM users
  INTO OUTFILE '/tmp/users.csv';

Actual file contents (TSV — note the tabs):
1\tJohn\tjohn@x.com
2\tJane\tjane@x.com

Excel double-click: every row appears as one merged cell.

Cleaner output (auto-detects tab delimiter, outputs comma-CSV):
id,name,email
1,John,john@x.com
2,Jane,jane@x.com

psql on Windows writes server-side files with CRLF line endings. pandas.read_csv() accepts either, but some text-only utilities (awk, simple split calls) see \r as part of the last column value. The cleaner's outputLineEnding: lf normalises.

Source (psql on Windows, written to a file):
id,name,email\r\n
1,John,john@x.com\r\n

Python pandas reads correctly (handles either ending), but:
  awk -F, '{print $3}' users.csv
  →  'john@x.com\r'   ← trailing carriage return is part of the value

Cleaner config: outputLineEnding: lf

Output:
id,name,email\n
1,John,john@x.com\n

CHAR(10) columns in PostgreSQL pad every value to exactly 10 characters with trailing spaces. \copy writes the padded form. Downstream string comparisons (WHERE code = 'ABC' vs the padded 'ABC ') fail until trimmed.

Source PostgreSQL table:
  CREATE TABLE products (code CHAR(10), name VARCHAR(100));
  INSERT INTO products VALUES ('ABC', 'Widget');

psql \copy output:
code,name
"ABC       ",Widget

Cleaner config: trimWhitespace: true (default)

Output:
code,name
ABC,Widget

SQLite's .mode csv writes UTF-8 without a BOM. Excel on Windows defaults to Windows-1252 and mojibakes any accented character. Same issue as Google Forms and Mailchimp — the data isn't corrupted, the encoding declaration is missing.

Source SQLite query result:
id,name
1,José Müller

Excel-on-Windows display (Windows-1252 fallback):
id,name
1,José Müller

Cleaner adds BOM on output → Excel auto-detects UTF-8 → displays correctly.

PostgreSQL text-format COPY writes \N for NULL. CSV-format COPY writes unquoted empty string. The cleaner is text-only — it preserves whatever's in the cell. If your downstream tool needs empty-for-NULL, chain with csv-find-replace.

Source (psql in text-format COPY mode):
id\tname\temail
1\tJohn\t\N
2\tJane\tjane@x.com

Cleaner output (preserves `\N` verbatim):
id,name,email
1,John,\N
2,Jane,jane@x.com

Follow-up: csv-find-replace with find: \\N, replace: empty,
  caseSensitive: true → converts to empty-string NULL.

MySQL's SELECT INTO OUTFILE without OPTIONALLY ENCLOSED BY '"' doesn't quote strings. A TEXT value containing a newline becomes a row break — every subsequent column shifts up one row. The cleaner's repairRows: true pads/trims to header width but the underlying multi-line value needs manual review.

Source MySQL row:
  id=42, notes='Line 1\nLine 2', email='x@y.com'

Unquoted SELECT INTO OUTFILE writes:
42\tLine 1\nLine 2\tx@y.com

  ↓ Parser sees this as two rows:
42\tLine 1
Line 2\tx@y.com

Fix at source: use OPTIONALLY ENCLOSED BY '"' or switch to mysqldump.
Fix at cleaner: repairRows: true (default) pads short rows to
  header width — the data still needs manual review.

MySQL's FIELDS TERMINATED BY defaults to \t (tab). A SELECT INTO OUTFILE 'file.csv' without explicit FIELDS TERMINATED BY ',' produces a TSV with a .csv extension. Excel double-click merges all columns. The cleaner's delimiter: auto detects this; force delimiter: ',' if your downstream needs comma-CSV.

By default, MySQL writes string values **without enclosure**. Any value containing the field delimiter or a newline breaks the row. Always use OPTIONALLY ENCLOSED BY '"' (or just ENCLOSED BY '"' if you don't mind quoting numeric fields too) for safe CSV output. The cleaner can repair some damage via repairRows but multi-line embedded values need manual review.

Older mysqldump versions default to utf8 (which is actually MySQL's 3-byte alias for UTF-8 BMP only — emoji and many CJK chars need 4-byte utf8mb4). If your tables are utf8mb4 but mysqldump uses utf8, 4-byte characters get replaced with ? permanently. Always specify --default-character-set=utf8mb4 for utf8mb4 tables. The cleaner can't recover characters that were truncated at the dump step.

Server-side COPY writes \n on Linux and \r\n on Windows. Client-side \copy ... TO STDOUT always writes \n. If a Windows analyst exported via \copy directly to a file (not stdout), downstream awk -F, shells see \r as part of the last column. The cleaner's outputLineEnding: lf normalises to \n.

Confirmed in the SQLite forum: starting in 3.53.0, the order of CLI flags now matters. sqlite3 -csv -header db.db < query.sql outputs headers correctly; sqlite3 -header -csv db.db < query.sql does not. The workaround is to always put -csv before -header in scripts. For dot commands inside the REPL, the conventional order is .mode csv then .headers on. The cleaner can't recover a missing header — re-run the export with the corrected flag order.

psql's \copy ... CSV writes empty string for NULL. psql's \copy ... CSV NULL '\N' writes the literal \N. Different configs produce different output for the same data — be explicit about which one you used. The cleaner preserves the cell content verbatim; conversion between the two needs csv-find-replace.

Per PostgreSQL docs: 'COPY FROM will complain if the line endings in the input are not all alike.' If a file has some rows ending in \n and others in \r\n (common after manual edits in different editors), re-import fails. The cleaner's outputLineEnding: crlf (or lf) normalises all rows to one ending.

SQLCMD -E -Q '...' -o output.csv writes a line of - characters between the header row and the data rows. This breaks any standard CSV parser. skipRows: 1 after the header drops the separator line. Better: use the -s, flag with -W (no trailing whitespace) and -h -1 (no header repeat) — or use bcp queryout for cleaner output.

bcp queryout file.csv -w produces a UTF-16 Little-Endian file with a BOM. The .csv extension is misleading — Excel and pandas both default to assuming UTF-8 and may misread. The cleaner's inputEncoding: auto detects the UTF-16 BOM and decodes correctly; the output is UTF-8 (with BOM if outputLineEnding: crlf).

PostgreSQL's text-format COPY escapes embedded \r and \n as the two-character sequences \r and \n. CSV-format COPY quote-wraps the entire field and embeds the raw newline. If you see literal \r in your data after a text-mode COPY, the data isn't corrupted — psql preserved the value verbatim. Re-export with WITH (FORMAT csv) to get standard CSV quoting.