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;

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.