Data engineer

Moving legacy Excel data into a database

Convert each legacy workbook to SQL with inferred column types, clean identifier names, NULL for empty cells and batched INSERT statements for MySQL, PostgreSQL or SQLite. Review the schema, run the dump in a transaction and compare row counts with the source.

Database administrator with a laptop in a server room aisle

Many teams run part of the business on workbooks built over years: customer lists, asset registers, order history, spread across dozens of XLS and XLSX files with slightly different layouts. Some old XLS files hit the 65,536 row limit and were split by year.

Moving that data into a database by hand means writing schemas, cleaning headers, fixing dates stored as serial numbers and identifiers that lost their leading zeros. A careless import creates wrong types that stay in the database for years.

The workflow

1Gather workbooksUpload the legacy files as a batch, or place them in an S3 bucket. Split files with the same layout, such as one workbook per year, are grouped together.
2Infer the schemaEvery value in each column is inspected to propose types such as INT, DECIMAL, DATE or VARCHAR with a length, and identifier columns with leading zeros stay text.
3Review and adjustYou check the proposed table and column names and types, override where needed and save them as a template, so all files of the group use the same schema.
4Generate SQLA CREATE TABLE statement and batched multi row INSERT statements are generated for your dialect, with empty cells as NULL and strings escaped.
5Load and verifyRun the dump inside a transaction, then compare row counts and a few totals with the source workbooks.

An example calculation

Example calculation, not a customer result. If writing schemas and cleaning 14 workbooks by hand takes a data engineer 30 hours, at a loaded cost of 85 USD per hour that is 2,550 USD for one migration. A Pro plan billed yearly costs 288 USD, and Business at 888 USD per year adds the scheduled monthly loads.

Pipeline for this job

Source: Batch upload of 14 workbooks, orders_2012.xls to orders_2025.xlsx
Destination: SQL dump files for PostgreSQL, downloaded or written to an S3 bucket
Schedule: One time migration, then monthly for workbooks still maintained by hand

Which plan fits

Pro covers a one time migration of files up to 100 MB in batches of 50. Business suits ongoing monthly loads from workbooks still in use, and larger files up to 500 MB.

See plans

Converters used

Questions about this workflow

Which databases are supported

SQL dumps are generated for MySQL, PostgreSQL and SQLite, each with its own identifier quoting, boolean type and escaping rules.

Can I change the inferred column types

Yes. Every inferred type is shown before SQL is generated, and you can override any of them, for example to keep an account number as text.

How are very large sheets loaded fastest

Use the generated CREATE TABLE statement and export the data as CSV for COPY in PostgreSQL or LOAD DATA in MySQL, which is faster than INSERT statements.