Import a CSV without breaking it

Keep leading-zero identifiers as text through an import, resolve an ambiguous date order from evidence inside the file, and recognise when a delimiter, a quotation mark or a line break has changed the number of records.

An import is a translation, and like any translation it can be confidently wrong.

The three failures in this lesson share one property: the result still looks like a table. Nothing errors. The columns line up. A total can be computed from it. The only signs that anything happened are an identifier that no longer matches the system it came from, a date that means a different day than it did an hour ago, and a row count that is off by the number of notes somebody pressed Enter inside.

Each of these has cost real organisations real money, and each is prevented by the same two minutes of care: look at the file before importing it, and keep a copy of what arrived.

A delimited file is text. Every structure a spreadsheet shows you afterwards is an interpretation, and there are four decisions in that interpretation worth taking away from the importer.

The delimiter. Comma is the default and is not universal: exports produced where the comma is a decimal separator commonly use a semicolon, and tab-separated files are still everywhere. Getting this wrong is the one failure that is obvious, because everything lands in one column.

The text qualifier. A field containing the delimiter is wrapped in quotation marks, and the delimiter inside those marks does not end the field. A name recorded as "Ruiz, Ana" is one field. An importer that ignores quoting turns it into two, and every column to the right of it shifts one place. That shift is what makes this failure dangerous: the sessions count ends up in the notes column, the notes end up beyond the table, and the sheet still looks orderly.

The column type. This is the leading-zero problem. An identifier like 00417 is text that happens to be made of digits. Read as a number it becomes 417, and 417 will never again match 00417 in the system it came from. The rule that avoids it: if you would never do arithmetic on a column, it is not a number column. Identifiers, postcodes, phone numbers and account codes are all text.

The date order. 03/04/2026 is either the third of April or the fourth of March, and the file does not say which. The importer will pick one, based on a setting you probably did not choose, and the result will look correct either way. Do not guess and do not accept the default. Look through the file for a date whose day exceeds twelve — a 22 or a 31 in the first position settles the order for the whole file at a glance. If no such date exists, the file cannot tell you, and you have to ask whoever produced it.

Two more things belong to the same two minutes.

A line break inside a quoted field is legal and common; anything that types a note into a form can produce one. A tool that splits on every line break turns one record into two, and the second one has a value in the first column and nothing else. This is why a row count from before the import and a row count from after it is worth thirty seconds.

Keep the original. Import into its own sheet and never edit it. Work on a copy. When the total is questioned in six weeks, the untouched sheet is the only thing that can distinguish "the data was always like this" from "we changed something".

Cedar Ridge's member export, as text:

member_id,name,joined,sessions,note
00417,"Ruiz, Ana",03/04/2026,12,"Prefers Tuesday
evenings"
00892,"Okafor, Chidi",11/04/2026,9,Transferred from Ridge
01130,"Bell, Sam",03/05/2026,15,
12045,"Nakamura, Yuki",22/04/2026,7,Volunteer
00308,"Dupont, Marie",05/05/2026,11,=1+1

Seven lines. Five records. Read the file for the four decisions before touching an importer.

The delimiter is a comma. Every name contains one and is therefore quoted, so an importer that ignores quotation marks will produce a shifted table for every single row.

Four of the five identifiers begin with a zero. All four will be damaged by a numeric column.

Ana's note contains a line break inside its quotes, which is why lines 2 and 3 are one record.

And 22/04/2026 settles the date order on its own: there is no twenty-second month, so this file writes the day first. Every other date in it must be read that way — which means 03/05/2026 is the third of May, not the fifth of March.

The last note begins with an equals sign. In a file that is four characters of text. In a spreadsheet that evaluates it, it is an instruction supplied by whoever produced the file. This practice grid stores such a field as text, always, with no setting to do otherwise.

Suppose the file above is imported by a tool that splits on every comma and every line break, and reads any all-digit field as a number. Before revealing, write down your answers to three questions.

