CSV Validator

Data analysts receive exports from partners that use semicolons in one region and commas in another, and the first sign of trouble is a loaded table where every column sits one position off. ETL engineers hit ragged rows mid-batch — a free-text field containing an unquoted comma shifts every column after it, and the job either fails at row 47,000 or, worse, loads silently wrong.

Paste a CSV or pick a file and this validator auto-detects the delimiter (comma, semicolon, tab, pipe), parses with a quote-aware state machine, and reports every problem with its line number: ragged rows, unclosed quotes, mid-field quote warnings, BOM markers, and empty lines. A stats panel shows header columns, row counts, and consistency, and a preview table renders the first ten data rows so you can confirm the split before importing anywhere.

How the validator parses CSV

Delimiter detection samples the first five non-empty lines and scores each candidate — comma, semicolon, tab, pipe — by counting occurrences outside quoted regions. Consistency across lines carries the most weight: a delimiter that appears the same number of times on every sampled line scores far higher than one whose count varies, because mixed counts usually mean the character also appears inside free-text values rather than as a field separator.

Once a delimiter is chosen, a single-pass state machine walks the text character by character. Inside a quoted field, delimiters and newlines are treated as literal content and do not split fields; a doubled quote ("") inside a quoted field is unescaped to a single quote character. Outside quotes, the chosen delimiter pushes the current field onto the row, and a newline terminates the row. This is the RFC 4180 model that PostgreSQL COPY, pandas read_csv, and BigQuery's CSV loader all implement.

After parsing, the header row defines the expected column count and every data row is compared against it — mismatches become line-numbered errors. A UTF-8 BOM (bytes EF BB BF, written automatically by Excel) is detected via the U+FEFF code point, stripped before parsing, and reported as a warning. Unclosed quotes at EOF and mid-field quote characters round out the issue list, each tagged error or warning with the line number to open in your editor.

Before and after: a broken export

Input — a partner export with an unquoted comma and a ragged row:

id,name,city,notes
1,"Doe, Jane",Paris,vip
2,Bob,Lyon
3,Ana,Madrid,"late, delayed",rush

Output — the validation report:

Delimiter: Comma (,)   Columns: 4   Data rows: 3   Consistent: no

Line 3: Row has 3 fields but the header defines 4.
Line 4: Row has 5 fields but the header defines 4.

Who validates CSVs before import

Common CSV pitfalls

• Unquoted commas in free-text fields. A notes column containing Paris, France without surrounding double quotes adds an extra field on that row, shifting every column after it. The validator reports the exact line and both field counts; wrap the value in quotes at the source or fix it before import.
• Excel's invisible BOM. Every CSV Excel exports starts with bytes that mark the file as UTF-8. Excel strips the mark on load, but pandas, csvkit, and PostgreSQL COPY often do not, so the first header becomes a string with an invisible prefix and exact-match column lookups fail. Re-export without the BOM or strip it in the ingest script.
• Semicolon exports from European locales. Many regions configure spreadsheets to use semicolons because the comma is the decimal separator. Auto-detection handles single-delimiter files, but mixed files (comma in one section, semicolon in another) need the delimiter override and a second validation pass before import.
• Unclosed quotes at end of file. A missing closing double quote leaves the parser inside a quoted field through the last line, merging everything that follows into one giant value. The validator flags this as an error at the line where the quote opened, so you can add the closing character rather than hunting through a multi-megabyte export by eye.

Best practices for CSV hygiene

• 1.Always quote fields that may contain the delimiter, a double quote, or a newline — it costs nothing and prevents the most common column-shift failures.
• 2.Validate at the boundary: run every inbound export through a check like this before it reaches a warehouse or ETL job, so malformed files fail at the edge rather than mid-batch.
• 3.Standardize on comma delimiters and UTF-8 without BOM across your organization, and document the convention so partner exports match by default.
• 4.For multi-gigabyte exports that exceed browser limits, run an equivalent check in a streaming tool (csvkit's csvclean, or a custom pandas script) in the same pipeline stage.

Frequently asked questions