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. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
070bfddab913186a277cb852464f28aa |
User & Date: | drh 2014-04-21 18:20:24.019 |
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
Changes to pages/sqlite.in.
︙ | ︙ | |||
14 15 16 17 18 19 20 | <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. | > | | | | 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 | <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>} |
︙ | ︙ | |||
77 78 79 80 81 82 83 | }</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, | | | | > > > > > > | | > | 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 | }</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 |
︙ | ︙ | |||
538 539 540 541 542 543 544 545 546 547 548 549 550 551 | 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> | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 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 | 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> |
︙ | ︙ |