1. How many data rows appear?
2. What does the cell that should hold Ana's session count actually hold?
3. What is in the first column of the row that should not exist?

The instinctive repair for the identifier column is to add the zeros back:

="00" & A2

It produces 00417 on screen, and it is the wrong fix for two reasons. It assumes every damaged identifier lost exactly two zeros, which is true here for 00417 but not for 01130. And it derives the identifier from the damaged value rather than from the file, so it can never recover information the import discarded.

The repair that works is to bring the column in as text in the first place, which in this grid is a leading apostrophe:

'00417

The apostrophe is not stored and is not displayed. It tells the sheet that what follows is text, so the zeros survive. In Excel and Google Sheets the same convention works, and both also offer a column type of Text in the import dialog, which is the better route when a column has more than a handful of rows.

Then check it. =COUNT(A2:A6) over the five correctly entered text identifiers returns 0. A nonzero result flags a numeric cell, even for an identifier such as 12045 that has no leading zero to lose. Zero alone does not prove the values are correct: blank cells and other nonnumeric values are also excluded, so compare all five identifiers with the source.

A colleague reports that a member list "gained three people" after import and that its sessions total fell. Those symptoms call for reconciliation, not an assumption about their cause. The worked file above produces one extra row under the stated careless import, not three.

1. Compare the counts first. Ask how many records the file contains and how many rows the sheet contains. If the sheet has more, something split records. If it has fewer, something merged or skipped them.
2. Find the extra rows. They will be the ones with a value in the first column and nothing else, or with a value in the middle and nothing on either side. Sorting by the identifier column brings them together.
3. Read the source line, not the imported row. The extra rows are halves of real records. The other half is a row that looks complete and is not.
4. Check the total against the source and the total formula. In the worked import, ignoring each quoted name comma shifts the session values into the notes column; splitting Ana's quoted newline separately creates the extra row. The session numbers still exist but are under the wrong heading. Recompute the total from correctly parsed records rather than inferring the cause from the two totals alone.
5. Re-import; do not repair. Deleting the orphan rows makes the count look right and leaves the damaged records damaged. Import again with quoting honoured, and compare the counts again.

The general shape: a row count that changed during an import is the finding, not a nuisance. Reconcile it before you look at any figure computed from the result.

Three sheets. SourceCsv is the file as it arrived, one line per row. Import is what a careless import made of it. Repaired is yours.

Restore the identifiers and the dates, then complete the findings block so the damage is counted rather than described.

This one is about a file you will actually meet.

Find any CSV export you have access to — a bank statement, a calendar export, a report from a system at work — and answer four questions before opening it in a spreadsheet. Open it in a plain text editor instead.

1. What is the delimiter, and how did you establish that rather than assume it?
2. Which columns are identifiers that must not become numbers?
3. Is there a date whose day exceeds twelve? If not, how would you find out the order?
4. Does any field contain the delimiter, a quotation mark, or a line break?

Then open it in a spreadsheet and see how many of your four answers the default import got right. If it got all four right, you have learned something about that particular file rather than about importers in general.

Use a copy. Never do this to the only version of anything.

Sign in to track your progress on this exercise.

Move on when you can do these without looking back.

• Explain why an identifier such as 00417 must be text, and name the one-cell test that proves a column of them still is.
• Say what a quotation mark does to a delimiter inside a field, and describe what an import that ignores quoting does to the columns to the right.
• Given a file of dates that are all ambiguous, say what you would do rather than accepting the importer's default.
• Describe the count you would take before an import and compare after it, and say what a higher count and a lower count each suggest.
• Say why the imported sheet should be kept untouched, and what question that answers six weeks later.

Sign in to track your progress on this exercise.

An import is finished when the identifiers still match their source, the dates mean the days they meant in the file, the record count reconciles, and the sheet the file landed on has not been edited.

Next: Find duplicates and inconsistent records. The data is now faithfully in the sheet, which is where a different problem starts: three spellings of the same person, and two rows that look like a duplicate and are not.