An import that fails on row 48,213 with "unexpected number of columns" is a bad afternoon. Checking a CSV file before you load it takes a few minutes and finds most of the problems that cause failed or, worse, silently wrong imports.
What "valid" means for CSV
Since the format is loosely defined, validation means checking the file against reasonable expectations, then against what the destination requires. Structural validity means:
- Every row has the same number of fields as the header.
- Quotes are balanced, and quotes inside quoted fields are doubled.
- The delimiter is consistent.
- The file uses the encoding the destination expects.
- Line endings are consistent.
Beyond structure, data validity means the values fit the destination: numbers are numbers, dates match a format, required columns are present.
Step 1: Look at the raw file
Open the first few lines in a text editor. Identify the delimiter, whether there is a header, and whether values are quoted. Skim the end of the file too, since trailing summary lines or blank rows are common additions by export tools.
Step 2: Check the field count
The most common structural failure is a ragged row, one that has more or fewer fields than the rest. Usual causes are unquoted commas in text, line breaks inside values and stray delimiters. Docento's Text & Markdown Editor checks this for .csv, .tsv and .tab files: it reads the first row to set the expected field count and reports "Expected 3 fields, found 4" with the line number of the first row that differs. Quoted fields, doubled quotes and line breaks inside quotes are handled correctly. Click the message to jump to the line.
Step 3: Check the quotes
An unclosed quote makes everything after it part of one field, so a single missing quote near the top of a file can produce a confusing error at the very end. The editor reports "Unclosed quote" and the line where the unterminated field started. Look for values containing apostrophes or inch marks that someone wrapped in quotes badly, or that contain a double quote that was not doubled.
Step 4: Check the encoding
Look for garbled characters in names and addresses. If you see é or replacement characters, the file is not being read with the encoding it was written with. See fixing garbled characters in CSV files. Check for a BOM at the start if the first column name looks wrong when loaded.
Step 5: Check the data
Structure can be perfect and the content still wrong. Check:
- Types. Are numeric columns free of currency symbols, thousands separators and text like "N/A"?
- Dates. Is there one consistent format? Prefer ISO 8601 (
2025-04-03). - Required fields. Any blank cells where the destination needs a value?
- Duplicates in columns that must be unique, such as IDs.
- Identifiers. Make sure leading zeros survived; see Excel and CSV leading zeros.
- Whitespace. Leading or trailing spaces can make "Ada" and "Ada " different keys.
Step 6: Test with a sample
Import the first 100 rows, plus a few from the middle and end, before running the full load. Compare counts: the number of rows in the file, minus the header, should equal the number of rows imported.
Automating it
For recurring imports, build validation into the pipeline: parse with a proper CSV library, check column counts and types, and reject the file with a clear message. Keep rejected rows in a separate file for review instead of dropping them silently.
Limits of quick checks
A browser check covers structure up to 5 MB in the Docento editor. For very large files, use a script or a command-line tool. A structure check does not verify business rules, so keep data checks as a separate step.
Takeaway
Validate a CSV in layers: look at the raw text, check field counts and quotes, confirm the encoding, then check the data against the destination's rules. Fix problems at the source file, and test a sample before the full import.
