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);
sqlite> .separator ","
sqlite> .import aux.csv test
sqlite> .mode column
sqlite> .header on
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.
Good stuff! Very helpful!