Clean a Survey-Response CSV

Google Forms exports UTF-8 without a BOM. Excel on Windows defaults to Windows-1252 and reads é as the two-byte sequence é. The data is fine — the encoding declaration is missing. Cleaner output adds a BOM that Excel auto-detects.

Input (Google Forms UTF-8, no BOM, opened in Excel-on-Windows):
Name,Comment
José Smith,Bonjour à tous

  ↓ Excel-on-Windows displays as:
José Smith,Bonjour à tous

Cleaner output (UTF-8 BOM added):
Name,Comment
José Smith,Bonjour à tous   ← Excel reads correctly

Some pre-2025 Typeform exports prepend a single metadata row before the actual question headers. The cleaner's skipRows: 1 drops it so row 1 of the output is the real header.

Input (Typeform legacy):
# Exported from typeform.com on 2026-05-15
Submit Date,Respondent,Q1: Name,Q2: Email
2026-05-15,abc123,John Smith,john@x.com

Cleaner config: skipRows: 1

Output:
Submit Date,Respondent,Q1: Name,Q2: Email
2026-05-15,abc123,John Smith,john@x.com

Mobile keyboards autocorrect straight " to curly “ ”. The data is the same; the encoding isn't. Downstream tools that do exact text matching (BI dashboards, NLP pipelines) will treat "frustrated" and “frustrated” as different strings. normalizeSmartQuotes folds them to ASCII.

Input:
Email,Feedback
user@x.com,"The “frustrated” user said “I don't think this works”"

Cleaner config: normalizeSmartQuotes: true

Output:
Email,Feedback
user@x.com,"The ""frustrated"" user said ""I don't think this works"""

Respondent filled the form twice — once from desktop (John.Smith@Gmail.com) and once from mobile (john.smith@gmail.com with trailing space from autocomplete). Exact dedup misses both. case-insensitive mode catches the case difference; normalized mode catches case + whitespace + NBSP.

Input:
Email,Response
John.Smith@Gmail.com,Yes
john.smith@gmail.com ,Yes

Cleaner config: dedupMode: normalized

Output (first occurrence kept):
Email,Response
John.Smith@Gmail.com,Yes

Respondent copied their support ticket from email and pasted it into a textarea. Outlook/Gmail/Apple Mail wrap text with CHAR(160) NBSPs between words — invisible to the eye, visible to any string-matching pipeline. normalizeHiddenWhitespace folds them to regular space.

Input (with NBSPs shown as · for clarity — they're U+00A0 in the actual file):
Email,Description
user@x.com,The·error·says·"Cannot·connect·to·server"

Cleaner config: normalizeHiddenWhitespace: true

Output:
Email,Description
user@x.com,The error says "Cannot connect to server"

Most survey platforms record an empty row when a respondent loads the form but doesn't submit. Skip-the-empties cleanup removes these silently.

Input:
Email,Response
user1@x.com,Yes
,
,
user2@x.com,No

Cleaner config: removeEmptyRows: true (default)

Output:
Email,Response
user1@x.com,Yes
user2@x.com,No

Google Forms exports UTF-8 without a BOM. Excel on Windows defaults to Windows-1252 when no encoding is declared. Cause is not the data — the file is correctly UTF-8. Two fixes: (1) re-save through Google Sheets (which writes UTF-8 with smarter Excel handling); (2) open via Excel's Data → Get Data → From File → From Text/CSV and manually pick UTF-8. The cleaner adds a BOM on output so future opens auto-detect.

SurveyMonkey writes Respondent IDs as 16-digit integers. Excel coerces 12+ digit numbers to scientific notation (1.23457E+15) and 16+ digit values get digit 16 replaced with 0 — permanently after save. SurveyMonkey's docs recommend using the Text Import Wizard with the Respondent ID column set to Text. The cleaner treats every cell as a string so the ID round-trips correctly — but if Excel was used between download and cleaner, the damage is done.

Typeform's CSV export writes multi-select (multiple-choice with multi-select enabled) as separate columns, one per answer choice, with Yes/No or 1/0 values. The question text itself doesn't appear as a column header. If your downstream tool expects a single multi-value cell, you'll need csv-column-merger after the cleaner. For most survey-analysis tools, the separate-column shape is preferred.

Most survey platforms quote-wrap free-text fields correctly. The exception: when a respondent's answer was copy-pasted into the form from a source that already had RFC 4180 quoting, and the form processed the literal " characters. Result: every comma in the answer shifts subsequent cells one column right. The cleaner's repairRows: true (default on) pads short rows or trims long rows to the header width. The underlying answer text may need manual review.

If your form allowed respondents to submit multiple times by design (longitudinal surveys, weekly check-ins, multi-stage research), any email-keyed dedup will collapse legitimate distinct responses. Use dedupMode: off for these scenarios. The cleaner's other passes (trim, smart-quote, NBSP, empty-row) are still safe to apply.

Jotform and Google Forms wrap multi-line address fields with \n inside the cell. RFC 4180 requires those cells to be quoted ("..."). The cleaner's parser is quote-aware so the embedded newlines stay inside the cell. If a downstream tool fails on these cells, the cleaner's outputLineEnding setting doesn't normalise newlines *inside* quoted cells — only between rows. For full sanitisation, run csv-find-replace with regex \r?\n → on the address column.

SurveyMonkey's Standard layout writes question text on row 1 and the answer-choice subhead on row 2. The cleaner's skipRows: 1 drops row 1, leaving the subhead as the canonical header. Alternative: switch to Condensed Columns in SurveyMonkey's export options for a single-header layout — generally easier downstream.

Some EU-locale Typeform exports use ; as the field delimiter (because , is the decimal separator). The cleaner's delimiter: auto sniffs from the first row. Force delimiter: ";" if your file is small enough that the sniffer guessed wrong (1–2 columns of data can confuse heuristics).

Microsoft Forms writes the submission timestamp in the form owner's timezone. Excel displays dates in the local timezone of whoever opens the file. The cleaner treats every cell as text, so the timestamp string is preserved exactly — the perceived difference is downstream display behaviour. If you need a fixed timezone, format the timestamp as text inside Forms before export.