⇥Data import in practice

With six steps that together take twenty minutes and almost always save the import.

1. A header row in which each column has a clear name.
2. One row per record. No subheadings, no blank rows, no totals row at the end.
3. One piece of information per column. Not name and company together, not street and town in one field.
4. No merged cells.
5. No formulas — replace them with values before you save.
6. Consistent date formats.

In addition: look at the last twenty rows. That is where things collect that have been appended at the bottom over the years — notes, test entries, a second block of data.

And the most important preparation: a backup copy of the original file, unchanged.

Enlarge

No. Import twenty rows first.

Why:

• You see immediately whether the column mapping is right.
• You see whether accented characters such as umlauts arrive correctly.
• You see whether empty fields are handled the way you expect.
• And if something is wrong, it affects twenty records and not four thousand.

The process that has proven itself:

1. Import twenty rows.
2. Open five of them individually and look at them.
3. If everything is right: the rest.
4. After the big run, check another five at random.

What you should check: not just whether something is there, but whether it is in the right field. A phone number in the fax field looks complete at first glance.

And use the dry run if you are unsure — it shows what would happen without anything happening.

That is a character set problem, and it is decided when the file is saved — not during the import.

What you do:

1. Open the file in the spreadsheet program.
2. When saving as CSV, set the character encoding to UTF-8.
3. Import again.

How to recognise it: “Müller” becomes “Müller” or “M?ller”. The pattern is always the same: umlauts and the German sharp s are affected; everything else is correct.

Please do not: correct the characters one by one afterwards. With four hundred records, that is a day’s work for a problem that one tick solves.

If it has already happened: undo the import, save the file correctly, import again. That is faster than any correction.

And a note for Windows: some programs offer “CSV” and “CSV UTF-8” separately. Choose the second one.

The detection suggests, you decide — always look through the mapping before you confirm.

Where it typically goes wrong:

• Two similar columns: “Phone” and “Phone 2”, “Email” and “Email private”.
• Unclear names: a column “No.” can be anything.
• Swapped order of first name and last name.
• Town and postcode in one column.

What helps: give the columns in the file clear names before uploading it. “Phone business” is recognised more reliably than “Tel2”.

What you should check: in the preview, look at a row for which you know the real record. Then every swap is obvious immediately.

And for columns you do not need: do not map them. It is better to leave a field empty than to fill it incorrectly.

Then a field is missing that is needed for a usable record — usually a name or a way to get in touch.

What helps:

• Add it, if the detail is somewhere else.
• Leave it out, if the record is no good anyway. An entry without a name and without contact details is not a contact.
• Work in two passes: first import the complete ones, then rework the incomplete ones.

What you should avoid: entering placeholders to get round the check. A record named “unknown” later clogs up every list and every report.

How to find the gaps: sort the file by the column concerned. The empty ones then sit together and you can see at a glance how many there are.

And the question behind it: if many records are incomplete, the question is not the import but whether the data is any good at all.

At two levels — in the file and against your existing records.

In the file: sort by email address and by company name. Duplicates then sit one below the other. Clean up before the import — afterwards it is more laborious.

Against your existing records: the import recognises matches and asks what should happen. You generally have three options:

• Skip — the existing record stays as it is.
• Fill in — empty fields are filled, filled ones stay.
• Overwrite — the file wins.

Which one to choose: “Fill in” is almost always the right one. “Overwrite” only if the file is demonstrably more up to date than your records, and that is less often the case than you might think.

And the case that is often overlooked: the same company under two spellings. No automatic process detects that reliably.

Yes — an import can be undone. Do it early, not after two weeks of further work.

Why timing matters: undoing removes what the import created. If you have worked on these records in the meantime, that work goes too.

The process:

1. Stop working.
2. Find the import in question in the history.
3. Undo it.
4. Check whether the state is correct again.
5. Fix the cause in the file.
6. Import again — again with twenty rows first.

Record: what went wrong. The next import from the same source will have the same problem.

And if too much time has passed: then it is no longer an undo but data maintenance. Work through the affected records one by one.

Enlarge

Four things, and all four belong in the same hour:

1. Spot check. Open five records and compare them with the file. Not just whether something is there — whether it is right.

2. Check the numbers. How many rows did the file have, how many records were created? A discrepancy has a reason you should know.

3. Set categories and assignments. Freshly imported records are usually unclassified. If you do it now, it is done; if not, it stays undone.

4. Record the source. Where does this data come from? You will need that later — for assessing quality and for the question of whether you may write to these people.

Point 4 is the one most often missing, and it causes the most trouble later. A list with an unknown source is a list you cannot use with a clear conscience.

That depends on the source — and you need to know it before you send the first email.

Ask yourself before the first send:

1. Where do the addresses come from? Your own customers, a trade fair, a purchased data set, research?
2. On what basis? Consent, contractual relationship, legitimate interest?
3. Can you prove it?

What is particularly delicate: purchased address lists. The seller often assures you that consents exist — the responsibility still remains with you, and in the event of a dispute you must prove it.

What you should do: record the source on the record, not in a separate file. Then it is there when someone asks.

And the practical rule: if you cannot explain why you may write to someone, do not write to them.

Better less and good than everything and unusable.

What you should sort out before importing:

• Records without any way to get in touch.
• Addresses that are recognisably out of date — companies that no longer exist.
• Entries that are obviously test data.
• Anything you cannot explain.

Why: poor data costs you permanently. Every list is less clear, every report less accurate, every campaign has more bounces, and many bounces harm deliverability overall.

The rule of thumb: if you would have to sort out more than a fifth, the source is the problem, not the individual records.

And what is often the better decision: not to import an old list at all, but to start afresh with the contacts you actually have.

Split it — that is simpler than any optimisation and at the same time guards against the big mistake.

How:

• Divide it into blocks of a few thousand rows.
• Import them one after the other.
• Check briefly after each block.

What that achieves:

• The run does not break off in the middle.
• If something is wrong, it affects one block, not everything.
• You see the progress.

What you should bear in mind: number the blocks and record which ones you have already imported. The most common mistake with split files is importing a block twice.

And if the run takes a very long time: that is normal with many records. Let it run and do not work on the same data in the meantime.

Then a one-off effort is worthwhile that pays off every time.

What you do once:

1. Standardise the file — always the same columns in the same order with the same names.
2. Record the mapping, so that it does not have to be decided afresh each time.
3. Write a short guide, so that someone else can do it too.
4. Decide how existing records are handled — usually “fill in”.

What you do each time:

• Take a quick look at the file to see whether the format has changed.
• Start with a small amount.
• Compare the numbers after the run.

What you should watch out for: sources change their format without notice. An additional column shifts all the following ones, and if you confirm the mapping blindly, phone numbers end up in the notes field.

So: even with routine imports, always look at the preview first.