Turn a CSV or Spreadsheet Into a SQLite Database You Can Query

There is a moment in the life of most spreadsheets where it stops being a spreadsheet problem. You want the total per region, but only for orders after March, excluding the test accounts, joined against a second sheet that maps customer IDs to segments. In SQL that is four lines. In a spreadsheet it is a nested formula you will not be able to read next week.

Converting the file to SQLite is the shortest path out. You get a real query engine, real joins and real aggregation, in a single file you can email. And unlike standing up Postgres for an afternoon's analysis, there is nothing to install and nothing to run.

Why SQLite rather than a database server

SQLite is a full SQL engine that lives inside a single file. There is no server, no port, no user account and no configuration. It is also not a toy: it has transactions, indexes, views, window functions and foreign keys, and it is almost certainly the most deployed database in the world, shipping inside every phone, every browser and most desktop applications.

For turning a data file into answers, that combination is close to ideal. The file is the database, so when you are done you can delete it or keep it, and either way there is nothing left running.

The part that actually matters: column types

Everything else about a CSV-to-database conversion is mechanical. Type inference is where files get quietly damaged, and it is worth understanding before you trust any tool with your data.

Consider a column of American postcodes:

• 90210
• 00501
• 10001

Decide the type cell by cell and you get a disaster: 90210 looks like a number, so it is stored as one. 00501 also looks like a number, so it is stored as 501 and the postcode is destroyed. Now the column is half integers and half text, sorting is nonsense, and the row for Agawam, Massachusetts points nowhere.

The correct rule is to decide the type from the whole column before converting any cell in it. A column is numeric only if every filled value in it is numeric. One 00501 and the entire column stays text, leading zero intact.

Here is that exact CSV run through the converter:

name,zip,price,qty
Alice,90210,19.99,5
Bob,00501,5,12
Cara,10001,3.50,7

and the schema that comes out the other side:

CREATE TABLE "my_table" (
  "name"  TEXT,
  "zip"   TEXT,
  "price" NUMERIC,
  "qty"   INTEGER
)

zip is TEXT, so 00501 is still 00501. Three other decisions are visible in that schema too:

• qty is INTEGER because every value in it is a whole number.
• price is NUMERIC, not REAL, because the column mixes 19.99 with a bare 5. NUMERIC is the one SQLite affinity that keeps 5 an integer and 19.99 a real, rather than rewriting your whole numbers as 5.0.
• Very long digit strings stay TEXT. A 25-digit account number is not a quantity, and storing it as a number would round it. Anything wider than a signed 64-bit integer keeps its text form rather than silently losing its last digits.

Ragged rows are padded rather than rejected, since real CSVs are full of them, and blank spacer lines are skipped instead of becoming rows of empty strings.

A workbook becomes several tables

Spreadsheets map onto databases better than onto anything else, and the conversion takes advantage of that: every sheet with data in it becomes its own table, named after the sheet.

A workbook with a Sales tab and a Staff tab produces:

sqlite> .tables
Sales  Staff

sqlite> SELECT * FROM Sales;
EMEA|120
APAC|80

which means the join you wanted between two tabs is now an actual SQL join. This is worth contrasting with converting the same workbook to JSON, which takes only the active sheet — reasonable for JSON, where there is no natural place to put five sheets, and clearly wrong for a database.

JSON input works the same way from the other direction: an array of objects becomes a table with one column per key, and keys missing from some records simply come out NULL.

Querying it

You almost certainly already have SQLite. Python ships with it, so no install is required:

python3 -c "
import sqlite3
db = sqlite3.connect('data.sqlite')
for row in db.execute('''
    SELECT zip, SUM(price * qty) AS revenue
    FROM my_table
    GROUP BY zip
    ORDER BY revenue DESC
'''):
    print(row)
"

If you would rather click than type, DB Browser for SQLite is free, open source and runs on Windows, macOS and Linux. It opens the file, shows the tables, and gives you a query pane.

And when the analysis is done and you need to hand the result back to someone in a spreadsheet, the trip is reversible: SQLite to CSV exports the data back out, and SQLite to SQL gives you a dump of CREATE TABLE and INSERT statements suitable for loading into Postgres or MySQL.

Limits worth knowing

The conversion caps at 500,000 rows and 500 columns. JSON input is limited to 50 MB of text, and spreadsheets to 25 MB of XLSX — which is a lot of rows, since XLSX is a zipped format and compresses tabular data heavily.

Beyond those sizes you are past the point where a browser upload is the right tool, and the answer is sqlite3's own .import command locally.

On privacy, since this is usually real data

Files converted here are processed entirely in memory, are never written to a disk, and are discarded the moment your download is sent. That matters more for this conversion than for most: nobody converts a CSV to a database out of curiosity. It is customer lists, transaction exports, HR data and survey responses — exactly the files where "where did the upload go afterwards" is a question worth having a real answer to.