Importing CSV data into SQLite with .import

I usually use my sqlite-utils insert blah.db tablename file.csv --csv command to import CSV data into SQLite, but for large CSV files (like a 750MB one) this can take quite a long time - over half an hour in this case.

SQLite can import CSV data directly, and when I tried it on this file it completed in 45 seconds!

Here's how I scripted that from the command-line:

sqlite3 data.db <<EOS
.mode csv
.import school.csv schools
.import state.csv states
EOS

The .mode csv ensures the imported files are treated as CSV, then the .import filename.csv tablename lines import the data.

Every column was imported as TEXT - so I used sqlite-utils transform to transform some of the column types afterwards:

sqlite-utils transform data.db schools \
    --type school_nces_id integer \
    --type year integer

Related

• sqlite One-liner for running queries against CSV files with SQLite - 2022-06-20
• python Running PyPy on macOS using Homebrew - 2022-09-14
• amplitude Exporting Amplitude events to SQLite - 2021-06-06
• googlecloud Analyzing Google Cloud spend with Datasette - 2022-08-16
• sqlite Saving an in-memory SQLite database to a file in Python - 2023-04-08
• zsh Passing command arguments using heredoc syntax - 2022-07-07
• twitter Loading Twitter Birdwatch into SQLite for analysis with Datasette - 2022-09-03
• sqlite Copy tables between SQLite databases - 2023-04-03
• sqlite Calculating the size of a SQLite database file using SQL - 2023-08-21
• sqlite SQLite VACUUM: database or disk is full - 2022-08-29

Created 2021-07-13T22:01:05-07:00 · Edit