CSV to SQLite: 4 Ways to Import CSV Data Into a Database

You have a CSV. You want it in SQLite. This should be a two-minute job, and it usually is — right up until it isn't.

I've imported CSVs into SQLite more times than I can count, across spreadsheets exported from Google Sheets, database dumps from a client's old MySQL box, log files someone renamed to .csv, and one memorable file that turned out to be UTF-16 with a BOM and semicolon delimiters. Every one of those was "just a CSV."

There are four sane ways to do this, and which one you pick depends on two questions: how often are you going to do this, and how messy is the CSV. If it's a one-off and the file is clean, the sqlite3 CLI will do it in three lines. If you're going to re-run the import every week from a script, you want Python. If the file is a mess and you'd rather click than debug, a GUI wizard earns its keep.

Here's the short version:

• sqlite3 CLI — fastest for clean, one-off files. No install on macOS. Everything is TEXT unless you say otherwise.
• DB Browser for SQLite — a GUI wizard with delimiter, encoding, and header options. Good middle ground.
• DBeaver — the import wizard for when the file is genuinely gnarly, or when you already live in DBeaver. Overkill for a simple file.
• Python + pandas — the answer when the import needs to be repeatable, scriptable, or part of a pipeline.

I'll walk through all four with real commands, then cover the part almost every tutorial skips: what actually goes wrong, and how to fix the data after it lands.

Before you import: what actually goes wrong with CSVs

CSV is a format with a specification — RFC 4180 — that almost nobody follows. That's the root of most pain. Here's what I hit most often.

Encodings and the UTF-8 BOM

The single most common failure: a CSV exported from Excel on Windows is often saved as UTF-8 with a BOM (byte order mark), or occasionally as Windows-1252 / Latin-1. The BOM is three invisible bytes at the start of the file. If your import tool doesn't strip them, your first column header becomes \ufeffid instead of id, and now every query that references id fails with "no such column."

If you see a weird character like  or  at the start of your first column name, that's the BOM. In the sqlite3 CLI you can usually avoid the problem by pre-cleaning the file. In Python, pandas.read_csv handles it if you pass encoding="utf-8-sig".

Non-UTF-8 encodings are the other half of this. A file saved as Windows-1252 will import, but accented characters turn into mojibake (café instead of café). You fix it by decoding with the right encoding, not by editing rows by hand.

Delimiters other than commas

Despite the name, plenty of "CSV" files are semicolon-separated (common in European Excel exports, where the comma is the decimal separator), tab-separated, or pipe-separated. The sqlite3 CLI can handle these with .separator, but if you don't notice the delimiter is a semicolon, you get one giant column.

Quoted fields containing commas, quotes, and newlines

This is where naive parsers break. A well-formed CSV quotes any field that contains a comma, a quote, or a newline:

id,name,notes
1,"Smith, John","He said ""hello"" and left"
2,Jane,"Line one
Line two"

The sqlite3 CSV importer and pandas both handle this correctly. A split(",") in your own script does not. If a row count comes out wrong — too many rows — it's almost always an embedded newline inside a quoted field that a naive parser split into two records.

Inconsistent date formats

One column, three formats: 2026-01-15, 01/15/2026, and 15 Jan 2026. SQLite has no native date type, so whatever lands lands as text, and then ORDER BY date sorts lexically and gives you nonsense. Decide on one format (I use ISO 8601, YYYY-MM-DD) and normalize before or after import.

Leading zeros

Zip codes, phone numbers, product SKUs — anything with a leading zero. If your import declares the column as INTEGER, 00501 becomes 501. If you let it land as TEXT, the zero survives. This is a type decision you have to make deliberately.

Empty columns and blank rows

Trailing commas produce empty trailing columns. Blank lines produce rows where every value is empty. Neither errors — they just quietly pollute your table. Worth a quick look at the file before you import.

The "everything is TEXT" outcome

This is the big one, and it's specific to SQLite. SQLite is dynamically typed. It doesn't enforce column types the way PostgreSQL or MySQL do. Instead, each column has a type affinity — a hint about how values should be stored — and the storage class of each value is decided per-value.

What this means in practice: when the sqlite3 CLI imports a CSV, it creates the table's columns with no declared type unless you created the table yourself first. A column with no declared type has BLOB affinity, and values go in as text. So your price column full of 19.99 values is TEXT, your quantity column full of 5 is TEXT, and SUM(quantity) may not do what you expect because the values are stored as strings.

