Turning a Folder of CSV Files Into a SQLite Database
One table per CSV file, with safe names and types inferred from the whole file. A short Python script that keeps ZIP codes as text and refuses ragged files.
A folder of CSV files, a handful of different layouts and a request to "put these in a database so I can query them". The .import command can do each file in a loop, and the result is a database of untyped text with table names that need quoting. This article builds the loop that a careful person would write: tables with usable names, columns with real types, and a refusal to load a file that is not rectangular. It is a short Python script, run on five files with deliberately awkward names, and the output is a database you can query without thinking about how it got there. Files that share one layout are better served by the single table loop in loading many CSV files into one database. The script uses only the standard library: the csv module to read and the sqlite3 module to write.
The folder
Five files, with names chosen to break things: a space in one, a leading digit in another, and a header with the same name twice in the last:
2025 targets.csv
Product List.csv
customers.csv
sales-2025-01.csv
sales-2025-02.csv
Region,2025 Target,RegionThe last line of that output is the header of 2025 targets.csv, Region,2025 Target,Region, which is legal CSV and awkward as column names.
The plain loop and what it gives you
The shortest version is a shell loop that imports each file under a cleaned name:
rm -f plain.db; for f in folder/*.csv; do t=$(basename "$f" .csv | tr -c 'a-zA-Z0-9\n' '_' | tr 'A-Z' 'a-z'); sqlite3 plain.db ".import --csv \"$f\" $t"; done
sqlite3 plain.db ".tables"Columns renamed during .import folder/2025 targets.csv due to duplicates:
"Region" to "Region_1",
"Region" to "Region_3"
2025_targets customers product_list sales_2025_01 sales_2025_02It worked, with one notice: Columns renamed during .import ... due to duplicates: "Region" to "Region_1", "Region" to "Region_3". The tables are 2025_targets, customers, product_list, sales_2025_01 and sales_2025_02. Notice 2025_targets, a name that begins with a digit, which needs quoting in every query. Every column is untyped text, so prices cannot be compared as numbers. The tr in the loop replaces anything that is not a letter or digit with an underscore, which is a reasonable start and does not deal with a leading digit, collisions between two names that clean to the same thing, or a file that is not rectangular.
The script
The script does the same job with a few more rules. It reads each file with Python's csv module, so quoting is handled, and it checks that every record has as many fields as the header before it creates anything:
"""Load every CSV in a folder into one SQLite database, one table per file, with column types inferred from the whole file.
An integer column stays TEXT when any value has a leading zero or a plus sign, so ZIP codes and part numbers survive.
Nothing is skipped silently: a record with the wrong number of fields stops the file and names the line.
"""
import csv
import re
import sqlite3
import sys
from pathlib import Path
def table_name(path: Path) -> str:
name = re.sub(r"[^0-9a-zA-Z]+", "_", path.stem).strip("_").lower()
return name if not name[0].isdigit() else "t_" + name
def column_name(raw: str, taken: set) -> str:
base = re.sub(r"[^0-9a-zA-Z]+", "_", raw.strip()).strip("_").lower() or "col"
base = "c_" + base if base[0].isdigit() else base
name, n = base, 2
while name in taken:
name, n = f"{base}_{n}", n + 1
taken.add(name)
return name
def kind(values: list) -> str:
filled = [v for v in values if v != ""]
if filled and all(re.fullmatch(r"-?(0|[1-9][0-9]*)", v) for v in filled):
return "INTEGER"
if filled and all(re.fullmatch(r"-?(0|[1-9][0-9]*)?\.[0-9]+|-?(0|[1-9][0-9]*)", v) for v in filled):
return "REAL"
return "TEXT"
def load(db: sqlite3.Connection, path: Path) -> None:
with open(path, newline="", encoding="utf-8-sig") as f:
rows = list(csv.reader(f))
header, body = rows[0], rows[1:]
for n, row in enumerate(body, start=2):
if len(row) != len(header):
sys.exit(f"{path.name}: record {n} has {len(row)} fields, the header has {len(header)}")
taken: set = set()
cols = [column_name(h, taken) for h in header]
types = [kind([r[i] for r in body]) for i in range(len(header))]
table = table_name(path)
db.execute(f'DROP TABLE IF EXISTS "{table}"')
db.execute(f'CREATE TABLE "{table}" ({", ".join(f"{c} {t}" for c, t in zip(cols, types))})')
db.executemany(f'INSERT INTO "{table}" VALUES ({", ".join("?" * len(cols))})',
[[(None if v == "" else v) for v in r] for r in body])
print(f"{path.name:26} -> {table:22} {len(body):6} rows {', '.join(f'{c} {t}' for c, t in zip(cols, types))}")
if __name__ == "__main__":
folder, database = Path(sys.argv[1]), sys.argv[2]
db = sqlite3.connect(database)
with db:
for p in sorted(folder.glob("*.csv")):
load(db, p)The rules are in three small functions. table_name lowercases the file stem, replaces runs of characters other than letters and digits with an underscore, and prefixes t_ if the result starts with a digit. column_name does the same for headers, and numbers duplicates, so the second Region becomes region_2. kind looks at every value in a column, not a sample: it picks INTEGER if all non-empty values are plain integers without a leading zero, REAL if all are numbers, and TEXT otherwise. An empty field becomes NULL. The whole load runs inside one transaction, with db:, so if any file fails, nothing is committed.
rm -f folder.db; python ../../csv_folder_to_sqlite.py folder folder.db2025 targets.csv -> t_2025_targets 2 rows region TEXT, c_2025_target REAL, region_2 TEXT
Product List.csv -> product_list 4 rows sku TEXT, name TEXT, price REAL, stock INTEGER, zip TEXT
customers.csv -> customers 4 rows id INTEGER, name TEXT, email TEXT, signup_date TEXT, balance REAL
sales-2025-01.csv -> sales_2025_01 500 rows sale_id TEXT, sku TEXT, qty INTEGER, price REAL
sales-2025-02.csv -> sales_2025_02 500 rows sale_id TEXT, sku TEXT, qty INTEGER, price REALEach line shows the file, the table it became, the row count and the columns with their types. Product List.csv is product_list with price REAL, stock INTEGER and zip TEXT. 2025 targets.csv is t_2025_targets with region, c_2025_target and region_2, and the target column is REAL because one value is 850.5. customers.csv has balance REAL and signup_date TEXT, which is right for dates in ISO form.
Checking the result
The database should hold exactly those tables, and the types should be what was reported:
sqlite3 folder.db ".tables" "SELECT name FROM sqlite_master WHERE type = 'table' ORDER BY name"customers product_list sales_2025_01 sales_2025_02 t_2025_targets
customers
product_list
sales_2025_01
sales_2025_02
t_2025_targetssqlite3 -header -column folder.db "SELECT sku, typeof(price) AS price, typeof(stock) AS stock, typeof(zip) AS zip, zip FROM product_list LIMIT 2" "SELECT region, c_2025_target, typeof(c_2025_target) FROM t_2025_targets" sku price stock zip zip
----- ----- ------- ---- -----
A-100 real integer text 00501
A-101 real integer text 02134
region c_2025_target typeof(c_2025_target)
------ ------------- ---------------------
EMEA 1000.0 real
APAC 850.5 realThe ZIP codes are text and still read 00501 and 02134, because a leading zero kept the column from being classified as an integer. That is the one rule in the inference that I care about most, and the one a naive inferrer gets wrong: a column of digits that includes 00501 is not a column of numbers. Prices are real, stock is integer, and the target values are real, with 1000 stored as 1000.0 because the column holds 850.5 too.
A ragged file stops everything
The check before loading is the part that .import does not have. I added a file with a short record to a folder containing a good file:
mkdir -p /tmp/badfolder; cp folder/customers.csv /tmp/badfolder/; printf 'a,b\n1,2\n3\n' > /tmp/badfolder/broken.csv
python ../../csv_folder_to_sqlite.py /tmp/badfolder /tmp/bad.db; echo "exit status $?"broken.csv: record 3 has 1 fields, the header has 2
exit status 1broken.csv: record 3 has 1 fields, the header has 2, and the exit status is 1. The script stopped before creating a table for it and, because the work is inside a transaction, rolled back the good file too. A load that reports the file and record number and loads nothing is easier to act on than one that loads fourteen files and warns about the fifteenth. If you prefer partial loads, move the with db: inside load and catch the error per file.
Where inference goes wrong
Inference over the whole file is better than sampling, since a bad value at row 90,000 still sets the type. It is still a guess about the future. A column of ids that are integers this month may contain A-100 next month, and the second file will load into a table whose column is INTEGER with a text value in it; SQLite accepts that in a non-strict table, silently. A column where every value is empty gets TEXT, which is a fair default. A column of dates in a US format is TEXT, since there is nothing in SQLite to infer a date from. And 1e5 or 1,000 in a numeric column makes it text, because my pattern for numbers is plain.
For tables you will keep, take the generated schema as a first draft: print it with .schema, fix the types by hand and create the tables yourself, as in setting column types when SQLite imports a CSV. Add STRICT once you are sure of the types, since it turns a mismatch into a visible failure.
How to tell it worked
For each table, the count in the database equals the count in the file, and a sum of one numeric column equals the sum from another tool. Print the tables and types and compare them with what you expected, because a column that came out TEXT when you expected REAL means a stray value, and finding the offender is a matter of listing the distinct values with SELECT DISTINCT col FROM t or filtering with WHERE col NOT GLOB '[0-9]*'. The validation guide has more checks.
When inference gets it wrong
If the folder is a feed that continues, schema drift is the problem and not typing, and detecting a changed header between deliveries is the check to add; see detecting schema drift between two CSV files. If the files are large, Python reading every value into memory to infer types is the wrong tool: use DuckDB, which samples to infer and streams to load, or read the file twice, once to infer and once to insert. And if you only want to query the files and then throw the database away, duckdb -c "SELECT * FROM read_csv('folder/*.csv')" is shorter than any of this, and handles the types for you.
