Help for owners
My old data export is a mess and the import keeps failing
Short answer
Imports fail on bad encoding, dates in mixed formats, stray quotes and line breaks, blank required fields, numbers stored as text, and duplicate rows. Work on a copy, never the original, keep a log of each change, and fix the worst problems in the file before trying again.
Does this sound like your situation?
- The importer says rows were rejected, and I cannot see why.
- Names with accents show as strange symbols.
- Dates come out in the wrong order or as numbers.
- Phone numbers lose their leading zero.
- The same customer appears several times with small differences.
Why does this happen?
Wrong character encoding
A file saved in one encoding and read as another turns accents and non-Latin names into junk. Databases that expect UTF-8 reject invalid byte sequences outright.
Technical detail: invalid byte sequence for encoding UTF8 while importing data
Spreadsheet programs change values quietly
Opening and saving a CSV in a spreadsheet can strip leading zeros, turn long numbers into scientific notation and rewrite dates. The damage is saved into the file.
Commas, quotes and line breaks inside fields
A note containing a comma or a line break splits one record into two if the file is not quoted properly, which shifts every column after it.
Empty values mean different things
An empty cell, a space, the word NULL and a zero are not the same. A required column that gets an empty string can stop the whole load.
Technical detail: LOAD DATA turns empty CSV fields into 0 or fails with Data truncated
Duplicates and free-text variants
John Smith, J. Smith and john smith ltd may be one customer. Without a rule for which record wins, the import either keeps all of them or fails on a unique key.
What can I do about it today?
- Make a read-only copy of the original exportStore it somewhere safe and never edit it. Work only on duplicates of it.
- Do not open and re-save a CSV in a spreadsheetIf you must look at it, view only, or import it as text, because saving can alter dates and numbers.
- Note the exact error messageCopy the first rejected row and the message. That tells which problem to fix first.
- Count rows before and afterWrite down how many records the export should contain, then compare with the new system.
- Decide the rule for duplicatesWhich record wins, the newest, the most complete, or the one with orders. Write it down.
When should I bring someone in?
- The export is big, from a database, or has more than one table.
- Cleaning would mean deleting data you cannot easily recreate.
- The same customers carry orders or history that must be merged.
How do I handle it?
- Profile the data firstI check encodings, empty values, formats, lengths and duplicate keys across every column before changing anything.
- Clean with a script, not by handEach fix is a written, repeatable step working on a copy, so I can rerun it and show what changed.
- Keep a rejects fileRows I cannot safely fix are set aside with the reason, not dropped, and you decide about them.
- Merge duplicates by agreed rulesMatching rules are agreed with you, and uncertain matches are listed for review rather than merged.
- Prove it with countsStart count, clean count, rejected count and final count must add up.
The price depends on what I find, so I quote in writing after a short discovery call. Tell me what is happening and I reply within 24 hours.
What do owners ask about this?
Can I clean it myself in a spreadsheet?
For small, simple lists, with a copy and care. For large files or linked data, a spreadsheet's silent changes cause more harm than good.
Why do accents show as symbols?
The file was saved in one encoding and read in another. The fix is to read it with the correct one, not to retype the names.
Should I delete duplicates?
Merge rather than delete, so orders and notes from both records are kept.
What if some rows cannot be fixed?
They go to a separate file with the reason so you can decide, instead of vanishing.
How was this page made?
I drafted this page with AI assistance. It is general guidance for business owners, not advice for your own system. Found a mistake? Tell me.
For your developer: the technical fixes
ERROR: invalid byte sequence for encoding "UTF8": 0xe9invalid byte sequence for encoding UTF8 while importing dataERROR 1265 (01000): Data truncated for column 'price' at row 1LOAD DATA turns empty CSV fields into 0 or fails with Data truncatedMysql2::Error: Incorrect string value: '\xF0\x9F\x98\x80' for column 'body' at row 1MySQL rejects text with 'Incorrect string value' (emoji and other characters)