Documentation Source Text

Check-in [070bfddab9]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Update the command-line shell documentation with a discussion of CSV import and export.
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 070bfddab913186a277cb852464f28aaf4e7cf1f
User & Date: drh 2014-04-21 18:20:24
Context
2014-04-22
00:32
Rename the "sqlite.html" page to "cli.html". Keep an "sqlite.html" page that redirects to "cli.html". check-in: a6ea023899 user: drh tags: trunk
2014-04-21
18:20
Update the command-line shell documentation with a discussion of CSV import and export. check-in: 070bfddab9 user: drh tags: trunk
13:12
Update the FAQ. check-in: eb122dda42 user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to pages/sqlite.in.

14
15
16
17
18
19
20

21
22
23
24
25
26
27
28
29
30
..
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95






96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112

113
114
115
116
117
118
119
...
538
539
540
541
542
543
544
































































545
546
547
548
549
550
551
<h3>Getting Started</h3>

<p>To start the <b>sqlite3</b> program, just type "sqlite3" optionally
followed by the name the file that holds the SQLite database.  If the 
file does not exist, a new database file with the given name will be
created automatically.  If no database file is specified, a temporary 
database is created, then deleted when the "sqlite3" program exits.

The <b>sqlite3</b> program will
then prompt you to enter SQL.  Type in SQL statements (terminated by a
semicolon), press "Enter" and the SQL will be executed.</p>

<p>For example, to create a new SQLite database named "ex1" 
with a single table named "tbl1", you might do this:</p>

<tcl>
proc Code {body} {
  hd_puts {<blockquote><pre>}
................................................................................
}</tcl>

<h3>Double-click Startup On Windows</h3>

<p>Windows users can double-click on the <b>sqlite3.exe</b> icon to cause
the command-line shell to pop-up a terminal window running SQLite.  Note,
however, that by default this SQLite session is using an in-memory database,
not a file on disk, and so all changes will be lost when the session exist.
To use a persistent disk file as the database, use the ".open" command
immediately after the terminal window starts up:

<tcl>Code {
SQLite version 3.8.4 2014-02-11 16:24:34
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> (((.open ex1)))
sqlite> 
}</tcl>







<p>Alternatively, you can create a new database using the default in-memory
storage, then save that database into a disk file using the ".save" command:

<tcl>Code {
SQLite version 3.8.4 2014-02-11 16:24:34
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> [[[... many SQL commands omitted ...]]]
sqlite> (((.save ex1)))
sqlite> 
}</tcl>

<p>Be careful when using the ".save" command as it will overwrite any
preexisting database files having the same name without prompting for
confirmation.


<h3>Special commands to sqlite3</h3>

<p>
Most of the time, sqlite3 just reads lines of input and passes them
on to the SQLite library for execution.
But if an input line begins with a dot ("."), then
................................................................................
The first output column is the name the database is attached with, 
and the second column is the filename of the external file.</p>

<tcl>Code {
sqlite> (((.databases)))
}</tcl>


































































<h3>Converting An Entire Database To An ASCII Text File</h3>

<p>Use the ".dump" command to convert the entire contents of a
database into a single ASCII text file.  This file can be converted
back into a database by piping it back into <b>sqlite3</b>.</p>








>
|
|
|







 







|
|







|


>
>
>
>
>
>










|





|
>







 







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
..
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
...
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
<h3>Getting Started</h3>

<p>To start the <b>sqlite3</b> program, just type "sqlite3" optionally
followed by the name the file that holds the SQLite database.  If the 
file does not exist, a new database file with the given name will be
created automatically.  If no database file is specified, a temporary 
database is created, then deleted when the "sqlite3" program exits.

<p>When started, the <b>sqlite3</b> program will show a brief banner
message then prompt you to enter SQL.  Type in SQL statements (terminated
by a semicolon), press "Enter" and the SQL will be executed.</p>

<p>For example, to create a new SQLite database named "ex1" 
with a single table named "tbl1", you might do this:</p>

<tcl>
proc Code {body} {
  hd_puts {<blockquote><pre>}
................................................................................
}</tcl>

<h3>Double-click Startup On Windows</h3>

