Implementation worksheet · 6 min read
A Spreadsheet-to-Database Import Validation Workbook
Validate nine things before importing, in this order: row count, uniqueness of whatever will be the key, date formats and ambiguity, number formats including thousands separators and currency symbols, leading zeros that Excel has already eaten, text encoding, empty versus null versus the string 'NULL', values that will not fit the destination type, and referential integrity between sheets. Then run the import into a staging table first and reconcile counts and checksums before touching anything real. The dangerous failures are not the rows that get rejected — they are the ones silently coerced into a valid value that is wrong.
A spreadsheet has no schema, so every cell is whatever someone typed. A database has a schema and will either reject a bad value or convert it. Rejection is loud and fixable. Conversion is silent, and a date parsed with the wrong day-month order produces a perfectly valid date that is not the one in the source.
Put it into practice
1. Count rows in the source before anything else
Write the number down. Blank rows, hidden rows, filtered views and a trailing row of totals all make the count you see differ from the count you get. Every later reconciliation is against this number, so it has to be the real one — check for a totals row specifically, because importing it creates a record that looks like data.
2. Test the intended key for uniqueness and for emptiness
Duplicates and blanks in the key column decide your whole import strategy, and finding them after a partial import means reconciling two systems. If duplicates are legitimate, the key is wrong; if they are errors, they need resolving in the source where someone knows which is correct.
3. Check dates for ambiguity, not just for format
03/04/2026 is two different dates depending on locale, and both are valid. If any date could be read either way, the import must be told explicitly which convention the source uses. This is the single most common silent corruption in spreadsheet imports and it is invisible afterwards, because every value still looks like a date.
4. Look for the leading zeros already lost
Postcodes, account numbers, product codes and phone numbers stored as numbers have lost their leading zeros before you ever opened the file. That damage happened in the spreadsheet, not at import, so check the original export and re-export as text if needed — no import setting recovers it.
5. Distinguish empty, null and the word NULL
A blank cell, a cell containing a space, and a cell containing the literal text 'NULL' are three different things that all look empty. Decide what each maps to before import, because the default is usually to store the string, which then fails every is-null check forever.
6. Check every value fits the destination type and length
Text longer than the column allows is either rejected or truncated, and truncation is the bad outcome. Numbers beyond precision get rounded. Check the maximum length and range per column against the destination definition rather than assuming.
7. Verify references between sheets before, not after
If one sheet references another, check that every reference resolves. Orphaned references become either rejected rows or broken foreign keys, and both are much cheaper to fix in the source.
8. Import to staging, reconcile, then promote
Never import straight into live tables. Load to a staging table, compare counts against your written-down number, checksum a numeric column against the spreadsheet total, and spot-check twenty rows by eye including the first, the last and anything unusual. Only then promote.
The validation workbook
Copy this structure into your review document and record your observed result for each row.
| Check | Source result | Action | Cleared |
|---|---|---|---|
| Row count (excluding totals/blanks) | record the number | ||
| Key uniqueness | resolve in source | ||
| Key blanks | resolve in source | ||
| Ambiguous dates | declare the convention | ||
| Number formats: separators, currency | strip and type | ||
| Leading zeros | re-export as text | ||
| Text encoding | confirm UTF-8 | ||
| Empty vs null vs 'NULL' | map explicitly | ||
| Values exceeding type or length | widen or truncate deliberately | ||
| Cross-sheet references resolve | fix orphans in source | ||
| Staging count matches source | reconcile before promoting | ||
| Numeric checksum matches | reconcile before promoting |
A failure worth checking
The silently coerced date. A spreadsheet exported from a system using day-month order is imported by a tool assuming month-day. Every date where the day is 12 or lower converts successfully to the wrong date; dates above 12 either fail or flip. The import reports success, the row count matches, and a portion of the data is quietly wrong in a way no count reconciles. It surfaces months later as records appearing in the wrong period, and by then the source spreadsheet may be gone.
Common questions
Should I clean the data in the spreadsheet or after import?
In the spreadsheet, where the person who knows what the values mean can see them. Cleaning after import means guessing at intent, and the guesses get baked in. The exception is mechanical transformation — stripping currency symbols, trimming whitespace — which is safer done consistently by the importer.
How many rows should I spot-check?
Twenty, chosen deliberately rather than randomly: the first row, the last row, any row with an unusual value, and a few from the middle. Random sampling misses the edges, and the edges are where the import broke.
Basis and scope
This is a proposed implementation method using illustrative examples, not a measured benchmark or a customer case study. Prepared with AI assistance. Validate product-specific behavior against current documentation and your own test environment.