David's Notes Mostly tech oriented...

SQLite tricks

David Gamba, @gambaeng
2023-02-02
version 0.2, 2023-05-18 #sqlite #csv

Importing CSV files

$ sqlite3 db_name.db
> .import data.csv table_name --csv --skip 1

Note	The --skip 1 option is used to skip the first line of the CSV file, which is the header.

This can be done to initialize the whole thing with the schema definition:

Put your schema and your import command into a file:

db.schema

CREATE TABLE IF NOT EXISTS "vol" (
        "N" INTEGER,
        "ID" TEXT,
        "State" TEXT,
        "Encrypted" TEXT,
        "Size" INTEGER,
        "Created" TEXT,
        "Name" TEXT,
        "Tags" TEXT
);

.import output/volumes.csv vol --csv --skip 1

Initialize the db:
```
sqlite3 db_name.db < db.schema
```

Importing TSV files

db.schema

CREATE TABLE IF NOT EXISTS "cities15000" (
	"geonameid" INTEGER,
	"name" TEXT,
	"asciiname" TEXT,
	"alternatenames" TEXT,
	"latitude" INTEGER,
	"longitude" INTEGER,
	"featureclass" TEXT,
	"featurecode" TEXT,
	"countrycode" TEXT,
	"cc2" TEXT,
	"admin1code" TEXT,
	"admin2code" TEXT,
	"admin3code" TEXT,
	"admin4code" TEXT,
	"population" TEXT,
	"elevation" TEXT,
	"dem" TEXT,
	"timezone" TEXT,
	"modificationdate" TEXT
);

.mode tabs
.import cities15000.txt cities15000

sqlite3 cities.db < db.schema

Exporting to CSV

sqlite3 -header -csv cities.db "select name,asciiname,countrycode,timezone from cities15000;" > cities15000-tz.csv

Exporting to TSV

sqlite3 cities.db ".headers off" ".mode tabs" "select distinct asciiname,countrycode,timezone from cities15000;"  > cities15000-tz.tsv