Getting Excel Data Into a Database Without Heartbreak

Somebody sends a workbook and asks for it in the database. The first attempt usually works: the wizard runs, a table appears, the numbers look right. The trouble arrives later, when a zip code has lost its leading zero, a date column is half dates and half text, the row count is a few short, and the same file loaded again doubles the data. None of these are bugs in the tools. They come from loading a format that does not promise types or structure into a system that demands both, without a step in between.

This guide describes the method that avoids those problems, and it works the same for PostgreSQL, MySQL, SQLite, SQL Server and DuckDB. It is the map for the cluster; each section links to the articles that go deep on one part: PostgreSQL with Python, MySQL, SQLite, SQL Server with the wizard and with OPENROWSET, and DuckDB. It assumes you know what is in the file, which what is inside an Excel file explains.

The method in five steps

Every reliable Excel load follows the same shape, whatever the tool:

1. Look at the file. Sheets, header row, merged cells, hidden content, formulas, date columns.

2. Read it without interpretation. Take every cell as text if you can, so nothing is changed on the way in.

3. Stage it. Put the raw text in a table whose columns are all text, with columns that say which file, which sheet and which row each value came from.

4. Check it. Compare row counts and a few column totals against what the sender expects.

5. Convert and load it. Cast to real types in SQL, send rows that fail to a rejects table, and insert the rest into the final tables, idempotently.

Skipping step 2 is how identifiers lose their zeros. Skipping step 3 is how you end up debugging a bad value with no way to find the cell it came from. Skipping step 4 is how a load that silently dropped 3 percent of the rows reaches a report. Skipping the idempotency in step 5 is how data gets doubled. The method costs perhaps an hour the first time and nearly nothing afterwards, and it turns a fragile one-off into a job you can run every week.

Step 1: look before you load

Open the workbook, or inspect it with a script, and answer a short list of questions. How many sheets, and are any hidden? Where is the header row, and is it a single row? Are there merged cells, subtotal rows or notes under the data? Which columns hold identifiers (zip codes, account numbers, long ids), which hold dates, and which hold money? Are there formulas, and do they have cached values? The earlier articles cover each: merged cells, hidden rows and other things that break imports, what happens to formulas when code reads an Excel file, and detecting mixed types in an Excel column before import.

Decide what the target is before you load. A data dictionary of the final table, with a name, type and rule for each column, makes every later decision mechanical. If the source columns are Order No, Customer, Amount (EUR) and Date, the target may be order_no text, customer text, amount_eur numeric(12,2) and ordered_on date. Write that down, and write down what a null means.

If you can influence the sender, the best fix is upstream: one sheet, one header row in row 1, no merges, identifiers formatted as text, dates as real dates, no totals row. Most senders will do this once asked. The rest of the guide assumes you cannot.

Step 2: read without interpreting

Readers differ in how much they interpret. The least interpretive options:

• pandas with dtype=str reads every cell as a string, keeping 007 as 007. Add keep_default_na=False so strings such as NA stay strings:

import pandas as pd

df = pd.read_excel("orders.xlsx", sheet_name="Orders", dtype=str, keep_default_na=False)
print(df.head(2).to_dict("records"))

[{'Order No': '007', 'Customer': 'Acme', 'Amount (EUR)': '19.99', 'Date': '45292'}]

The date is still a serial number in text, because that is what the cell holds; the raw read keeps it that way, and you convert it later in one known place (importing Excel dates correctly into a date column). A caveat: reading with dtype=str turns a numeric cell 19.99 into the text 19.99 using Python's conversion, which for a float like 0.1 + 0.2 can show 17 digits. Round amounts in the staging step or read them as floats and round there.

• openpyxl gives cell-level control and can read the number format of every cell, which is how you tell a date from a number (reading and writing Excel files with openpyxl).

• DuckDB's Excel extension has an option to read everything as text and can run SQL directly on the sheet (loading Excel files with DuckDB).

• Wizards (MySQL Workbench, SQL Server Import and Export) guess types from sampled rows. They are convenient and the guessing is exactly what you are trying to avoid; use them for exploration.

When a sheet has title rows above the header, tell the reader where the header is (handling Excel files with title rows above the header). When a workbook has several sheets, read each one explicitly (loading multiple Excel sheets into separate tables).

Step 3: stage the raw text

A staging table mirrors the sheet as text and adds provenance columns:

CREATE TABLE stage_orders (
    batch_id    bigint      NOT NULL,
    source_file text        NOT NULL,
    source_sheet text       NOT NULL,
    source_row  integer     NOT NULL,
    order_no    text,
    customer    text,
    amount_eur  text,
    ordered     text
);

