From CSV Export to Production Table: A Field Guide to Safe Bulk Loading

The Load That Succeeded and Still Was Wrong

A colleague sends you a CSV. It is the reconciled quarterly figures, exported from a system that does not have an API, and it needs to go into a reporting table by Friday. You import it. No errors. The row count matches the spreadsheet exactly. You mark the ticket done.

Three weeks later someone in finance notices that a subset of amounts are off by a factor of one hundred, and that customer identifiers beginning with zero have lost their leading digits. The row count was never the thing worth checking. It only tells you that the right number of rows arrived, not that they contain the right values.

This failure mode is common because it is quiet. A load that crashes gets attention immediately. A load that silently coerces types produces a database that looks fine and is wrong, and the discovery happens far from the cause. The procedure below exists to make that class of error impossible to miss.

Why CSV Is a Worse Format Than It Appears

CSV looks trivial: values, commas, newlines. In practice it is an under-specified family of formats that disagree with each other, and most of the pain in spreadsheet-to-database work traces back to that.

There Is No Type Information

Every field is text. Whether 0042 is the integer 42 or the string "0042" is not stated anywhere; it depends entirely on what the consumer decides. Whether 1,234 is one value with a thousands separator or two values depends on the quoting, which depends on the producer. Whether 03/04/2026 is March 4th or April 3rd depends on the locale of whoever exported it.

The Delimiter Is Not Always a Comma

Locales that use a comma as the decimal separator export with semicolons. Some systems use tabs or pipes. A file with a .csv extension may contain any of these, and guessing wrong produces a single-column table rather than an error.

Encoding Is Frequently Undeclared

CSV carries no encoding declaration. A file may be UTF-8, UTF-8 with a byte order mark, Windows-1252, or a regional codepage. Reading Windows-1252 as UTF-8 turns accented characters into replacement characters, and the load still succeeds.

Excel Adds Its Own Transformations

If the CSV passed through a spreadsheet application on its way to you, it may already be damaged. Long numeric identifiers become scientific notation. Values resembling dates are converted. Leading zeros are stripped. These changes happen on open, before anyone saves anything, and they are not recoverable from the modified file.

The Procedure

Six steps. The ordering matters, because each step establishes something the next one depends on.

Step 1: Profile Before Loading Anything

Open the file in a table editor and look at the actual values. Specifically:

• Does the header row exist, and is it a single row? Multi-row headers from formatted reports are common and break naive parsers.
• Are there trailing empty rows or a summary total row at the bottom? Totals rows loaded as data are a classic source of double counting.
• Do any columns mix formats, such as dates in two different layouts?
• Are there merged cells or blank spacer columns from a human-formatted report?
• Do numeric columns contain currency symbols, thousands separators, or parenthesised negatives?

Fix these at the source. Cleaning in a table editor where you can see the data is faster and safer than writing transformation SQL against values you have not inspected.

Step 2: Decide the Target Schema Explicitly

Do not let a tool infer your column types. Inferred schemas are how a postal code column becomes an integer. Write the DDL yourself, and for the initial load prefer wide text types for anything ambiguous.

Loading everything as text into staging and casting deliberately in SQL is slower to write and dramatically safer, because a failed explicit cast is an error you see, whereas a silent coercion is not.

Step 3: Generate Dialect-Correct SQL

Identifier quoting differs across databases: backticks in MySQL, double quotes in PostgreSQL and SQLite, square brackets in SQL Server. A script generated for the wrong dialect fails immediately, which is the good case, or succeeds against a table you did not intend, which is not.

Batch the INSERTs rather than emitting one statement per row, and set the table name to the staging table rather than the production target. Tools such as the Develop Box Online Converters Excel-to-SQL generator let you set the database dialect, table name, and batch mode before generating, which handles the mechanical part correctly.

Step 4: Load Into Staging

The staging table has no indexes, no foreign keys, no triggers, no constraints beyond nullability, and text columns for everything. It exists to get bytes into the database where SQL can examine them.

This is the single highest-value habit in the entire procedure. A bad load into staging is a truncate away from being undone. A bad load into a production table with foreign key children is an incident.

Step 5: Validate in SQL

Now that the data is queryable, check it properly. Row count is the least interesting check. Run these instead:

• Null counts per column. A column that should never be null and has 4,000 nulls indicates a parsing or column-alignment problem.
• Distinct count on identifier columns. If it is lower than the row count, you have duplicates that will violate a unique constraint on promotion.
• Min and max on every date and numeric column. A date of 1899-12-30 means an Excel serial number was misread. A maximum amount six orders of magnitude above the median means a decimal separator was misparsed.
• Length distribution on text columns. Values at exactly the column width suggest truncation. Values of length zero versus null distinguish missing from empty.
• Explicit cast test. Attempt the cast to the real type on every column and count failures. This finds the rows that would have coerced silently.
• Character sanity check. Search for the Unicode replacement character. Any occurrence means an encoding mismatch upstream.

Write these as a reusable validation script. You will run it on every load, and having it saved converts a twenty-minute manual review into a thirty-second execution.

Step 6: Promote in One Server-Side Statement

Move staging to target with an INSERT SELECT that performs the casts explicitly. Wrap it in a transaction. This keeps all the data movement inside the database, avoids a second network transfer, and gives you a single atomic operation that either fully succeeds or fully rolls back.

If the target already contains rows that may overlap, use the dialect's upsert form rather than a plain insert, and be explicit about which columns constitute the conflict key.

Silent Corruptions and How to Catch Each One

Make It Rerunnable Before You Need To

Every bulk load will eventually need to be rerun: the source file was wrong, a mapping was misunderstood, or the load failed halfway. Designing for that in advance is far cheaper than improvising during an incident.

• Truncate and reload staging every time. Never append to staging. It should always reflect exactly one source file.
• Give every load a batch identifier. A column recording which load a row came from makes selective rollback possible.
• Prefer upsert over insert for promotion. Rerunning then converges rather than duplicating.
• Keep the source file, unmodified, alongside the load record. When someone questions a figure six months later, the original bytes are the only authoritative answer.
• Record the validation output, not just the fact that it passed. The counts and ranges at load time are what let you prove nothing changed afterward.

Frequently Asked Questions

Is a staging table worth it for a one-off load?

Especially for a one-off load. One-off loads get the least review and the least testing, which is exactly when an undo path matters most. Creating a staging table costs one statement and converts an irreversible operation into a reversible one.

Why load everything as text instead of the correct types?

Because it makes coercion failures visible. When staging columns are text, you can query for values that will not cast and see exactly which rows are problematic. When staging columns are typed, the database silently coerces what it can, and you lose the evidence.

How do I keep Excel from damaging a CSV before I load it?

Do not open it in Excel. Use a text editor or a browser-based table editor that does not apply automatic type conversion. If a spreadsheet must be involved, use its text import wizard and mark every column as text explicitly rather than double-clicking the file.

Conclusion: The Row Count Proves Almost Nothing

Bulk loading feels like a mechanical task, which is why it is so often done without a procedure. The failures it produces are not loud crashes but quiet wrong values, and those surface weeks later in a report that nobody can reconcile.

Profile the source, choose the schema deliberately, land in staging, validate with real queries, and promote server-side in one transaction. It adds perhaps twenty minutes to a load, and it is the difference between data you can defend and data you merely hope is correct.