SQLite: Create database and import data

-- create a new database in the current directory
sqlite3 chems.db

-- check that is is created
.databases

-- open the database (if necessary)
ATTACH DATABASE 'chems.db' as 'chems';

-- create a table
CREATE TABLE substances (
    id INT PRIMARY KEY NOT NULL,
    ec_number CHAR(15) ,
    cas_number CHAR(15) ,
    ec_name TEXT,
    mol_formula TEXT
);

-- check that it is created
.schema

-- truncate the table if a fresh start is needed
delete from substance;

-- import pipe delimited file (no headers!!!)
.separator '|'
.import chems.csv substances

-- confirm data import
SELECT * FROM substances;

-- detach from database (if necessary)
DETACH DATABASE 'chems';