You can still run SUM() on text that looks numeric — SQLite will coerce it — but you lose the guarantees, comparisons get subtly weird, and any tool that reads the schema sees no types at all. The fix is to create the table yourself with the types you want, then import into it. I'll show that below.

That's the landscape. Now the four methods.

Method 1: The sqlite3 CLI

This is the fastest path for a clean, one-off file, and it's my default. First, a note on installing it: sqlite3 ships with macOS, so you already have it. On Windows it is not installed by default — you download the command-line tools from sqlite.org/download.html and put sqlite3.exe on your PATH. The full reference for these dot-commands is at sqlite.org/cli.html.

The two-line version

The bare minimum looks like this:

sqlite3 mydb.db

.mode csv
.import people.csv people

That's it. .mode csv tells the CLI to treat input and output as CSV, and .import file table reads the file into the table named people.

Two catches, and they're both important:

1. The table must already exist. .import will not create it. If people doesn't exist, you'll get an error. (There's a wrinkle: if the table name doesn't exist and you're importing a file, some versions create it — but the created table has untyped columns, which is the "everything is TEXT" problem. Don't rely on it. Create the table explicitly.)
2. Column order matters, and the header row gets imported as data. .import reads every line as data. If your CSV has a header, that header becomes your first row unless you skip it.

Handling the header row

The modern sqlite3 CLI supports a --skip option on .import:

.mode csv
.import --skip 1 people.csv people

--skip 1 skips the first line — exactly what you want for a header. (If you're on an older build without --skip, the classic workaround is to import the header as a row and then DELETE FROM people WHERE rowid = 1;, or to pipe the file through tail -n +2. I prefer --skip where it's available.)

The full worked example

Let's do this properly, with an explicit schema so the columns have real types. Suppose people.csv looks like this:

id,name,email,signup_date,account_balance
1,Alice,alice@example.com,2026-01-15,120.50
2,Bob,bob@example.com,2026-02-03,0
3,"Smith, Carol",carol@example.com,2026-02-20,42.00

Note the quoted "Smith, Carol" — that comma is inside quotes and must stay one field. Here's the whole session:

sqlite3 people.db

-- 1. Create the table with real types BEFORE importing.
CREATE TABLE people (
    id              INTEGER PRIMARY KEY,
    name            TEXT NOT NULL,
    email           TEXT UNIQUE,
    signup_date     TEXT,      -- ISO 8601 date, stored as text
    account_balance REAL
);

-- 2. Switch to CSV mode and import, skipping the header row.
.mode csv
.import --skip 1 people.csv people

-- 3. Verify.
SELECT COUNT(*) FROM people;          -- expect 3
SELECT * FROM people LIMIT 5;

Because I declared the types, id is an INTEGER, account_balance is REAL, and signup_date is TEXT holding an ISO date. The quoted name imported as the single value Smith, Carol. If I'd let the CLI create the table, every column would have been untyped.

If your file uses a different delimiter, set it before importing:

.mode csv
.separator ";"
.import --skip 1 people.csv people

A note on type affinity

Even with declared types, remember that SQLite's affinity rules are conversions, not gates. A column declared INTEGER will store 'abc' as text if you insert it — SQLite won't reject it. Declaring types gets you correct storage for well-formed data and correct behavior in SUM(), AVG(), comparisons, and indexes. It does not get you validation. If you need hard validation, you need CHECK constraints or application-level checks.

Method 2: DB Browser for SQLite

DB Browser for SQLite is a free, open-source GUI, and its CSV import wizard is the friendliest of the bunch for a one-off file. I reach for it when the file is small, I want to eyeball the preview before committing, or the person asking for help isn't comfortable in a terminal.

The workflow:

1. Open or create your .db file.
2. Go to File → Import → Table from CSV file…
3. Pick your CSV. You immediately get a preview with dropdowns for:
   • Field separator — comma, semicolon, tab, pipe, or a custom string.
   • Quote character — usually ", but some exports use '.
   • Encoding — UTF-8, UTF-16, Latin-1, and others. This is where you fix BOM and mojibake problems.
   • First row contains column names — check this to use the header as column names instead of a data row.
4. Edit the column names and types in the preview grid. This is the killer feature: you can set id to INTEGER, balance to REAL, and dates to TEXT right here, so you don't hit the "everything is TEXT" outcome.
5. Name the table, click OK, and it creates the table and imports the rows in one step.