The first four columns are the provenance. batch_id identifies one load. source_row is the row number in the sheet (so a bad value maps back to Orders!A17 without guessing). The other columns hold the raw text, with no constraints, because the stage must accept anything. A stage that rejects rows would hide the problems you want to see.

Load the dataframe into it. For small files, to_sql is enough; for large ones, PostgreSQL's COPY through psycopg is much faster (staging Excel data in a table before cleaning it):

import psycopg

df["source_row"] = df.index + 2          # header is row 1, data starts on row 2
rows = [
    (batch_id, "orders.xlsx", "Orders", r.source_row, r["Order No"], r["Customer"], r["Amount (EUR)"], r["Date"])
    for _, r in df.iterrows()
]
with psycopg.connect("dbname=app") as conn, conn.cursor() as cur:
    with cur.copy("COPY stage_orders FROM STDIN") as copy:
        for row in rows:
            copy.write_row(row)

The row number is computed from the dataframe index plus the header offset. If you skipped title rows with skiprows, add the number you skipped, or the references will be off. Keep the staging tables for a while after the load. Disk is cheap and the ability to answer "where did this value come from" is not.

Step 4: check the load

Before any conversion, prove that everything arrived. Compare counts:

SELECT count(*) AS staged FROM stage_orders WHERE batch_id = 42;

with the number of data rows in the file, which you know from the sender or from counting rows in the sheet. They should agree exactly. Also compare a column total, converted on the fly, with the total in the sheet. If the staged count is lower, rows were dropped by the reader (a blank row in the middle ended the read, a hidden-row rule skipped some, a limit was hit). If it is higher, the sheet contained rows that are not data, such as totals or notes. Validating row counts after an Excel import covers the checks in detail, including how to count rows in the source without Excel.

Add data-shape checks here too, since they are cheap:

• Are there rows where every column is empty?

• Is the key column unique, or are there duplicates?

• Do any rows look like totals (first column starts with "Total")?

• Does the number of distinct values in a categorical column look right?

A failed check means stop and talk to the sender, or fix the read. Do not push forward hoping the conversion will sort it out.

Step 5: convert and load

With clean text in the stage, define the conversions in SQL, once. Typical ones:

INSERT INTO orders (order_no, customer, amount_eur, ordered_on)
SELECT
    order_no,
    customer,
    amount_eur::numeric(12,2),
    DATE '1899-12-30' + ordered::integer
FROM stage_orders
WHERE batch_id = 42
ON CONFLICT (order_no) DO UPDATE
SET customer = EXCLUDED.customer,
    amount_eur = EXCLUDED.amount_eur,
    ordered_on = EXCLUDED.ordered_on;

Several decisions are visible in that statement. The amount is cast from text, and a malformed value raises an error and stops the whole statement, which is useful in a first run and annoying in a regular one. The date is computed from the Excel serial number by adding the days to 30 December 1899, which is correct for dates after February 1900 (converting Excel serial dates to real dates in SQL). The ON CONFLICT clause makes the load idempotent for orders keyed by order_no: loading the same file twice updates the rows and adds none.

To keep the load running when a few rows are bad, separate the good rows from the bad before inserting. A common way is a validation view or query that flags each staged row:

CREATE VIEW stage_orders_checked AS
SELECT s.*,
       (amount_eur ~ '^[0-9]+(\.[0-9]{1,2})?$') AS amount_ok,
       (ordered ~ '^[0-9]+$')                    AS date_ok
FROM stage_orders s;

Insert from the rows where all flags are true, and write the others to a rejected_rows table with the reason, the batch, the file and the row number. Then someone can fix the source or the rule, with the evidence in front of them. Never discard rejects silently.

The conversions differ by engine, but the principle does not. PostgreSQL has ::numeric and to_date; MySQL has CAST and STR_TO_DATE; SQLite stores dates as text and has date(); SQL Server has TRY_CONVERT, which returns null instead of raising, a good fit for the checked-view approach. MySQL and SQLite details are in their articles. A few type habits help in all of them:

• Money goes into numeric or decimal, never a float.

• Identifiers stay text, even if they look numeric.

• Dates go into date or timestamp columns, converted from serials or parsed from text with an explicit format.

• Flags are mapped from the allowed strings (Y, Yes, 1, TRUE) to booleans with a CASE, and anything else is a reject.

Repeatability: the same file twice

Real jobs run again. Decide what a second load of the same file means. Three techniques, from lightest to heaviest:

A natural key with an upsert. If each row has a stable key, ON CONFLICT (PostgreSQL, SQLite) or ON DUPLICATE KEY UPDATE (MySQL) makes reloading harmless.

A file fingerprint. Compute a hash of the file and store it in a load_batches table. Refuse to load a file whose hash is already recorded, or replace the earlier batch.

