Skip to content
Get a free demo
Guide

How to move Excel data into a database

The import command is the easy part. The work is in splitting one wide sheet into proper tables and choosing types that won’t damage your values.

Split the sheet into tables

A spreadsheet tends to repeat itself: the same customer name and address appear on every order. A database stores each fact once and connects the tables with IDs.

One wide sheet becomes two tables
TableColumnsOne row is
customerscustomer_id, name, phone, zipOne customer
ordersorder_id, customer_id, order_date, totalOne order, pointing at its customer

To find your tables, ask what each column describes. If several columns repeat on every row and all describe the same thing, they belong in a table of their own. Columns such as Item 1, Item 2 and Item 3 are another sign that a separate table is needed.

Choose column types that protect your values

Which type for which kind of column
Kind of valueUseWhy
ZIP codes, phone numbers, IDs with zerosTextYou never do arithmetic on them, and a number type drops the leading zeros.
MoneyDecimal, such as DECIMAL(12,2)Floating-point types can introduce rounding errors in totals.
DatesA real date typeIt sorts correctly and rejects impossible dates.
Yes or noBooleanIt stops Y, yes, TRUE and 1 from all appearing in one column.

Choose the types before you import. If you let an import tool guess from the first rows, it may decide a column is numeric and then choke on, or silently damage, a value further down.

Clean and export

Fix the data while it is still easy to read. Remove blank rows, repeated headings and exact duplicates, one heading row on top, and resolve any ambiguous dates. Our guide to cleaning a messy spreadsheet has the order of steps.

Write dates as 2025-03-04, because every database reads that form correctly. Then save each table as its own CSV in UTF-8 using File > Save As > CSV UTF-8, so accented letters survive.

Import it

SQLite: the simplest place to start

SQLite is one file on your computer with no server to run, which makes it a good place to practise. In the sqlite3 shell:

.import --csv customers.csv customers

If the table doesn’t exist yet, SQLite creates it and names the columns from the first row. If it already exists, the first row is imported as data, so add --skip 1.

PostgreSQL

Create the table with your chosen types first, and then load the file:

COPY customers FROM '/path/customers.csv' WITH (FORMAT csv, HEADER);

That path is read by the database server. To send a file from your own computer, use the same options with \copy in psql.

MySQL

LOAD DATA LOCAL INFILE 'customers.csv' INTO TABLE customers
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;

The server and the client must both allow LOCAL loading, and it is often off by default.

SQL Server

SQL Server Management Studio has an import wizard for flat files, under Tasks when you right-click a database. Check the column types it proposes on the screen where they are listed, since this is where a ZIP code can become a number.

Verify every table

Don’t trust an import that merely finished without errors. Compare counts and totals against the sheet:

SELECT COUNT(*), SUM(total) FROM orders;

-- orders pointing at a customer that does not exist
SELECT o.order_id FROM orders o
LEFT JOIN customers c ON c.customer_id = o.customer_id
WHERE c.customer_id IS NULL;

Look at a few awkward records by hand too: a name with an accent, the longest address, and an ID with leading zeros.

A database still needs a front end

Most people won’t type into a database directly, so plan what they will use. That might be a reporting tool for reading, a no-code builder connected to the database, or a custom app built for your steps. Someone also needs to own backups, because a database with no tested backup is riskier than the spreadsheet it replaced.

Questions

Questions about moving Excel to a database

Which database should a small business pick?

If you only need a local file for one person, SQLite is hard to beat. For several people using it at once, PostgreSQL and MySQL are both free, widely supported and available from many hosting providers. If you already pay for Microsoft services, SQL Server or Azure SQL may fit better.

Can I keep using Excel after moving the data?

Yes, for reading. Excel can connect to most databases through Data > Get Data, which gives you live reports without keeping a second copy to maintain. Keep the editing in one place, though.

Is Microsoft Access a database I could use for this?

It is, and it imports Excel directly. It suits one desk or a small office on a shared network, with a limit of 2 GB per database file. Our guide to replacing Access covers when that stops being enough.

Want the database and the app done for you?

We design the tables, move your data across, and build the screens your team will actually use, starting with a free demo.

Get a free demo