The advantage over the CLI is that you see what's about to happen — the delimiter, the encoding, and the parsed columns are all visible before anything is written. If a quoted field with a comma is going to break, you'll see the column count go wrong in the preview.

The tradeoff is that it's clicky. Doing the same import every week through a wizard is tedious, and you can't put it in a script. For that, you want Method 4.

Method 3: DBeaver

DBeaver is a general-purpose database client — it talks to SQLite, PostgreSQL, MySQL, and dozens of others. If you already use it for your other databases, its CSV import wizard means you don't have to install or learn anything new. There's a full breakdown of how it stacks up against a purpose-built SQLite tool in my DBeaver comparison.

The SQLite import flow:

1. Connect to your SQLite database (DBeaver creates the connection from a .db file path).
2. Right-click the target table (or the database, to create a new one) → Import Data.
3. Choose CSV as the source and point at your file.
4. The wizard walks you through delimiter, quote character, encoding, and header row, with a live preview.
5. Map the CSV columns to the table columns, choose how to handle existing rows (append, truncate, etc.), and run it.

DBeaver's wizard is the most configurable of the three GUI options. It handles weird encodings, custom delimiters, and column mapping well, and because it's a real SQL client you can immediately run SELECT queries against the result without switching tools.

When it's overkill: if you only ever touch SQLite, and only occasionally, DBeaver is a large, Java-based application with a lot of surface area you'll never use. For a single clean CSV, opening the sqlite3 CLI and typing two commands is faster than the wizard. And if what you actually want is a viewer/editor for the resulting table rather than an import tool, a lighter local-first tool is a better fit — which is roughly the gap BaseVolt's feature set is aimed at. More on that at the end.

Method 4: Python + pandas

If the import needs to happen more than once — a weekly refresh, a build step, part of an ETL job — scripting it is the right call. Python with pandas is the standard approach, and it handles the messy cases (encoding, delimiters, dates, dtypes) far more gracefully than the CLI.

The core is two calls: read_csv to load, to_sql to write.

import sqlite3
import pandas as pd

# 1. Read the CSV. utf-8-sig strips a UTF-8 BOM if present.
df = pd.read_csv(
    "people.csv",
    encoding="utf-8-sig",
    sep=",",                 # change to ";" for semicolon files
    dtype={"id": "Int64"},   # keep id as integer, allow NULLs
    parse_dates=["signup_date"],
)

# 2. Normalize text columns (strip stray whitespace).
df["name"] = df["name"].str.strip()

# 3. Write to SQLite. if_exists="replace" rebuilds the table each run;
#    use "append" to add rows instead.
with sqlite3.connect("people.db") as conn:
    df.to_sql("people", conn, if_exists="replace", index=False)

A few things worth knowing about this pattern:

• if_exists="replace" drops and recreates the table every run — perfect for a full refresh, dangerous if the table has other data or is referenced by foreign keys. Use "append" when you're adding rows. If you need foreign keys enforced on the connection, set PRAGMA foreign_keys = ON; right after connecting; see my foreign keys guide for why that's off by default.
• Dtypes matter. pandas infers types, and it will happily make an id column a float if there are any missing values, turning 1 into 1.0. Declaring dtype explicitly avoids that.
• to_sql uses the table's declared types derived from the DataFrame dtypes, so you get real INTEGER / REAL / TEXT columns instead of the all-TEXT outcome.
• For big files, add chunksize=10000 to to_sql so it writes in batches instead of building one giant transaction in memory.
• You can also read CSV in chunks with chunksize on read_csv if the file doesn't fit in memory.

This is the method that scales: version it, put it in CI, re-run it on demand. The wizard and CLI methods are for humans; this one is for machines.

The comparison

Method	Best for	Handles messy CSVs	Repeatable	Setup effort
sqlite3 CLI	Clean one-off files; quick imports	Limited (delimiter and header only)	Via shell scripts	None on macOS; download on Windows
DB Browser for SQLite	One-off files where you want a visual preview	Good (encoding, delimiter, quote, types)	No — manual clicks	Install GUI (~small download)
DBeaver	Messy files, or when you already use DBeaver	Strong (full mapping, encoding, delimiters)	No — manual wizard	Install a large Java app
Python + pandas	Repeatable pipelines and scheduled imports	Strongest (explicit encoding, dtypes, dates)	Yes — fully scriptable	Python + pandas installed