import hashlib

def sha256_of(path):
    h = hashlib.sha256()
    with open(path, "rb") as f:
        for block in iter(lambda: f.read(1 << 20), b""):
            h.update(block)
    return h.hexdigest()

Replace by partition. If each file is a full snapshot for a period (March's orders), delete the period's rows and insert the new ones in one transaction. Wrap the delete and the insert in the same transaction, so a failure leaves the old data intact.

Record every batch: file name, hash, loaded at, row count, who ran it. When a number in a report is questioned in six months, that table is the answer.

Choosing a tool

For large files, the fastest route is often to convert the sheet to CSV first (the conversion itself is a read of the workbook) and then use the engine's bulk loader: COPY in PostgreSQL, LOAD DATA in MySQL, .import in SQLite, BULK INSERT in SQL Server. You lose nothing by doing so if you read as text, and you gain the loader's speed. See saving an Excel sheet as CSV and converting Excel to CSV for the conversion and its encoding traps.

Tools that need care

Wizards. The MySQL Workbench import wizard reads CSV and JSON, not Excel. SQL Server's Import and Export Wizard reads Excel through the ACE provider, which must be installed and match the bitness of the tool, and which guesses column types from the first rows. A column whose first eight rows are numbers and later rows contain text loses the text as nulls. That is the most common wizard failure and gives no error.

**OPENROWSET and linked servers.** They run on the database server, not on your machine: the file must be reachable from the server, the service account needs permission, and ad hoc distributed queries must be enabled. That setting widens the attack surface of the server, which is a reason to prefer loading from outside (importing Excel data into SQL Server with OPENROWSET).

Untrusted files. A workbook from a stranger is an input like any other. Do not enable macros, do not open it with a tool that evaluates external links, and keep the loader's permissions narrow. XLSX files are zips, and a hostile one can be much larger when unpacked than it looks.

Dates, numbers and text in more detail

Most rejected rows in a real load come from three kinds of value, and it is worth deciding the rules before the first run.

Dates. A date cell arrives as a serial number when you read raw text, as a datetime when a library applied the format, or as text when someone typed it. A single column can contain all three. Decide the canonical form (a date column in the database), then convert each case: a serial through the epoch arithmetic, a datetime directly, text through to_date with an explicit pattern. Never rely on the database's guess for text dates, because 03/04/2024 is March or April depending on the session setting. If a column mixes forms, count each form first, so you know how much of the data each rule touches (dealing with text that looks like a date in Excel). Time zones do not exist in the file; document the zone the sender used and store timestamps with an explicit offset where it matters.

Numbers. Amounts typed by people contain thousands separators, currency symbols, trailing spaces and the occasional letter O instead of a zero. In a cell formatted as a number, the stored value is clean, and only the display has symbols. In a text cell, you get what was typed. The safe pipeline strips known decoration with a regular expression, converts, and rejects what remains. Percentages stored as numbers are fractions: 0.45, not 45. Negative numbers in accounting format may arrive as text in parentheses. Write the rules for each column and test them against a sample that includes the ugly cases.

Text. Trim leading and trailing whitespace, including the non-breaking space (code 160) that survives an ordinary trim and breaks joins. Normalize case only where the business treats ACME and Acme as the same customer. Watch for invisible line breaks in headers; a column called Amount\n(EUR) is a different name from Amount (EUR). Match on cleaned header names, and fail loudly when an expected column is missing instead of loading the others.

A test file that includes one example of every ugly case pays for itself. Keep it in the repository next to the loader, with the expected output, and run it whenever the rules change.

Performance for larger workbooks

Reading XLSX is the slow part. The package must be unzipped and its XML parsed, and the shared strings table loaded, before the first row appears. A workbook with a few hundred thousand rows takes noticeable time with any library, and memory use grows with the file. Three habits keep it manageable.

First, read only what you need: the sheet, the range and the columns. Both pandas (usecols, nrows, skiprows) and openpyxl (read-only mode with iter_rows) support this (reading large Excel files in read-only mode). Second, use a faster reader when the file is big. The Rust-based calamine engine reads XLSX far faster than openpyxl in typical use, and pandas can use it by name (reading Excel files faster with python calamine). Measure with your own file before you rely on any speed claim. Third, load in bulk. Row-by-row INSERT statements are slow in every engine because of per-statement overhead; batch them with executemany, multi-row VALUES, or the engine's copy protocol.

For very large sheets, converting to CSV once and loading the CSV with the native bulk loader is often fastest overall. The cost is the extra file and the encoding choices, and the benefit is a loader that moves millions of rows per minute. If the sheet exceeds a million rows, it is not an Excel file in any sense that matters: ask for CSV or a database export at the source (Excel row and column limits).

Testing the loader

A loader is code, and it needs tests like code. A small suite catches the regressions that cost most:

• A golden file. A tiny workbook with every quirk (a leading-zero id, a serial date, a text date, a blank row, a merged region) and the exact rows expected in the final table.

• A count test. The staged row count equals the number of data rows in the golden file.

• An idempotency test. Run the load twice and assert that the row count does not change.

• A reject test. Feed a row with a bad amount and assert that it lands in the rejects table with a reason and does not stop the batch.

• A schema test. Rename a header in the golden file and assert that the loader fails with a message naming the missing column.

Use a throwaway database for these tests, started the same way in development and continuous integration (database workflows in CI YAML). Generate the golden workbook from code with openpyxl so the repository stores the script and not an opaque binary.

Permissions and safety

A loader touches three trust boundaries: the file, the machine that reads it and the database it writes to. Treat each deliberately. The file comes from a person or a system outside your control, so parse it with a maintained library, never evaluate anything it contains, and ignore macros and external links. The machine running the loader should have read access to the incoming folder and nothing else. The database account should be able to insert into the staging and target tables and read what it needs, and not to drop or alter anything. A loader that connects as a superuser turns every malformed file into a potential incident.

Be careful with personal data. A workbook of customers carries names, emails and sometimes identifiers that regulation protects. Load it into a database with the right access controls, keep the staging tables only as long as needed, and do not copy the raw workbook into places with wider access, such as a shared chat or a ticket. If you need an example for a bug report, anonymize it first.

Finally, log without leaking. A rejects table that stores the full offending row is useful and sensitive in the same breath, so give it the same access rules as the data. Logs that print row contents should stop at the row number and the column name.

Monitoring a recurring load

When the job runs every night, watch it. Keep the batch table small and informative: when it ran, which file, how many rows read, how many loaded, how many rejected. Alert on rules that indicate a problem even when the job succeeds: a row count that differs from the previous file by more than a set percentage, a sudden jump in rejects, a file with a new column, a file that did not arrive. A load that succeeds with 0 rows is the quietest failure of all, and it is also the easiest to detect with a count.

Keep the last few input files, with their hashes, in cheap storage. When a number is questioned, you can reload the same file into a scratch database and reproduce the result, which ends most arguments quickly.

Common failures and where to look

Leading zeros are gone. The reader turned the id into a number. Read as text, and see keeping leading zeros in Excel and when importing.

Dates are five-digit numbers. The reader did not apply the date format. Convert from serial, and check the date system (how Excel stores dates as serial numbers).

Long numbers end in zeros. The sender's Excel damaged them before the file was written (Excel number precision and the 15 digit limit). Go back to the source system.

Column values vanish. The reader sampled the type from the first rows, found numbers and discarded text. Read as text.

Row counts are off by a few. Hidden rows, blank rows, a totals row, or merged regions. Compare with the source and read the cleaning article.

The load works on your machine and not on the server. File path, permissions, a missing provider, or a different version of the library.

The job duplicates data on a retry. Add the key, the hash or the partition replace.

An end-to-end shape

Put together, a good job looks like this, in any language:

1. Compute the file's hash and check the batch table.

2. Read each expected sheet as text, with explicit header rows.

3. Add provenance columns and bulk load into the stage.

4. Check counts and shapes. Stop on failure.

5. Convert with SQL into typed tables, sending rejects to a rejects table.

6. Record the batch with counts of loaded and rejected rows.

7. Keep the stage for a set period, then drop it.

The whole job can be a script of a hundred lines, and the SQL is the part you read when something looks wrong. If you later move from Excel to CSV or an API as the source, only steps 2 and 3 change.

When loading Excel into a database is the wrong job

The workbook is a report with formatting, subtotals and charts. The data in it is a derived summary. Load the underlying source, not the report.

People still edit the workbook as the master. A database copy becomes stale the moment someone changes the sheet. Either move the editing into a system with a form and constraints, or accept that the sheet is the master and treat the database as a snapshot refreshed on a schedule.

A BI tool could read the workbook directly. If the only goal is a chart, a connector that reads the file may be less work than a pipeline. The database earns its place when several consumers or joins need the data.

The data is tiny and used once. For a 40-row lookup, typing it into a VALUES list may be faster than building a loader. Do not build a pipeline for a one-time paste.

The workbook relies on formulas the database must reproduce. Then the job is translation, which is covered in translating Excel formulas into SQL. Load the inputs, reimplement the logic, and compare to the workbook's cached results.

The PostgreSQL loader is described in the COPY reference, the MySQL equivalent in LOAD DATA, and the pandas reader in the read_excel documentation. For a quick look at a small extract, the CSV to JSON converter works on pasted text.