Skip to content

Importing and Exporting Data

SQLite Expert can move data in and out of your databases in a wide range of formats — spreadsheets, text files, JSON, XML, HTML, SQL scripts, other SQLite databases, and ADO data sources.

There are two main ways to do it:

  • Quick grid export — right-click any data grid (a table's Data tab or the result of a query) and export what you see to Excel, ODS, a text file, XML, JSON, HTML, or SQL.
  • The Data Transfer Wizard — a guided, option-rich tool for transferring whole tables and views between SQLite databases, SQL scripts, and ADO data sources.

Which grid an export writes out

The export commands act on the current grid — the one you last worked in. Right-clicking a grid makes it current, so the grid's own menu always exports what you clicked.

A few tabs hold more than one grid: Database has three, Extensions has three, and Compare has two. There the current grid's heading is shown in bold, and the export dialog names it again on its read-only Source row — so the Import/Export menu cannot quietly write out the wrong one. To export a different grid, click inside it first.

Where a grid has no heading of its own — a table's Data tab, the result of a query — Source names the table the rows came from, or reads Query result where there is no single table behind them. The two exports with no options dialog, text file and SQL, say the same thing in their title bar instead.

The file an export offers to write is named the same way: after the table where there is one, and after the grid where there is not, so exporting the Database tab's three grids gives you three differently named files rather than three blank prompts. A result with neither a table nor a heading — a join, a SELECT of expressions — has nothing to be named after, and the dialog offers the last file you exported to instead.

Note

An export writes what each cell actually holds, not what its column's declared type says it should. SQLite does not enforce declared types, so a column declared INTEGER can hold the text seven — a value the grid shades to point out (see Data types). Such a value is written out as the text it is, and an exported SQL script therefore reproduces the storage classes of the table it came from rather than a converted approximation of them.

A blob is the one value with no text form. Where one turns up in a column read as text, each format writes it the way it writes any blob — hex in CSV, base64 in XML and JSON, X'…' in an SQL script, a marker in the other two — rather than leaving the cell empty.

JSONB is the exception. A JSONB value is a blob, but unlike other blobs it does have a text form — the JSON document it encodes — and SQLite treats the byte layout of a JSONB blob as its own business, free to change between releases. The JSON is therefore both the readable form and the durable one.

A JSON export writes the document itself, as part of the file rather than as base64 of the bytes that encode it. A document that is a bare string keeps its base64, because written as a JSON string it could not be told from one — which leaves a rule you can apply while reading: in a blob column, a JSON string is base64 and anything else is the document.

A text file (CSV or tab-separated) writes the JSON wherever it was going to write a marker instead of the bytes — (BLOB) or [BLOB_DATA]. Nothing is lost by that: a marker carried no data either way. CSV with Include BLOB data on still writes the bytes, and an SQL script still writes X'…', because there the export is a copy of the value.

A spreadsheet names the blob unless you ask for it. Excel and ODS start out exporting the grid as you see it laid out, and that route can only be told what a cell says — so a blob reads as (blob). Tick Include BLOB data and the export follows the data instead, writing each blob as x'…', which the spreadsheet import reads back as a blob. See Exporting data to Excel for what that costs.

The formatted routes for HTML and XML name the blob for the same reason, and have no such option. HTML and XML each also offer a data route that follows the dataset, and there a JSONB column reads as its JSON like everywhere else.

Which formats come back unchanged

Exporting and re-importing a table does not always give you the table you started with, and which parts survive depends on the format rather than on the export. Every format below carries text, numbers, and the storage class of each value; the columns say what else it can hold on to.

Format Blobs Nulls Empty text Mixed classes in one column
SQL script ✅ ✅ ✅ ✅
Text file (CSV) ✅ with Include BLOB data ✅ ✅ bytes and nulls only
Text file (tab-separated) [BLOB_DATA] become empty text ✅ —
XML — Data ✅ ✅ ✅ —
JSON ✅ ✅ ✅ —
Excel, ODS ✅ with Include BLOB data ✅ become nulls —
HTML shown as base64 become empty text ✅ —

An SQL script is the only exact copy, and that is what it is for: it writes a literal per value, so it is the only format that can say "this cell is an integer and the one below it is a blob" about a single column.

Three notes on the rest:

  • Mixed storage classes in one column only arise in a column declared BLOB or declared nothing at all, because those are the columns SQLite converts nothing in (see Data types). Every format except a SQL script states one type per column and has nowhere to record that the rows disagree, so the values come back under whichever single type the file named.
  • A tab-separated file quotes nothing, so a value holding a tab or a line break is written as [INVALID_DATA], and a null and an empty string are written as the same nothing between two tabs. Use CSV if either matters.
  • A spreadsheet cell that is empty is a null, because a sheet has no way to tell an empty cell from a cell holding an empty string, and a cell nobody filled in is the ordinary case. HTML makes the opposite choice, for the same reason in reverse.

Tip

Tools → Options → Data → Open the file after exporting shows you each file as it is written. It is off to begin with: an export is a request to write a file, not to open one.

A file type with nothing registered against it — .json and .sql often have nothing — opens its folder with the file selected instead of putting Windows' "How do you want to open this file?" picker in front of you.

How an imported table declares its columns

An import that creates a table has to write a type for each of its columns. Three imports work those types out themselves, and each offers a Column types box to say how they are written:

The choices are the same in all three: Descriptive types with no sizes (the default), STRICT types, regular table, STRICT table, and Descriptive types with sizes (legacy). What each one writes, and why a size matters even though SQLite ignores it, is under How the columns are declared.

Importing from another SQLite database or from a script offers no such choice: the columns are already declared there, and are created exactly as they were. To rewrite a table's types afterwards, see Converting tables to STRICT.

In this section