The pattern: the CLI wins on speed for clean files, the GUIs win on visibility for messy one-offs, and pandas wins on repeatability. There's no single best answer, which is why it's four methods and not one.

A worked example end to end

Let me put it together with a small, slightly messy CSV so you can see the whole arc — from raw file to verified table. Call it orders.csv:

order_id,customer,email,order_date,total,notes
1001,Alice,alice@example.com,2026-03-01,49.99,
1002,"Doe, John",john@example.com,2026-03-02,12.50,"Rush order, ship fast"
1003,Alice,alice@example.com,2026-03-05,0,

Note three real-world wrinkles: a quoted name with a comma ("Doe, John"), a quoted note with a comma, and an empty trailing field on two rows. A naive split(",") would mangle both quoted fields.

Step 1 — create the table with types.

CREATE TABLE orders (
    order_id    INTEGER PRIMARY KEY,
    customer    TEXT NOT NULL,
    email       TEXT,
    order_date  TEXT,       -- ISO 8601
    total       REAL,
    notes       TEXT        -- empty string -> NULL we'll fix later
);

Step 2 — import.

.mode csv
.import --skip 1 orders.csv orders

Step 3 — verify the schema. .schema prints the CREATE TABLE statement as SQLite sees it, which confirms your types survived:

.schema orders
-- CREATE TABLE orders (
--     order_id    INTEGER PRIMARY KEY,
--     customer    TEXT NOT NULL,
--     email       TEXT,
--     order_date  TEXT,
--     total       REAL,
--     notes       TEXT
-- );

Step 4 — verify the row count. You imported a 3-row file (plus a header you skipped), so you expect 3:

SELECT COUNT(*) FROM orders;
-- 3

If you'd forgotten --skip 1, this would say 4 and you'd have a row with order_id = 'order_id'. That single check catches the most common import mistake.

Step 5 — spot-check the tricky rows. Confirm the quoted fields came through intact:

SELECT order_id, customer, total, notes FROM orders;
-- 1001 | Alice      | 49.99 | (null)
-- 1002 | Doe, John  | 12.50 | Rush order, ship fast
-- 1003 | Alice      | 0.0   | (null)

Doe, John and Rush order, ship fast are each single values — the quoting worked. And the empty trailing fields came in as NULL, which brings us to cleanup.

Fixing the data after import

The import is the easy half. Here's what I almost always do next.

Types: check what you actually got

If you didn't declare types (or used a tool that created the table for you), inspect them:

SELECT typeof(order_id), typeof(total) FROM orders LIMIT 1;

If typeof(total) returns 'text', your numbers are strings. Rather than rebuild, you can often just recreate the table with types and copy — or, if you're using pandas, re-run the import with explicit dtypes.

Dates

SQLite has no date type. Dates live as TEXT (ISO 8601 is the sane choice) or as INTEGER (Unix epoch) or REAL (Julian day). Whatever the format, keep it consistent. If a column has mixed formats, normalize:

-- Rewrite US-style MM/DD/YYYY into ISO YYYY-MM-DD.
UPDATE orders
SET order_date = substr(order_date, 7, 4) || '-' ||
                 substr(order_date, 1, 2) || '-' ||
                 substr(order_date, 4, 2)
WHERE order_date LIKE '__/__/____';

Then ORDER BY order_date sorts correctly because ISO strings sort lexically. For anything more complex, normalize in pandas with parse_dates before it ever hits the database — it's less error-prone than string surgery in SQL.

NULLs versus empty strings

This bites people constantly. A CSV with an empty field imports as an empty string '', not NULL. Those are different things: WHERE notes IS NULL won't find them, and COUNT(notes) counts them as present.

-- Turn empty strings into real NULLs.
UPDATE orders SET notes = NULL WHERE notes = '';

Do this right after import so downstream queries and your data tooling behave. (In pandas, df.replace("", pd.NA) before to_sql does the same thing.)

Deduplication

Re-running an import is the classic way to get duplicate rows. If you have a natural key, use it:

-- Remove duplicate order_id rows, keeping the lowest rowid.
DELETE FROM orders
WHERE rowid NOT IN (
    SELECT MIN(rowid) FROM orders GROUP BY order_id
);

Better: prevent duplicates at the schema level with a UNIQUE constraint on the natural key, and use INSERT OR IGNORE on subsequent imports.

Adding a primary key

