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. The rows that are wrong all seem to have something in common.

Why this 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. One address reading “Flat 2, 14 High Street” splits into two fields and shifts every column after it one place right, for that row only.
  • Quoting solves it, and half-quoting makes it worse. A file that quotes some fields and not others, or that has an unescaped quote inside a quoted field, can throw off the parser from that point to the end of the file rather than for one row.
  • A line break inside a cell, which is easy to type into a notes field, ends the record early. One row becomes two, the second of which has too few columns and no header to explain itself.
  • 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 right now

  • Find rows whose field count differs from the header's. That single check identifies almost every damaged row and is much faster than reading the file.
  • Ask for the file again as an Excel workbook or as tab-separated text. Both carry a comma inside a value without ceremony, and re-exporting costs the sender less than repairing costs you.
  • Do not repair by hand in a spreadsheet, which is the tool that will re-save the file with the same problem. If you must repair, do it in a text editor on the raw file.
  • Check the last column specifically. A shift pushes data off the end, so the final column is where truncation shows up first.

Stopping it on the next file

Parsing happens once, on the file, and a row whose shape does not match the header is reported as that row rather than absorbed. Because every field has a type, a shifted row usually fails several cells at once, which is what makes it obvious: a postcode sitting in a country column is a text value in a text column and looks fine, right up until the column beside it is an email that is not an email.

The contact list template is a sheet with those rules already set, if you want to see the shape of one before building your own.

Other things that go wrong

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. Nothing errored. The totals are simply wrong, and some individual rows look perfectly reasonable.The plus sign disappeared from my phone numbersInternational phone numbers have lost their leading plus. Some cells show an error instead, and some have collapsed into a single run of digits with the spaces gone.My CSV shows é where it should show éNames and addresses are full of sequences like é, ü, or ’ where accented letters, umlauts, and apostrophes should be. Sometimes there are question marks or black diamonds instead.My dates imported as five-digit numbersA column that should hold dates contains numbers around 45000 instead. They are all in a narrow range, and sorting them still puts the rows in the right order.

Define the sheet once

Name your fields, and every file after that is matched to them and checked cell by cell before a row is allowed to land.

Start freeAll fixes