Importing a spreadsheet without importing the mess

A spreadsheet works because a person reads it. A blank cell means something, a note in the margin explains an exception, and the person knows which rows to ignore.

A database has none of that context. An import moves the rows and loses every one of those understandings.

Clean the columns before you import anything

Three problems account for most bad imports, and all three are visible before you start.

1. Mixed types in one column. A date column holding Q3 and TBC, or a number column holding about 400.
2. One column doing two jobs. A name column carrying the customer and their phone number.
3. Statuses that drifted. Complete, complete, Completed and done, all meaning the same thing.

All three fix faster in the spreadsheet, where a find and replace takes minutes. The same fix after the import takes a person and a morning.

Decide the field types before the data arrives

An import into text columns always succeeds, and that is the trap. Your dates sort alphabetically and your numbers refuse to total.

The types come first, so the import fails loudly where a value does not fit. Our guide to choosing a field type covers picking them for the questions you will ask later.

Import twenty rows first

A sample tells you everything a full import would, and it costs nothing to discard. Twenty rows expose the mixed columns and the stray formats.

The sample deserves a look in the list, not in the importer. The list is where the problems become obvious, because that is how people will read the data.

Work out what makes a row unique

Spreadsheets duplicate rows quietly. The same customer appears three times with three spellings, and each becomes its own record.

The thing that identifies a row comes first, and the deduplication runs on it. Merging afterwards means relinking everything that already points at the wrong copy.

Keep the original file

The first import rarely lands perfectly. Keeping the source file means the second attempt starts from the data rather than from a repair.

It belongs somewhere findable, with the date on it. An import you can run again is a decision you can undo.

Close the spreadsheet afterwards

The worst outcome is two live copies. Somebody keeps updating the spreadsheet, somebody else updates the records, and both are wrong within a fortnight.

A date, an announcement, and a read only spreadsheet ends it. An import that leaves the old file open has not finished.

Relations have to arrive in order

A jobs sheet naming customers cannot link to customers that do not exist yet. The import either fails or creates a text value that looks right and links to nothing.

The customers import first, then a check, then the jobs that point at them.

That order applies to every link in the data: sites before jobs, staff before assignments, suppliers before invoices. Getting it wrong costs a rebuild rather than a fix.

A handful of links deserve checking after each stage rather than at the end. A wrong link found after four imports costs far more to unpick.

An import you can repeat is an import you can afford to get wrong once.

What to change first

• Mixed columns, split columns and drifted statuses get fixed in the source
• The field types get set before the data arrives
• Twenty rows import first, and get read in the list
• What makes a row unique gets settled, and deduplication runs before the import
• The original file stays, dated, so the import can run again
• A date gets set, and the old spreadsheet turns read only