Spreadsheet to database
The signal that a spreadsheet has outgrown itself is not the number of rows. It is two people editing it, or one person editing it on a phone.
Before you move anything
Print the sheet. Circle every column that is a fact about the thing, and every column that is a note to a human. The notes almost always need to become one field, not fifteen.
Fix the data while it is still in the sheet
Every messy sheet has the same four problems:
- Dates in three formats, usually because of one paste from a website
- Names with trailing spaces, which look identical and are not
- A column that is a number for most rows and "n/a" for a few
- Merged cells holding a heading that should be its own column
Sort by each column in turn and the odd ones float to the ends. This takes twenty minutes and saves a whole afternoon later.
Pick the boring option
| If | Use |
|---|---|
| Under ~5,000 rows, two or three editors | Airtable or a shared Google Sheet with protected ranges |
| You need it on a phone in the field | Airtable |
| It feeds invoicing or payroll | Something with real constraints - Postgres behind a small form |
Keep the old sheet, read-only, for six months. Somebody
will ask what a value was in March.