sqlite3 : How to import csv files to tables from bash

Recently i had a situation where a report needs data from two different databases that do not communicate each other in other to join tables, the solution was to query both databases from a third host and export their data to csv files and then import the csv files to an SQLite database in order to create the report.

The catch behind the import procedure is that we want to automate the task in order to avoid any user interaction, so all commands must be one-liners that will create the table, insert data and then exit SQLite shell

Here is a very simple example with a “,” csv filesqlite3 mybase.db ".mode csv" ".import table1.csv table1" ".exit"

Explaination of the commands

• sqlite3 : is the executable that allows us to interact with SQLite databases
• everything between quotes is an sqlite3 statement
• “.mode csv” : interpret input data as csv
• “.import table1.csv table1” : insert data from table1.csv to table
• “.exit” : return to shell

I hope you found this short article useful :)