Once we have (somewhat or totally clean) tabular data we are ready to work with, we can put it into a SQLite database. This way we can unleash the power of SQL on the data to varied and potentially complicated analysis.

Loading csv files into SQLite

sqlite-utils insert sqlite_filename table_name file_to_upload.csv --csv

# load the contents of iris.csv file into a table called "iris" in the sqlite file iris_03.db
sqlite-utils insert iris_03.db iris iris.csv --csv

Loading csv files with no headers into SQLite

# load the contents of iris.csv file with no headers into a table called "iris" in the sqlite file iris_03.db
sqlite-utils insert iris_03.db iris iris.csv --csv --no-headers

load tsv files

sqlite-utils insert sqlite_filename table_name file_to_upload.tsv --tsv

# load the contents of iris.tsv file into a table called "iris" in the sqlite file iris_04.db
sqlite-utils insert iris_04.db iris iris.tsv --tsv

load json files

# sqlite-utils insert sqlite_filename table_name file_to_upload.json
# load the contents of iris.json file into a table called "iris" in the sqlite file iris_05.db
sqlite-utils insert iris_05.db iris iris.json

load excel files

via in2csv and sqlite-utils

# first convert from excel into csv
in2csv iris.xlsx > converted_iris.csv

# Then load into SQLite
# load the contents of converted_iris.csv file into a table called "iris" in the sqlite file iris_06.db
sqlite-utils insert iris_06.db iris converted_iris.csv --csv

If there are multiple sheets in the excel file, it is possible to convert a single sheet with the --sheet modifier.

# first convert from excel into csv and then load into SQLite.
# the data we want is in a sheet in the excel file called 'my_iris_data'
in2csv --sheet 'my_iris_data' iris.xlsx > converted_iris.csv

# load the contents of converted_iris.csv file into a table called "iris" in the sqlite file iris_06.db
sqlite-utils insert iris_07.db iris converted_iris.csv --csv