CSV import data errors: why they happen, and the fix
Prices doubled. "café" turned into "café". Three copies of every product. When an import goes wrong the instinct is to blame the file, but a "bad file" is almost never the cause: two systems disagreed, silently, about what a comma, a quote mark or a number meant, and nothing flagged the disagreement until the wrong data was already in front of you.
Seven ways a clean-looking file lies
Every one of these arrives with the right row count and no error message.
Encoding mismatches — a file written as Windows-1252 and read as UTF-8 (or the reverse) turns "café" into "café" and an em dash into three garbled characters. Nothing crashes; the values are simply wrong, on whichever columns you don't happen to scroll past.
The delimiter isn't actually a comma — much of continental Europe saves CSV with semicolons, because those locales use the comma for decimals. Assume the wrong one and you get a single giant unsplit column — or a price's decimal comma split into columns that don't exist.
Spreadsheet "help" before export — Excel stores a postal code typed as 01234 as the number 1234, and renders a 16-digit barcode in scientific notation, rounded past 15 digits. By the time the CSV is written, the original value no longer exists anywhere.
Numbers that mean two different things — 1.234,56 and 1,234.56 are the same amount under two regional conventions. Read one under the other's assumption and it parses fine — as 1.23, or as 1234560.
Headers that move — an export adds or reorders a column, "postcode" becomes "postal_code", and an importer matching by position quietly writes descriptions into price fields. Nothing about a CSV enforces that column 4 means the same thing twice.
Re-importing without a stable key — fix three rows, re-upload 2,000, and an importer with no stable identity per record creates 2,000 duplicates instead of updating three. Names change; an external reference, SKU or order number doesn't.
Characters you can't see — a non-breaking space pasted from a web page, or "Blue " with a trailing space, displays identically to the clean value and fails every exact match — so the same category gets created twice, and duplicate checks never fire.
Before you upload: the check that catches most of this
1
Open it as plain text once
Before trusting any tool's preview, open the file in a plain text editor, not a spreadsheet — a spreadsheet re-interprets the file the moment it opens it, which hides exactly the problems you're checking for. Look at the raw start of the first line for anything sitting before the first header name.
2
Confirm the delimiter matches what you assume
Count delimiters on a few different lines by eye. A file that's actually semicolon-delimited but assumed to be comma-delimited shows zero commas outside the values themselves — a five-second check that catches an entire class of failure before it starts.
3
Test one row with a comma, a quote and a line break in it
Find, or deliberately create, a test row with a comma inside a text field, a quote mark, and a multi-line note, and confirm the importer keeps each as one field rather than splitting it. This single row exercises the exact mechanism that silently shifts every column after it when handled wrong.
4
Run a small batch before the whole file
Ten or twenty rows first, reviewed individually, catches a systemic mapping mistake — a swapped column, a wrong currency, a bad date format — while it's still ten rows to fix, not ten thousand.
5
Decide what "done" means per row, not per file
A single pass or fail for the whole file tells you nothing about which rows to fix. Insist on a per-row result — created, updated, skipped, failed, and why — before trusting an importer with anything that matters.
An encoding mismatch, almost always. The file was written in one character encoding — commonly Windows-1252 or Latin-1 — and read as UTF-8, or the reverse. Re-save the source file explicitly as UTF-8, and check whether a byte-order mark needs to be stripped, depending on what the importer expects.
Spreadsheet software stores anything that looks like a number as a number, and numbers don't keep leading zeros. It happened before the CSV was written, at the moment the value was typed or auto-formatted, and it can't be recovered from the CSV alone — the source column needs to be formatted as text, not fixed after the fact.
The importer had no stable way to recognise a row in the new file as "the same record" it already saved, most likely because matching was on a name or description rather than a fixed identifier like an external reference, order number or SKU. Without that stable key, every import looks like a first import.
That's a reporting problem as much as a data problem — a good importer tells you the outcome of every row, not just the file as a whole. If the tool you're using only gives one pass/fail for the whole upload, treat that as a sign to test in small batches until you can see per-row results.
Almost always a decimal-versus-thousands-separator mismatch. 1.234,56 and 1,234.56 are both valid representations of the same number under different regional conventions, and reading one under the other's assumption doesn't fail — it just produces the wrong value. Confirm which convention your export uses and make sure the importer is told, not left to guess.
Be careful — opening and re-saving a CSV in spreadsheet software is exactly the step that introduces several of the problems on this page: leading zeros, date auto-conversion, scientific notation. If you need to hand-edit a handful of rows, a plain text editor is safer than a full round trip through a spreadsheet.
How Olmira handles this
Olmira's file-import connector is built against exactly this list: a strict RFC 4180 parser that handles quoted commas and embedded newlines, automatic stripping of a leading UTF-8 BOM, and oversized files rejected outright rather than silently truncated. A row whose column count doesn't match the header fails on its own without aborting the rest, and a dedupe key means running the same file twice updates or skips existing records — by the policy you chose — rather than duplicating them.
For product and service catalogues, importing by CSV adds a per-row preview — including any fields AI has filled in, clearly flagged — before a single row is saved. The full connector platform, with scheduled pulls and signed webhooks alongside file import, is on Integrations.