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.
| Table | Columns | One row is |
|---|---|---|
| customers | customer_id, name, phone, zip | One customer |
| orders | order_id, customer_id, order_date, total | One 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
| Kind of value | Use | Why |
|---|---|---|
| ZIP codes, phone numbers, IDs with zeros | Text | You never do arithmetic on them, and a number type drops the leading zeros. |
| Money | Decimal, such as DECIMAL(12,2) | Floating-point types can introduce rounding errors in totals. |
| Dates | A real date type | It sorts correctly and rejects impossible dates. |
| Yes or no | Boolean | It 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 customersIf 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.