Importing and Exporting Data

Data often arrives as files — especially CSV — and results often need to leave as files. Each database has fast bulk tools for this.

SQLite

sqlite> .mode csv
sqlite> .import --skip 1 new_customers.csv customers
sqlite> .headers on
sqlite> .once report.csv
sqlite> SELECT name, city FROM customers;

.import loads a CSV file into a table (--skip 1 skips the header row); .once file writes the next query's output to a file.

PostgreSQL

-- PostgreSQL
COPY customers (name, email, city, joined_on)
FROM '/data/new_customers.csv' WITH (FORMAT csv, HEADER true);

COPY (SELECT name, city FROM customers) TO '/data/report.csv' WITH (FORMAT csv, HEADER true);

COPY reads files on the database server. From your own computer, use psql's \copy command, which has the same syntax but reads local files.

MySQL

-- MySQL
LOAD DATA LOCAL INFILE 'new_customers.csv'
INTO TABLE customers
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
IGNORE 1 LINES
(name, email, city, joined_on);

A safer import pattern: staging tables

Real files are messy. Rather than loading straight into your real tables:

1. Load the file into a staging table with every column as text.
2. Check it with queries: missing values, duplicates, invalid dates, numbers that aren't numbers.
3. Clean and insert into the real table with INSERT ... SELECT, converting types as you go.

-- standalone
CREATE TABLE staging_prices (sku TEXT, price TEXT);
INSERT INTO staging_prices VALUES ('A1', '10.50'), ('B2', 'n/a'), ('C3', ' 7 ');

SELECT sku, price
FROM staging_prices
WHERE CAST(TRIM(price) AS REAL) = 0 AND TRIM(price) NOT IN ('0', '0.0');

+-----+-------+
| sku | price |
+-----+-------+
| B2  | n/a   |
+-----+-------+

The check finds the value that isn't a number, before it can corrupt the real table. The Random CSV Generator is handy for creating practice files.

Common mistakes

• Character encoding problems — accented names turning into é. Save files as UTF-8, and tell the import tool the encoding.
• Date formats: 03/04/2025 means different dates in different countries. Convert to ISO (2025-04-03) during cleaning.