Spreadsheets Optimise for the Wrong Reader
A spreadsheet that a human finds clear is frequently one a machine finds unparseable. Merged cells communicate grouping visually. Blank rows create breathing room. A bold total at the bottom summarises. Colour encodes status. Every one of these is a helpful signal to a person and a defect to an importer.
This mismatch is the root of most spreadsheet-to-database pain. The file is not broken; it was simply built for a different consumer. The work of cleaning it is the work of translating from a visual medium into a relational one, and it goes much faster when you know in advance what you are looking for.
What follows is the set of defects that appear in nearly every real export, in the order that makes sense to fix them. Fixing them out of order creates rework, because some corrections depend on others having already happened.
Defect 1: Structural Noise Around the Data
Real exports rarely start with a header on row one. They start with a report title, a generation timestamp, a filter description, a blank row, and then the header. At the bottom there is often a blank row followed by a totals row.
Load this naively and your header becomes "Quarterly Revenue Report", your first data row becomes the real header, and your totals row becomes a customer named "TOTAL" with a revenue figure equal to the sum of everything else. That last one is genuinely dangerous, because it inflates every aggregate downstream while looking like legitimate data.
The Fix
Delete everything above the header row and everything below the last real data row before doing anything else. Then verify by checking the top and bottom rows explicitly. This is first because every other operation assumes the region is correct.
Defect 2: Merged Cells
A merged cell spanning four rows means "these four rows share this value" to a human. To a parser it means one populated cell and three empty ones. Region names, category labels, and date groupings are the usual victims.
The Fix
Unmerge, then fill down. Every row must carry its own complete set of values, because relational rows are independent by definition. There is no concept of a value being inherited from the row above.
This is worth checking even when you do not see merged cells: some exports produce the same effect by simply leaving repeated values blank after the first occurrence, which is visually similar and equally broken.
Defect 3: Empty Rows and Columns
Blank rows used as visual separators become rows of nulls. Blank spacer columns become columns with no data and often no header, which either fails to import or produces a column named something like "Column7".
The Fix
Delete fully empty rows and columns. Be deliberate about partially empty rows: a row with one populated cell might be a real record with missing data, or it might be a stray note someone typed. Inspect before deleting, because these two cases look identical and have opposite correct handling.
Defect 4: Inconsistent Values Meaning the Same Thing
Free-text entry produces variation. A status column contains "Active", "active", "ACTIVE", "Actv", and " Active" with a leading space. A country column contains "USA", "U.S.A.", "United States", and "US". Each variant becomes a distinct value in a GROUP BY, which quietly fragments every report built on that column.
The Fix
Normalise in stages, because the order affects the result:
- Trim whitespace. Leading and trailing spaces are invisible and are the single most common cause of apparent duplicates. Non-breaking spaces, which frequently arrive from web-sourced data, need explicit handling since they are not matched by ordinary trim operations.
- Normalise case. Apply a consistent case transformation to any column used for grouping or joining. Do this after trimming so that case rules apply to clean values.
- Collapse internal whitespace. Double spaces between words are as invisible as leading ones and just as damaging to equality comparisons.
- Map synonyms explicitly. This part cannot be automated safely. Build a lookup and apply it, and keep the lookup because the next export will contain the same variants.
A browser-based table editor with bulk transform operations handles the first three mechanically across an entire column, which matters when the column has 40,000 rows.
Defect 5: Duplicate Rows
Duplicates arrive from overlapping exports, from a report run twice with different filters, or from genuine double entry. They are not always exact: two rows describing the same entity may differ in a timestamp or a trailing space, which defeats exact-match deduplication.
The Fix
Deduplicate after normalising, never before. Two rows that differ only by case or trailing whitespace are not exact duplicates until normalisation makes them so. Running deduplication first leaves them both in place, and the unique constraint on your target table finds them for you at the worst possible moment.
Decide what constitutes identity before you start. Full-row duplication is the easy case. Duplication on a business key with differing other columns is a judgement call about which row wins, and that decision should be documented rather than implicit in whichever row happened to come first.
Defect 6: Type Ambiguity
This is the defect that produces silent corruption rather than visible errors, and it deserves the most care.
| What You See | What Goes Wrong | Correct Handling |
|---|---|---|
00742 |
Becomes 742. The identifier no longer matches anything. | Force text. Any code with a fixed width is text, not a number. |
44562 |
An Excel date serial stored as an integer, later cast to 1899. | Convert to ISO 8601 in the source before export. |
1.23457E+15 |
A long ID lost precision. The trailing digits are gone forever. | Text from the start. Re-export if this already happened. |
(1,250.00) |
Accounting negative parsed as text, or as positive 1250. | Convert to a leading minus sign before import. |
1.234,56 |
European decimal read as English: value inflated 100x. | Normalise separators explicitly. Never let the parser guess. |
03/04/2026 |
March 4th or April 3rd depending on locale. No error either way. | Demand ISO 8601. Ambiguous dates cannot be fixed downstream. |
TRUE / Y / 1 |
Three representations in one column become three distinct values. | Map to a single boolean representation before load. |
The recurring theme is that ambiguity resolves silently and in the parser's favour, not yours. Every one of these is cheap to fix in the source and expensive to detect afterward, which is why type handling belongs in the cleaning phase rather than the loading phase.
Defect 7: Wide Layouts That Should Be Tall
Reports frequently use one column per month: Jan, Feb, Mar, and so on. This is readable and completely wrong for a database, because next January requires a schema change, and every query that aggregates across months has to name every column explicitly.
The Fix
Reshape into long form: one row per entity per period, with the period as a value rather than a column name. This is what transposition and unpivoting are for. The result looks worse to a human and is dramatically better for querying, indexing, and extension.
The signal that you need this is a column header containing data. If your header row includes dates, years, product names, or region names, the layout is wide and should be tall.
The Order of Operations
Sequence matters more than people expect. This ordering avoids rework:
- Delete structural noise so the data region is exactly the data.
- Unmerge and fill down so every row is self-contained.
- Delete empty rows and columns.
- Reshape wide to long if needed, while the dataset is still small.
- Trim, case-normalise, and collapse whitespace.
- Deduplicate, now that equal values compare equal.
- Resolve types explicitly, column by column.
- Generate SQL, then read the first and last batch by eye.
Doing step 6 before step 5 leaves duplicates behind. Doing step 4 after step 5 means normalising values you are about to restructure. The dependencies are real.
The Excel-to-SQL workflow in Develop Box Utilities is arranged around this sequence: the table editor handles resizing, clearing empty rows, deduplication, case transformation, and transposition with undo at every step, and the generator produces the batched, dialect-correct INSERT script once the data is actually clean. Because it all runs in the browser, none of the intermediate messy states are ever transmitted anywhere.
Frequently Asked Questions
Should I clean in the spreadsheet or in SQL after loading?
Structural problems belong before the load, because a wrongly-shaped import is hard to reason about in SQL. Value normalisation can go either way, but doing it before means your staging table is already trustworthy. The one thing to never postpone is type handling, since coercion damage is not recoverable after the fact.
How do I catch invisible whitespace differences?
Compare the count of distinct raw values against the count of distinct trimmed and case-folded values. A gap between the two is exactly the number of variants hiding in the column. Then look for non-breaking spaces separately, since ordinary trimming does not remove them.
Can precision lost to scientific notation be recovered?
No. Once a 19-digit identifier has been stored as a float, the low-order digits are gone and no amount of reformatting brings them back. You must re-export from the source with the column typed as text. This is the strongest argument for treating identifiers as text from the very beginning.
Conclusion: Cleaning Is the Cheapest Stage
Every defect described here costs minutes to fix in a table editor and hours to unwind once it is in a database with foreign keys pointing at it. The asymmetry is enormous, and it only runs in one direction.
Treat cleaning as a deliberate, ordered pass rather than something you do reactively when an import fails. Work through the seven defects in sequence, read the generated SQL before running it, and the load itself becomes the boring part it should be.