How to convert CSV or JSON to SQLite (.db) and query it with SQL

How to convert CSV or JSON to SQLite

Upload the .csv or .json, click "Convert and download" and get a .db file with the same name. Inside it there is a single table named dados, with one column per header field and one row per record, every column stored as text. Open the .db in your SQLite program and run SELECT * FROM dados. Everything happens in your browser.

1. Open the "CSV/JSON to SQLite" tool and upload the .csv or .json file (the JSON must be a list of objects or of arrays).
2. Click "Convert and download". The first time, the browser downloads the SQLite engine (about 660 KB); your file never leaves your machine.
3. Open the .db in a SQLite program such as [DB Browser for SQLite](https://sqlitebrowser.org/), the sqlite3 command line or an extension in your editor.
4. Query it with SQL: SELECT * FROM dados WHERE city = 'Austin' ORDER BY name, for example.

What comes out the other side

• A single table named dados. That is the name you type in your queries.
• Every column is created as text. There is no type guessing: to sum or sort as a number, convert in the query, for example ORDER BY CAST(price AS REAL). SQLite does that without complaint.
• The first CSV row becomes the column names. An empty header becomes coluna_N (N is the position) and a repeated name gets the suffix _1, _2 and so on. The CSV separator (comma, semicolon or tab) is detected automatically.
• Nested JSON is expanded: an object inside another becomes columns named key.subkey; a list inside a record becomes several rows, with a key_index column numbering each item. That way a tree-shaped JSON becomes a queryable table instead of a blob of text stuck in one cell. In a list of lists, the first list becomes the column names.
• A JSON that is not a list (an object at the root, for instance) is refused with the notice "The JSON must be a list (array) of objects or rows. Check the content." Copy only the inner list to a new file.
• The generated database has no indexes or primary keys. Create them in your SQLite client if the dataset is large. The upload limit is 2 GB per file.

SQLite to CSV or JSON: the way back

Already have a SQLite database and want the data as text to import somewhere else, version it or inspect it? The "SQLite to CSV/JSON" tool accepts .db, .sqlite and .sqlite3 files. Pick CSV or JSON in the list next to the button and click "Convert and download". It exports only the first table in the database: if yours has several tables and the one you need is not first, export it from your SQLite client. A database with no table containing data returns the notice "The database has no tables with data". Null values come out as empty cells. With the CSV or JSON in hand, the other paths, such as opening it in Excel, are in how to convert CSV, JSON and Excel to each other.

What SQLite is and why it is worth it

SQLite is a database that fits in a single file. Unlike MySQL or PostgreSQL, it does not run as a separate server: tables, indexes and data live in a .db you copy, version and ship with the app. According to the project itself, it is the most widely deployed database in the world, embedded in browsers, phones and countless desktop programs, precisely because it is lightweight and needs no installation or configuration.

• Query with SQL: filter, group and count thousands of rows with one query instead of scrolling through a giant spreadsheet.
• Take it to an app: many apps and prototypes read straight from a .db; generating the database from a CSV is the shortcut to feed the app with real data.
• Performance: with an index created in your client, a search on a large dataset comes back far faster than scanning a whole CSV.
• Portability: a single file, easy to store, send and version.

Limits and when not to use it

• One table per file: each CSV or JSON produces a database with one table. To combine data from two files (JOIN), generate both databases and join them in your client with ATTACH DATABASE, or import the second table there.
• Everything is text: numeric comparisons and sorting by value need CAST in the query, and dates stay as the text that was in the file.
• SQLite is not a server: it is ideal for local queries, prototyping and analysis, not for many users writing at the same time.
• Column names with spaces or accents work, but need double quotes in SQL ("full name"). If you can, simplify the header before converting.

Everything happens in your browser: the file is processed on your own device and is never sent to any server. Your data never leaves your computer.

The first query to run is SELECT * FROM dados LIMIT 20: you check the column names and see whether nested JSON was expanded the way you expected.