Online Tool Store
· 5 min read
How to Find Out Why a CSV Import Failed
Manesh Jayawardhana
CIO & Co-founder
The import fails with “invalid value in column 4, row unknown”. The file has 18,000 rows. Column 4 looks like numbers all the way down when you scroll through it.
Somewhere in there are four values that are not numbers, and finding them by eye is not a plan.
Why one bad value breaks a column
Import tools infer a type per column and apply it to every row. One value that does not fit either fails the import or forces the whole column to text, which then breaks everything downstream that expected a number.
The values that cause it are almost always formatting rather than data:
Thousands separators. 1,250.00 is text. In a comma-delimited file it may also be splitting into two columns, which produces a different and more confusing error.
Currency symbols. £45.00 is text.
Leading or trailing spaces. 12 frequently parses and sometimes does not, depending on the tool.
Empty strings versus nulls. Some importers treat "" as a value and reject it in a numeric column.
Placeholder text. N/A, -, unknown and TBC scattered through an otherwise numeric column.
Each of these looks like data to a human and like a type violation to a parser.
Ambiguous dates are worse
A bad number fails loudly. An ambiguous date succeeds and is wrong.
03/04/2026 is 3 April in most of the world and 4 March in the United States. Nothing in the file says which. A parser picks one convention and applies it to the whole column, and if it picks wrong, every date between the 1st and 12th of a month is silently shifted.
The dates above the 12th of the month give it away — 25/03/2026 cannot be month-first — which is why a file containing only early-month dates is the dangerous case. There is no signal at all.
Set the format explicitly whenever the data could be either. A parser’s guess is right about half the time and wrong invisibly.
| Value | Looks like | Actually |
|---|---|---|
1,250.00 | Number | Text |
12 | Number | Number with padding |
N/A | Missing | Text in a numeric column |
03/04/2026 | A date | Two possible dates |
007 | Number | Text, if leading zeros matter |
Leading zeros are the other trap
Product codes, postcodes and account numbers frequently start with zeros. Typed as numbers, 00734 becomes 734, and the change is permanent once saved.
This is the main reason not to open a CSV in a spreadsheet to inspect it. The spreadsheet converts on load, and saving writes the converted values back. Inspecting the file with something that does not reformat it avoids destroying the data you were trying to check.
Encoding problems look like data problems
A class of failure that presents as corrupted values rather than as an error.
A file saved as UTF-8 and read as Latin-1 produces the familiar mangled characters — an accented letter becoming two symbols. Read the other way and characters go missing entirely.
The symptom is usually a small number of rows with strange characters in name or address fields, which looks like bad data entry and is actually a mismatch between how the file was written and how it is being read.
A byte order mark at the start of the file causes a related problem, where the first column’s header carries invisible characters and the importer reports a missing column that is plainly there.
Both are fixed at the export or import step by stating the encoding explicitly rather than letting either end guess.
Common mistakes to avoid
- Opening the file in a spreadsheet to look for the problem, which strips leading zeros and reformats dates on the way in.
- Letting a parser guess an ambiguous date format.
- Fixing the four bad rows in the export rather than in the system that produced it, so the next export has the same problem.
- Assuming a column is clean because the first hundred rows are.
- Converting a column to text to make the import pass, which moves the problem downstream.
How to do it with CSV Type Detector
The CSV Type Detector reports the inferred type and lists every value that does not fit.
- Paste the CSV including its header row — it is parsed in the browser, so an export containing customer data stays on your machine.
- Set the date format explicitly if your dates could be read either way.
- Read the exception list, which names the rows rather than making you find them.
- Fix them at source where you can, so the next export is clean.
Other CSV tools that keep data local are in the tools directory.
Frequently asked questions
Why is my numeric column detected as text?
Because at least one value is not a number — usually a thousands separator, a currency symbol, a placeholder like N/A, or an empty string. The exception list shows exactly which rows.
How are ambiguous dates handled?
They are flagged rather than guessed. 03/04/2026 is two different dates depending on convention, and a parser that picks silently is wrong about half the time with no visible symptom.
Why not just open the file in a spreadsheet?
Because it converts on load — leading zeros disappear, dates reformat, and long numbers become scientific notation. Saving then writes those changes back into the file.
Final thought
Read the exception rows before changing anything. Four values in eighteen thousand rows is the entire problem, and they are almost never in the data — they are in how it was formatted on the way out.