SQLite3: How to Import an CSV and change the column types?

Question

I am looking to import an CSV using Command Line Shell For SQLite on linux (SQLite version 3.29.0), and set the appropriate data types.

sqlite> .open outputSQLDB.db
sqlite> .import input.csv tmpTable

But now the imported table is messed up:

sqlite> .schema
CREATE TABLE DX(
  "id,field1,field2" TEXT
);

Why aren't the fields separated?

At the end do I just do:

sqlite> CREATE TABLE myTbl (
   ...>                    id INTEGER,
   ...>                    field1 TEXT,
   ...>                    field2 INTEGER
   ...>                   );
CREATE INDEX id_index on myTbl (id);
sqlite> DROP TABLE IF EXISTS tmpTable;

You seem to be using some command line tool. Please specify which one you are using. — Olivier Jacot-Descombes, Oct 02 '19 at 17:18
Try to execute the command `.mode csv` before `.import input.csv tmpTable` as stated in the [link](https://www.sqlite.org/cli.html#csv_import) I inserted into your question. Also, this [answer](https://stackoverflow.com/a/29437981/880990) to [Import CSV to SQLite](https://stackoverflow.com/questions/14947916/import-csv-to-sqlite) shows how you can specify a separator. — Olivier Jacot-Descombes, Oct 02 '19 at 19:56
it works! Then running 'CREATE TABLE ...' the .schema changes, but when I run 'SELECT id from myTbl;' I get no results? — user1581390, Oct 02 '19 at 20:00
You you never added the content of `tmpTable` to `myTbl`. Execute `INSERT INTO myTbl (id, field1, field2) SELECT id, field1, field2 FROM tmpTable;` before the `DROP TABLE`. — Olivier Jacot-Descombes, Oct 02 '19 at 20:08

Olivier Jacot-Descombes · Accepted Answer · 2019-10-03T12:51:43.053

Specify .mode csv before inserting. Also make sure the temp table does not exist, otherwise SQLite interpretes the first line of the CSV as data.

Before dropping the temp table, transfer the rows to the new table with an INSERT INTO command. Otherwise they will be lost.

You will get a command sequence of:

.open outputSQLDB.db
DROP TABLE IF EXISTS tmpTable;
.mode csv
.import input.csv tmpTable
CREATE TABLE myTbl (
    id INTEGER,
    field1 TEXT,
    field2 INTEGER
);
CREATE INDEX id_index on myTbl (id);
INSERT INTO myTbl (id, field1, field2)
SELECT id, field1, field2
FROM tmpTable;
DROP TABLE IF EXISTS tmpTable;

I would also either use

CREATE TABLE IF NOT EXISTS myTbl (
   ...
);

or

DROP TABLE IF EXISTS myTbl;

before creating the table.

SQLite3: How to Import an CSV and change the column types?

1 Answers1