Loading Many CSV Files Into One SQLite Database in Bash

A year of monthly exports arrives as twelve CSV files with the same columns, and the job is to get them into one table you can query. The tempting shortcut is cat, and it has a trap in it. The better route is a short loop that imports each file on its own and records where each row came from, so that a bad month can be found, counted and removed. This article gives that loop, runs it on six files, and shows what happens when you run it twice. The single file case is in the SQLite import guide. The .import options used here are described in the command line shell documentation, and the transaction behavior in SQLite's transaction reference.

The files and the target table

Each file looks like this, one per month:

sale_id,sku,qty,price
2025-01-0001,B-007,3,24.29
2025-01-0002,A-100,2,49.46
2025-01-0003,A-100,6,35.38

The target is one table with a primary key, a source column that says which file a row came from, and types for the numbers:

CREATE TABLE sales (
  source TEXT, sale_id TEXT PRIMARY KEY, sku TEXT, qty INTEGER, price REAL
);

The loop

For each file, import into a temporary table, where SQLite creates the columns from the header, and then insert from it into sales with the file name added. The .import has no --skip, since a table that does not exist takes its names from the first row:

#!/usr/bin/env bash
set -euo pipefail
db=sales.db
sqlite3 "$db" "CREATE TABLE IF NOT EXISTS sales (source TEXT, sale_id TEXT PRIMARY KEY, sku TEXT, qty INTEGER, price REAL)"
for f in many/sales-*.csv; do
  sqlite3 "$db" <<SQL
.bail on
DROP TABLE IF EXISTS temp.incoming;
.import --csv --schema temp "$f" incoming
BEGIN;
INSERT INTO sales SELECT '$(basename "$f")', sale_id, sku, qty, price FROM temp.incoming;
COMMIT;
SQL
  echo "$(basename "$f"): $(qsv count "$f") records in the file, $(sqlite3 "$db" "SELECT count(*) FROM sales WHERE source = '$(basename "$f")'") in the table"
done

set -euo pipefail makes bash stop at an error, and .bail on does the same inside sqlite3 for SQL errors. The temporary table is dropped at the top of each iteration, so each file starts clean, and the insert runs in a transaction so that a failure in one file leaves nothing from it in the table. After each file the script prints the count in the file and the count in the table, which is the check that matters.

./load_many.sh

sales-2025-01.csv: 500 records in the file, 500 in the table
sales-2025-02.csv: 500 records in the file, 500 in the table
sales-2025-03.csv: 500 records in the file, 500 in the table
sales-2025-04.csv: 500 records in the file, 500 in the table
sales-2025-05.csv: 500 records in the file, 500 in the table
sales-2025-06.csv: 500 records in the file, 500 in the table

All six lines show 500 and 500. A mismatch there would be the first sign of a file with an unterminated quote or a short record, since SQLite would load it with a warning and the strict qsv count would say a different number.

Looking at what was loaded

Because each row carries its source, a per-file summary is one query:

sqlite3 -header -column sales.db "SELECT source, count(*) AS rows, round(sum(qty * price), 2) AS revenue FROM sales GROUP BY source ORDER BY source" "SELECT count(*) AS total_rows FROM sales"

     source        rows  revenue
-----------------  ----  --------
sales-2025-01.csv   500  72052.85
sales-2025-02.csv   500  75841.56
sales-2025-03.csv   500  73365.61
sales-2025-04.csv   500  72363.55
sales-2025-05.csv   500  77600.88
sales-2025-06.csv   500   76443.4
total_rows
----------
      3000

Six files, 500 rows each, 3,000 in total, and a revenue figure per month. If a month looks wrong, say a revenue that is half of the others, you can compare its row count and its figure with the file's own total and decide whether the file or the load is at fault. And you can remove one month with a single DELETE FROM sales WHERE source = 'sales-2025-03.csv' and load a corrected file in its place.

Running it twice

The script is safe to rerun because of the primary key. The second run imports the first file into the temporary table, tries the insert, and hits the duplicate:

./load_many.sh 2>&1 | head -4; echo "exit status ${PIPESTATUS[0]}"

Error near line 5: UNIQUE constraint failed: sales.sale_id
exit status 1

Error near line 5: UNIQUE constraint failed: sales.sale_id and an exit status of 1. Nothing was loaded twice. That is the behavior I want for a job that might run again after a half finished attempt, since the alternative is a table in which January is there twice and every total is wrong. If you want reruns to skip files that are already loaded, check the source column first (SELECT 1 FROM sales WHERE source = ? LIMIT 1) and continue.

A key that identifies a row is what makes this work. If your data has no natural key, add the file name and the line number as a composite key. SQLite can number the rows of a temporary table with rowid, which is the order of import.

Why not cat

The shortest way to combine files is to strip the headers and concatenate:

rm -f cat.db; (head -1 many/sales-2025-01.csv; for f in many/*.csv; do tail -n +2 "$f"; done) > /tmp/all-sales.csv
sqlite3 cat.db ".import --csv /tmp/all-sales.csv sales" "SELECT count(*) FROM sales"

3000

That gave 3,000 rows, the right number, and it is the version to avoid, for two reasons. It loses the source of each row. And it depends on every file ending with a newline. A file whose last line lacks one is joined to the first line of the next:

printf 'a,b\n1,2' > /tmp/x1.csv; printf 'a,b\n3,4\n' > /tmp/x2.csv
(head -1 /tmp/x1.csv; for f in /tmp/x1.csv /tmp/x2.csv; do tail -n +2 "$f"; done) | cat -A

a,b$
1,23,4$

The last line of the output is 1,23,4$, with cat -A showing the end of line. The records 1,2 and 3,4 have become one record, 1,23,4, which is three fields where two belong. The .import command would report it as a short or long record, and the table would have one row too few. Importing each file separately has no such failure, since each file ends where its file ends.

Variations

Files with different columns need different handling: import each into its own table, as in the folder article, or select the columns you want by name in the INSERT ... SELECT, which also tolerates a changed column order. Files in different encodings need converting first, because .import takes bytes as they come. A file with a different header, a title row on top, needs --skip and a table you create. And a very large number of files is a case for running the loop in parallel only if the target is separate databases, since SQLite allows one writer at a time.

If the files are gzipped, replace the path with '|gunzip -c file.csv.gz' in the .import, as in the .import article. To run the load from cron, put set -euo pipefail at the top, log the printed counts, and alert on any difference between the file count and the table count.

How to tell it worked

For each file the count in the table equals qsv count. The total equals the sum of the counts. A sum of one numeric column over the whole table equals the sum you get by summing the files with another tool, for example DuckDB reading many/*.csv with a glob. And SELECT typeof(qty), count(*) FROM sales GROUP BY 1 shows only integer.

When a loop is not the right shape

If the files are a stream, arriving every few minutes, a loop over a folder is the wrong shape and a small program that watches the folder and records which files it has handled is better. If the combined data is large and you query it often, a columnar format like Parquet written once by DuckDB is faster than any number of SQLite rows, as the Parquet comparison shows. And if you only want to ask questions of the files, you do not need a database at all: DuckDB's read_csv('many/*.csv') unions them with the file name as a column in one statement, as the DuckDB guide shows.