csvs-to-sqlite: turning CSV files into a queryable SQLite database

One table per file, then optionally a proper relational shape

csvs-to-sqlite converts CSV files into a SQLite database, and the intended workflow is to browse or publish that database with Datasette. The basic use is one file, one table, with the table named after the file:

csvs-to-sqlite myfile.csv mydatabase.db

That produces a database called mydatabase.db containing a single table, myfile, holding the CSV content. The table name comes from the filename, which is the first thing to check when two files share a name or when a filename is not a valid identifier. What separates this tool from a plain import is the option to refactor the imported shape. A CSV of election results with a county, precinct, office, district, party and candidate column repeated on every row is the example in the documentation, and the tool can pull those columns out into their own tables and leave integer foreign keys behind.

Bulk import shapes: several files, wildcards and directories

The path arguments accept more than a single file, which is what makes the tool usable on a folder of exports. Several files at once produce one table per file:

csvs-to-sqlite one.csv two.csv bundle.db

Because the arguments are expanded by your shell, wildcards work as well, which is how you sweep up a downloads folder:

csvs-to-sqlite ~/Downloads/*.csv my-downloads.db

Directories are also accepted, and in that case the script searches them recursively for CSV files and creates a table for each one it finds:

csvs-to-sqlite ~/path/to/directory all-my-csvs.db

The recursive behaviour is the one to watch, since a directory that happens to contain a large tree of exports will produce a table per file without further prompting, and the table names are derived from the file paths. If you want to be explicit about what lands in the database, listing the files is safer than pointing at a directory.

Extracting a column into a lookup table

The column extraction option is the feature the tool exists for. The flag is --extract-column, with the shortname -c, and it takes a specification in the form column_name:optional_table_name:optional_table_value_column_name. With a bare column name, the column is replaced with integer foreign key references to a new table of the same name:

CREATE TABLE "office" (
    "id" INTEGER PRIMARY KEY,
    "value" TEXT
);

With all three parts, you choose the table name and the name of the column holding the strings:

CREATE TABLE "Precinct" (
    "id" INTEGER PRIMARY KEY,
    "name" TEXT
);

The original table then keeps integer columns with foreign keys pointing at the derived tables, so the values are stored once and the wide table becomes narrow. A full invocation on the election example looks like this:

csvs-to-sqlite openelections-data-*/*.csv \
    -c county:County:name \
    -c precinct:Precinct:name \
    -c office -c district -c party -c candidate \
    openelections.db

The rest of the option surface, from quoting to full text search

The help output lists a wider set of switches than the documentation highlights, and several of them change the shape of the result rather than just the import behaviour. -q controls field quoting per csv.QUOTE_* constant, with QUOTE_MINIMAL, QUOTE_ALL, QUOTE_NONNUMERIC and QUOTE_NONE available, and --skip-errors drops lines with too many fields instead of stopping the import, which is the difference between a partial database and none. --replace-tables overwrites tables that already exist, and -t sets the table name explicitly instead of taking it from the filename. -d, -dt and -df parse columns into ISO formatted dates and datetimes, with -df taking custom format strings to try. -pk sets the primary key, -f populates a full text index from one or more columns, and -i adds an index on a column or a compound index on several. There are also --shape for a custom column type mapping in the form csvcol:dbcol(TYPE), --filename-column to record the source filename in each row, and --fixed-column to populate a column with a constant.

Delimiters and quoting for files that are not really CSV

Tab separated files are handled by the separator option, and the README is explicit that a tab character needs shell escaping:

csvs-to-sqlite my-file.tsv my-file.db -s $'\t'

The quoting option matters for the same reason. Files exported from spreadsheets often quote fields that do not need quoting, or quote numbers as if they were text, and a mismatch between the file and the assumed quoting rule will either insert stray quote characters into values or fail to parse a field. Setting -q to the constant that matches how the file was written is the fix. Installation is a single pip command, and current versions require Python 3:

pip install csvs-to-sqlite

The last version that supports Python 2 is 0.9.2, installable as pip install csvs-to-sqlite==0.9.2, which is the pin to reach for if you are still on an old interpreter and cannot move.

The author recommends sqlite-utils instead

The most consequential line in the README is a note, not a feature list: the tool is infrequently maintained, and the author suggests using sqlite-utils for importing CSV and TSV data to SQLite for most cases. That is the author of the tool pointing at a sibling tool in the same ecosystem, and it changes how you should read the rest of the page. The difference in approach is that csvs-to-sqlite is a single purpose converter with a fixed opinion about what the output should look like, a table per file with optional extracted lookup tables, while sqlite-utils is a general purpose database manipulation tool where the import is one command among many, and the table design, indexes and schema are things you build up over further commands. If your job is one import and then browse with Datasette, csvs-to-sqlite is the shorter path. If you are loading data into a database that will keep changing, the general tool is the one the author would point you to.

Release history, dependencies and licence

The version history explains the infrequent maintenance label. The releases are 1.2 from 2020-11-03, 1.3 from 2021-11-18 and 1.3.1 from 2025-04-10, and the last push to the main branch was on 2026-07-15. So the code is being touched, but published releases are years apart, and the setup.py metadata still lists 1.3.1 as the version. The dependency list is short and tells you how the import is done: click for the command line, pandas for reading the files, dateparser for the date parsing options, py-lru-cache and six for compatibility. The package registers a console script named csvs-to-sqlite and the source lives in csvs_to_sqlite/ with tests in tests/. The licence field on the repository says Apache-2.0, and the CHANGELOG.md at the top level plus the release page linked from the README are where the history of each version is recorded.

Editorial conclusion

Adopt csvs-to-sqlite if you already have a set of CSV files and want a SQLite database that Datasette can browse or publish, and if the column extraction feature saves you writing the same normalisation code again. Do not adopt it for a new project without reading its own note, because the author calls the tool infrequently maintained and points at sqlite-utils for CSV and TSV import in most cases. Verify first that the version you install supports the Python you run, since current releases need Python 3 and the last Python 2 compatible version is 0.9.2, and check whether the table and column options you want are present in the release you pin rather than in the ones you remember.

Frequently asked questions

Official sources

1. Issues
2. License: Apache-2.0
3. README
4. Releases
5. simonw/csvs-to-sqlite on GitHub