Easiest way to recast variables in SQLite

Question

New to databasing, so please let me know if I'm going about this entirely wrong.

I want to use databases to store large datasets (I use R to analyze data, which cannot load datasets larger than available RAM) and and I'm using SQLite-Manager in FireFox to import .csv files. 99% of the time I use reals, but would like to avoid all the clicking to manually cast each of 100 columns as REAL (the default in SQLite-Manager is TEXT).

Is there a way I can I can quickly/easily cast all columns as REAL? Thanks!

Answer 1

Hello, why don't you make a script to be interpreted by the SQLite shell?

Run sqlite my_db < script.txt with contents of scripts.txt following:

CREATE TABLE foo(
 col1 REAL,
 col2 REAL,
 [...] generate those lines with a decent text editor
);

.separator ;
.import 'my/csv/file.csv' foo
.q

Note that dot-commands of the SQLite shell are available using “.help”. Imports are rudimentary and won't work if you have double quotes (remove them). Only the , is interpreted as a separator, you cannot escape it. If needed you can use a multicharacter separator.

Also be sure that file.csv is UTF8-encoded.