If your table came from a tool that created it without a primary key, and you have a natural key column, you're in the same boat as adding any constraint to an existing SQLite table: ALTER TABLE can't add a primary key. You rebuild the table — create a new one with the key, copy the rows, drop the old, rename. (If you have a spare column and just want a surrogate rowid-based key, note that every SQLite table already has an implicit rowid you can use in queries without declaring anything.)

For the full rebuild pattern — and why it's a multi-step process — see the ALTER TABLE section in my SQLite foreign keys guide. Once the table is clean, if you want to browse and edit it without writing more SQL, opening the .db file in a proper viewer is the next step; my guide on how to open a .db file covers the options.

Troubleshooting

The failures I see over and over, and what they mean:

• "no such table: X" — .import requires the table to exist. Create it first. This is the #1 CLI error.
• First column named id or id — UTF-8 BOM. Re-save as UTF-8 without BOM, or import with encoding="utf-8-sig" in pandas.
• Row count is one higher than expected — you didn't skip the header. Add --skip 1 (CLI) or check "first row contains column names" (GUI).
• Everything landed in one column — wrong delimiter. Set .separator ";" (or the right one) before importing, or pick it in the wizard.
• Row count is way too high — embedded newlines inside quoted fields being split by a naive parser. Use the CLI, a GUI wizard, or pandas — all handle quoted newlines; split(",") does not.
• Accented characters look like café — wrong encoding. Import as UTF-8, or as the encoding the file was actually saved in (often Windows-1252).
• Leading zeros vanished (00501 → 501) — the column was treated as INTEGER. Declare it TEXT.
• SUM() returns 0 or nonsense — values are stored as TEXT and something isn't coercing. Declare numeric types and re-import.
• WHERE x IS NULL finds nothing — empty fields became ''. Run the empty-string-to-NULL update.
• Numbers show as 1.0 instead of 1 — pandas inferred float because of missing values. Set dtype={"id": "Int64"}.
• Error: FOREIGN KEY constraint failed — if you're importing into a table with FK constraints and foreign keys are enabled, a referenced parent row is missing. Insert the parents first, or import in dependency order.

When to use what

A short decision guide, in the order I'd actually decide:

• One file, clean, once → sqlite3 CLI. Three commands, done.
• One file, messy, and I want to see it before I commit → DB Browser for SQLite. The preview earns its keep.
• I already live in DBeaver, or the file needs serious column mapping → DBeaver's import wizard.
• I'll do this again next week → Python + pandas. Script it, version it, forget about it.
• I need the resulting table to be browsable and editable by a human → import with any of the above, then open the .db in a proper tool.

That last one is the part most tutorials stop short of, and it's usually the real goal. You rarely import a CSV because you want a CSV in a database — you do it because you want to query, filter, join, and edit the data. The sqlite3 CLI gets the data in, but it's a poor place to live. That's where a local-first desktop app like BaseVolt fits: point it at the .db file your import produced and you get a full dashboard — grid, gallery, kanban, and dashboard views, plus relation visualization — in a few seconds, with no schema changes. To be clear, BaseVolt is not a CSV importer; you bring the CSV in with one of the four methods above, and BaseVolt is how you work with the table afterwards. It's a local app for macOS (Apple Silicon) and Windows, it connects to PostgreSQL, MySQL, SQLite, and Cloudflare D1, and your data never leaves localhost — you can grab it from the download page or try the live demo at demo.basevolt.app. For the SQLite side specifically, there's more at /sqlite.

Bottom line

Importing a CSV into SQLite is a solved problem — there are four good ways to do it, and the right one depends on your situation, not on which tool is "best."

• The sqlite3 CLI is the fastest for clean one-offs, but it won't create a typed table for you and it won't skip your header unless you tell it to. Create the table first, then .mode csv and .import --skip 1.
• DB Browser and DBeaver give you a preview and real encoding/delimiter/type controls, which matters when the file is messy.
• pandas is what you want the moment the import has to happen more than once.
• Whatever you use, remember SQLite's type affinity: if you don't declare types, everything is TEXT. And empty CSV fields become empty strings, not NULL — fix that immediately.

The import itself is five minutes. The ten minutes you spend declaring types, normalizing dates, and converting empty strings to NULL is what makes the difference between a table you can query and a table that keeps surprising you.

Once the data's in, BaseVolt is how you actually use it — a local-first admin panel for your SQLite database, no account required, no data leaving your machine.

---

If you found this useful, I write about databases, local-first software, and the unglamorous parts of building dev tools. Find me on X.