Importing data from a text file¶

Import a delimited text file (CSV, TSV, or a custom delimiter) into a new or existing table.

To import a text file:

Choose Import/Export → Import text file from the main menu.
In the Import text file dialog, set the options described below.
Select the source text file.
Check the delimiter. SQLite Expert tries to detect it automatically (comma, semicolon, or tab) — correct it if the guess is wrong.
Choose the destination:
- Existing table — pick a table from the list to add the rows to.
- New table — enter a name, and SQLite Expert creates the table for you.
Tick Delete existing data to clear the table before importing.
For a new table, tick Auto detect field types to let SQLite Expert guess each column's type from the data. This makes two passes over the file, so it is slower; if you leave it off, every column is imported as TEXT.
For a new table, choose under Column types how those types should be written into the schema. See How the columns are declared.
Click Start.

Tip

Auto-detecting field types gives you numbers and dates as real types instead of plain text, which is usually what you want — leave it on unless the file is very large and import speed matters.

How the columns are declared¶

Column types, under Options, decides how the types of a new table are written into its schema. It has no bearing on what is imported — only on what the CREATE TABLE says. It is greyed out, along with Auto detect field types, when the destination is a table that exists already: the columns of such a table are not ours to choose.

Descriptive types with no sizes — the default. Each column is declared with the name that describes it and nothing else: TEXT, INTEGER, REAL, BOOLEAN, or BLOB for a column of hex blob literals such as x'0A1B'.

STRICT types, regular table — only the type names a STRICT table allows: INTEGER, REAL, TEXT and BLOB. The table is created without the STRICT keyword, so SQLite still applies its ordinary affinity rules and refuses nothing. This is the shape to pick for a table you may want to convert to STRICT later, or one that may be appended to by something less careful than this program.

STRICT table — the same names, and the table is created STRICT. SQLite itself then refuses any value that does not fit the column's type, rather than converting it or storing it as it stands.

Descriptive types with sizes (legacy) — the descriptive names again, with the longest value found in the file in brackets after them: TEXT(37). A floating-point column is FLOAT here rather than REAL, as earlier versions wrote it; SQLite treats the two names alike. SQLite ignores the number entirely — it is not a limit there, and a column declared TEXT(37) stores a value of any length. SQLite Expert does not ignore it: it reads the number back as the column's length, and a cell editor in that column will not accept more characters than the declaration allows. The widest row of one file therefore becomes a typing limit for every row afterwards. This is what earlier versions always did, and it is here only for schemas that were built that way.

Note

The types are worked out from every row in the file, not a sample of it, so a STRICT table created by an import can hold everything that import puts in it. A later import into the same table, or a value typed into the grid afterwards, is a different matter: STRICT will refuse anything that does not fit.

Note

Two things follow from dropping the sizes, both of them ordinary. A column declared TEXT with no size is read as a memo rather than as a string — see Data types — and under either STRICT style a column of true and false is declared INTEGER, so it is shown as 0 and 1 rather than as a checkbox.