The command-line utility named sqlite3 allows the user to manually enter and execute SQL commands against an SQLite database. 


For a listing of the available commands while you are interactively using sqlite3, you can enter ".help" at any time. For example:


sqlite> .help

.backup ?DB? FILE      Backup DB (default "main") to FILE

.bail ON|OFF           Stop after hitting an error.  Default OFF

.databases             List names and files of attached databases

.dump ?TABLE? ...      Dump the database in an SQL text format

.echo ON|OFF           Turn command echo on or off

.exit                  Exit this program

.explain ON|OFF        Turn output mode suitable for EXPLAIN on or off.

.genfkey ?OPTIONS?     Options are:

                         --no-drop: Do not drop old fkey triggers.

                         --ignore-errors: Ignore tables with fkey errors

                         --exec: Execute generated SQL immediately

                       See file tool/genfkey.README in the source 

                       distribution for further information.


.header(s) ON|OFF      Turn display of headers on or off

.help                  Show this message

.import FILE TABLE     Import data from FILE into TABLE

.indices TABLE         Show names of all indices on TABLE

.iotrace FILE          Enable I/O diagnostic logging to FILE

.load FILE ?ENTRY?     Load an extension library

.mode MODE ?TABLE?     Set output mode where MODE is one of:

                         csv      Comma-separated values

                         column   Left-aligned columns.  (See .width)

                         html     HTML <table> code

                         insert   SQL insert statements for TABLE

                         line     One value per line

                         list     Values delimited by .separator string

                         tabs     Tab-separated values

                         tcl      TCL list elements

……

……

……

sqlite>



To go ahead with this short tutorial, we are going to consider the following commands:

  • .import
  • .separator
  • .mode
  • .output
  • .headers

Let's start importing a csv file into a database.

the file we are going to import is the following one:

Note: this procedure will work for simple files (if a field of a record contains something like "duffy, duck" it won't work)

$ cat aux.csv

1,2,3,4

2,3,4,56

32,342,34,34

34,3434,342,34

1,4,1,4

2,5,2,5

2,5,2,5

3,5,3,5

34,5,34,5

4,6,4,6

5,78,5,78

Open a new database:

$ sqlite3 newdb.db

Create the table that will contain our data:

 

sqlite> create table test (id integer, value integer, level integer, money integer);

Setup the separator

sqlite> .separator ","

Import data from the file to the test table

sqlite> .import aux.csv test

Setup the colum mode 

sqlite> .mode column

Setup the header output
sqlite> .header on

verify the file has been imported
sqlite> select * from test;
id          value        level       money 
----------  -----------  ----------  ----------
1           2            3           4
2           3            4           56
32          342          34          34
34          3434         342         34
1           4            1           4
2           5            2           5
2           5            2           5
3           5            3           5
34          5            34          5
4           6            4           6
5           78           5           78

 

 

Exporting the database to a text file (.csv)

Setup the output mode to csv

sqlite> .mode csv

Select the output file to send data to

sqlite> .output myfile.csv

run the query

sqlite> select * from test;

set the output to standard output and exit from sqlite3

 

sqlite> .output stdout

sqlite> .quit

Check the output file

 

$ cat myfile.csv

id,value,level,money

1,2,3,4

2,3,4,56

32,342,34,34

34,3434,342,34

1,4,1,4

2,5,2,5

2,5,2,5

3,5,3,5

34,5,34,5

4,6,4,6

5,78,5,78


That's all,

Gg1.