CODEWITHSUNDEEP
sqlite
user1581390
user1581390

Reputation: 2008

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

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;

Upvotes: 0

Views: 580

Answers (2)

BallpointBen
BallpointBen

Reputation: 13839

As of 3.32.0, this has become a bit simpler. You can now create the desired table ahead of time and import your CSV directly into it, skipping the creation of a temp table altogether. The --skip 1 argument skips the header row of the CSV, which would otherwise be interpreted as a data row when importing into an existing table.

.open outputSQLDB.db
DROP TABLE IF EXISTS myTbl;
CREATE TABLE myTbl (
    id INTEGER,
    field1 TEXT,
    field2 INTEGER
);
CREATE INDEX id_index ON myTbl (id);
.import --csv --skip 1 input.csv myTbl

Upvotes: 0

Olivier Jacot-Descombes
Olivier Jacot-Descombes

Reputation: 112672

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.

Upvotes: 0

Related Questions