How to move donor data off a spreadsheet safely

The single biggest obstacle to a small charity adopting a CRM is not price. It is the belief that moving twelve years of records will be a disaster. That fear is reasonable — but the migration is almost always smaller than it looks, and it is entirely reversible if you set it up properly.

The rule that makes this safe

Never edit the original. Take a copy, work on the copy, and keep the original untouched and backed up. If the import goes wrong, you have lost nothing but an afternoon.

Everything else below is preparation. The import itself takes minutes.

Get to one row per person

Spreadsheets that grew organically usually have one row per gift, or one row per mailing, rather than one row per person. Decide which you have.

If you have one row per gift, that is fine — most systems import people and gifts separately, and you can use the same file twice. What causes trouble is a hybrid: a person row with columns like Gift 1, Gift 2, Gift 3. Those need reshaping into a gift list before anything else.

Split the fields that got combined

The usual offenders:

• Name holding “Mr and Mrs John Smith” — split into salutation, first, last, and a second person
• Address holding the whole address in one cell — split into street, city, province, postal code
• Notes holding a phone number, an email and a comment

You do not have to do this perfectly. You have to do it well enough that the system can tell the fields apart.

Normalise the things that look fine but are not

• Dates. A column with 12/03/2019, Mar 12 2019 and 2019-03-12 will import three different ways. Pick ISO format.
• Amounts. Strip currency symbols and commas. Check for text in a number column.
• Provinces. AB, Alberta, alta are three values to a computer.
• Blank versus zero. A blank gift amount and a $0 gift mean different things.

Decide how households work

This is the decision people skip and then regret. If a couple gives together, do you want one record or two? Most systems handle a household grouping, but you have to tell it which is which.

The pragmatic answer for a small charity: one record per person who could receive a receipt, grouped into a household for mailing. Receipts are issued to individuals, so the individual has to exist.

Duplicates

Do not try to resolve every duplicate before importing. Import, then use the system’s duplicate detection, which is better at it than you will be at 11pm in a spreadsheet.

What you should do first is spot the systematic duplicates — the same person appearing once as “Jennifer” and once as “Jenny”, or a person who moved and exists at both addresses.

What to keep

Keep everything, including the columns you think are useless. A Notes field full of half-sentences is often the only record of why a major donor gives, and it cannot be reconstructed. Import it into a notes field even if it is messy.

After the import

Check three things before you trust it: total number of records, total dollars raised, and a spot-check of five donors you know well. If those three match, the import worked. If they do not, you still have the original.

Why this is worth doing

The spreadsheet works right up until it does not. It has no memory of what was said, no way to tell you who has gone quiet, and exactly one person who understands it. The migration is an afternoon. The alternative is a permanent constraint on how the organization operates.