<p>Windows users can double-click on the <b>sqlite3.exe</b> icon to cause
the command-line shell to pop-up a terminal window running SQLite.  Note,
however, that by default this SQLite session is using an in-memory database,
not a file on disk, and so all changes will be lost when the session exits.
To use a persistent disk file as the database, enter the ".open" command
immediately after the terminal window starts up:

<tcl>Code {
SQLite version 3.8.4 2014-02-11 16:24:34
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> (((.open ex1.db)))
sqlite> 
}</tcl>

<p>The example above causes the database file named "ex1.db" to be opened
and used, and created if it does not previously exist.  You might want to
use a full pathname to ensure that the file is in the directory that you
think it is in.  Use forward-slashes as the directory separator character.
In other words use "c:/work/ex1.db", not "c:\work\ex1.db".</p>

<p>Alternatively, you can create a new database using the default in-memory
storage, then save that database into a disk file using the ".save" command:

<tcl>Code {
SQLite version 3.8.4 2014-02-11 16:24:34
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> [[[... many SQL commands omitted ...]]]
sqlite> (((.save ex1.db)))
sqlite> 
}</tcl>

<p>Be careful when using the ".save" command as it will overwrite any
preexisting database files having the same name without prompting for
confirmation.  As with the ".open" command, you might want to use a
full pathname with forward-slash directory separators to avoid abiguity.

<h3>Special commands to sqlite3</h3>

<p>
Most of the time, sqlite3 just reads lines of input and passes them
on to the SQLite library for execution.
But if an input line begins with a dot ("."), then
................................................................................
The first output column is the name the database is attached with, 
and the second column is the filename of the external file.</p>

<tcl>Code {
sqlite> (((.databases)))
}</tcl>


<tcl>hd_fragment csv {CSV import}</tcl>
<h3>CSV Import</h3>

<p>Use the ".import" command to import CSV (comma separated value) data into
an SQLite table.  The ".import" command takes two arguments which are the
name of the disk file from which CSV data is to be read and the name of the
SQLite table into which the CSV data is to be inserted.

<p>Note that it is important to set the "mode" to "csv" before running the
 ".import" command.  This is necessary to prevent the command-line shell
from trying to interpret the input file text as some other format.

<tcl>Code {
sqlite> (((.mode csv)))
sqlite> (((.import C:/work/somedata.csv tab1)))
}</tcl>

<p>There are two cases to consider:  (1) Table "tab1" does not previously
exist and (2) table "tab1" does already exist.

<p>In the first case, when the table does not previously exist, the table is
automatically created and the content of the first row of the input CSV
file is used to determine the name of all the columns in the table.  In
other words, if the table does not previously exist, the first row of the
CSV file is interpreted to be column names and the actual data starts on
the second row of the CSV file.

<p>For the second case, when the table already exists, every row of the
CSV file, including the first row, is assumed to be actual content.  If
the CSV file contains an initial row of column labels, that row will be
read as data and inserted into the table.  To avoid this, make sure that
table does not previously exist.

<tcl>hd_fragment csvout {CSV export}</tcl>
<h3>CSV Export</h3>

<p>To export an SQLite table (or part of a table) as CSV, simply set
the "mode" to "csv" and then run a query to extract the desired rows
of the table.

<tcl>Code {
sqlite> (((.header on)))
sqlite> (((.output c:/work/dataout.csv)))
sqlite> (((.mode csv)))
sqlite> (((SELECT * FROM tab1;)))
sqlite> (((.output stdout)))
}</tcl>

<p>In the example above, the ".header on" line causes column labels to
be printed as the first row of output.  This means that the first row of
the resulting CSV file will contain column labels.  If column labels are
not desired, set ".header off" instead. (The ".header off" setting is
the default and can be omitted if the headers have not been previously
turned on.)

<p>The line ".output <i>FILENAME</i>" causes all query output to go into
the named file instead of being printed on the console.  In the example
above, that line causes the CSV content to be written into a file named
"C:/work/dataout.csv".  The final line of the example
(the ".output stdout" line) causes subsequent output to be written to
the console again.  The use of ".output" is not required for CSV export,
but it is often more convenient than trying to copy/paste the resulting
CSV text from the console.

<h3>Converting An Entire Database To An ASCII Text File</h3>

<p>Use the ".dump" command to convert the entire contents of a
database into a single ASCII text file.  This file can be converted
back into a database by piping it back into <b>sqlite3</b>.</p>