My CSV columns are shifted or split in the wrong places
Most rows look right, but some have values in the wrong columns: a postcode in the country column, a phone number where the email should be.
Why it happens
- A CSV separates fields with commas and has no other idea of structure, so a comma inside a value is indistinguishable from a separator unless the value is quoted.
- Quoting solves it, and half-quoting makes it worse.
- A line break inside a cell, which is easy to type into a notes field, ends the record early.
- This is why the count of broken rows is a clue: a handful means stray commas in particular values, and everything after a certain point means a quoting error at that point.
What to do now
- Find rows whose field count differs from the header's.
- Ask for the file again as an Excel workbook or as tab-separated text.
- Do not repair by hand in a spreadsheet, which is the tool that will re-save the file with the same problem.
- Check the last column specifically.
Next time
Parsing happens once, on the file, and a row whose shape does not match the header is reported as that row rather than absorbed.
Start from the contact list template.
Related problems
My supplier's prices are a thousand times wrongPrices from a European supplier are out by a factor of about a thousand, or occasionally by a factor of a hundred.The plus sign disappeared from my phone numbersInternational phone numbers have lost their leading plus.My CSV shows é where it should show éNames and addresses are full of sequences like é, ü, or ’ where accented letters, umlauts, and apostrophes should be.My dates imported as five-digit numbersA column that should hold dates contains numbers around 45000 instead.