Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Add documentation on the edit() function and .excel command in the CLI. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA3-256: |
24c4bece599c5c174312a02a580d2e7f |
User & Date: | drh 2018-01-11 00:44:47.731 |
Context
2018-01-11
| ||
18:29 | Add sqlite3_vtab_nochange() to the change log. (check-in: 3b8a8bd89a user: drh tags: trunk) | |
00:44 | Add documentation on the edit() function and .excel command in the CLI. (check-in: 24c4bece59 user: drh tags: trunk) | |
2018-01-10
| ||
20:28 | Add the "Export to Excel" subparagraph under "CSV Export" section of the CLI documentation. (check-in: 1728ebf59c user: drh tags: trunk) | |
Changes
Changes to pages/changes.in.
︙ | ︙ | |||
77 78 79 80 81 82 83 84 85 86 87 88 89 90 | <li> Added the experimenal [.expert command] <li> Added the ".eqp trigger" variant of the ".eqp" command <li> Enhance the ".lint fkey-indexes" command so that it works with [WITHOUT ROWID] tables. <li> If the filename argument to the shell is a ZIP archive rather than an SQLite database, then the shell automatically opens that ZIP archive using the [Zipfile virtual table]. <li> Databases are opened using [https://sqlite.org/src/file/ext/misc/appendvfs.c|Append VFS] when the --append flag is used on the command line or with the .open command. </ol> <li> Enhance the [SQLITE_ENABLE_UPDATE_DELETE_LIMIT] compile-time option so that it works for [WITHOUT ROWID] tables. | > > > | 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 | <li> Added the experimenal [.expert command] <li> Added the ".eqp trigger" variant of the ".eqp" command <li> Enhance the ".lint fkey-indexes" command so that it works with [WITHOUT ROWID] tables. <li> If the filename argument to the shell is a ZIP archive rather than an SQLite database, then the shell automatically opens that ZIP archive using the [Zipfile virtual table]. <li> Added the [edit() SQL function]. <li> Added the [export to excel|.excel command] to simplify exporting database content to a spreadsheet. <li> Databases are opened using [https://sqlite.org/src/file/ext/misc/appendvfs.c|Append VFS] when the --append flag is used on the command line or with the .open command. </ol> <li> Enhance the [SQLITE_ENABLE_UPDATE_DELETE_LIMIT] compile-time option so that it works for [WITHOUT ROWID] tables. |
︙ | ︙ |
Changes to pages/cli.in.
︙ | ︙ | |||
156 157 158 159 160 161 162 163 164 165 166 167 168 169 | .databases List names and files of attached databases .dbinfo ?DB? Show status information about the database .dump ?TABLE? ... Dump the database in an SQL text format If TABLE specified, only dump tables matching LIKE pattern TABLE. .echo on|off Turn command echo on or off .eqp on|off|full Enable or disable automatic EXPLAIN QUERY PLAN .exit Exit this program .expert EXPERIMENTAL. Suggest indexes for specified queries .fullschema ?--indent? Show schema and the content of sqlite_stat tables .headers on|off Turn display of headers on or off .help Show this message .import FILE TABLE Import data from FILE into TABLE .imposter INDEX TABLE Create imposter table TABLE on index INDEX | > | 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 | .databases List names and files of attached databases .dbinfo ?DB? Show status information about the database .dump ?TABLE? ... Dump the database in an SQL text format If TABLE specified, only dump tables matching LIKE pattern TABLE. .echo on|off Turn command echo on or off .eqp on|off|full Enable or disable automatic EXPLAIN QUERY PLAN .excel Display the output of next command in a spreadsheet .exit Exit this program .expert EXPERIMENTAL. Suggest indexes for specified queries .fullschema ?--indent? Show schema and the content of sqlite_stat tables .headers on|off Turn display of headers on or off .help Show this message .import FILE TABLE Import data from FILE into TABLE .imposter INDEX TABLE Create imposter table TABLE on index INDEX |
︙ | ︙ | |||
184 185 186 187 188 189 190 | insert SQL insert statements for TABLE line One value per line list Values delimited by "|" quote Escape answers as for SQL tabs Tab-separated values tcl TCL list elements .nullvalue STRING Use STRING in place of NULL values | | > > | | 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 | insert SQL insert statements for TABLE line One value per line list Values delimited by "|" quote Escape answers as for SQL tabs Tab-separated values tcl TCL list elements .nullvalue STRING Use STRING in place of NULL values .once (-e|-x|FILE) Output for the next SQL command only to FILE or invoke system text editor (-e) or spreadsheet (-x) on the output. .open ?OPTIONS? ?FILE? Close existing database and reopen FILE The --new option starts with an empty file .output ?FILE? Send output to FILE or stdout .print STRING... Print literal STRING .prompt MAIN CONTINUE Replace the standard prompts .quit Exit this program .read FILENAME Execute SQL in FILENAME .restore ?DB? FILE Restore content of DB (default "main") from FILE .save FILE Write in-memory database into FILE .scanstats on|off Turn sqlite3_stmt_scanstatus() metrics on or off |
︙ | ︙ | |||
431 432 433 434 435 436 437 438 439 440 441 442 443 444 | it reads from standard input. So to see the results of a query in a text editor, one could type:</p> <tclscript>DisplayCode { sqlite3> (((.once '|open -f'))) sqlite3> (((SELECT * FROM bigTable;))) }</tclscript> <tcl>hd_fragment fileio {file I/O functions}</tcl> <h2>File I/O Functions</h2> <p>The command-line shell adds two [application-defined SQL functions] that facilitate reading content from a file into a table column, and writing the content of a column into a file, respectively. | > > > > > > > > > > > > > > > > > > > > | 434 435 436 437 438 439 440 441 442 443 444 445 446 447 448 449 450 451 452 453 454 455 456 457 458 459 460 461 462 463 464 465 466 467 | it reads from standard input. So to see the results of a query in a text editor, one could type:</p> <tclscript>DisplayCode { sqlite3> (((.once '|open -f'))) sqlite3> (((SELECT * FROM bigTable;))) }</tclscript> <p>If the ".output" or ".once" commands have an argument of "-e" then output is collected into a temporary file and the system text editor is invoked on that text file. Thus, the command ".once -e" achieves the same result as ".once '|open -f'" but with the benefit of being portable across all systems. <p>If the ".output" or ".once" commands have a "-x" argument, that causes them to accumulate output as Comma-Separated-Values (CSV) in a temporary file, then invoke the default system utility for viewing CSV files (usually a spreadsheet program) on the result. This is a quick way of sending the result of a query to a spreadsheet for easy viewing: <tclscript>DisplayCode { sqlite3> (((.once -x))) sqlite3> (((SELECT * FROM bigTable;))) }</tclscript> <p>The ".excel" command is an alias for ".once -x". It does exactly the same thing. <tcl>hd_fragment fileio {file I/O functions}</tcl> <h2>File I/O Functions</h2> <p>The command-line shell adds two [application-defined SQL functions] that facilitate reading content from a file into a table column, and writing the content of a column into a file, respectively. |
︙ | ︙ | |||
462 463 464 465 466 467 468 469 470 471 472 473 474 475 | }</tclscript> <p>Note that the readfile(X) and writefile(X,Y) functions are extension functions and are not built into the core SQLite library. These routines are available as a [loadable extension] in the [http://www.sqlite.org/src/artifact?ci=trunk&filename=ext/misc/fileio.c|ext/misc/fileio.c] source file in the [SQLite source code repositories]. <tcl>hd_fragment schema</tcl> <h1>Querying the database schema</h1> <p>The sqlite3 program provides several convenience commands that are useful for looking at the schema of the database. There is nothing that these commands do that cannot be done by some other | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 485 486 487 488 489 490 491 492 493 494 495 496 497 498 499 500 501 502 503 504 505 506 507 508 509 510 511 512 513 514 515 516 517 518 519 520 521 522 523 524 525 526 527 528 529 530 531 532 533 534 535 536 537 | }</tclscript> <p>Note that the readfile(X) and writefile(X,Y) functions are extension functions and are not built into the core SQLite library. These routines are available as a [loadable extension] in the [http://www.sqlite.org/src/artifact?ci=trunk&filename=ext/misc/fileio.c|ext/misc/fileio.c] source file in the [SQLite source code repositories]. <tcl>hd_fragment editfunc {edit() SQL function}</tcl> <h2>The edit() SQL funtion</h2> <p>The CLI has another build-in SQL function named edit(). Edit() takes one or two arguments. The first argument is a value - usually a large multi-line string to be edited. The second argument is the name of a text editor. If the second argument is omitted, the VISUAL environment variable is used. The edit() function writes its first argument into a temporary file, invokes the editor on the temporary file, rereads the file back into memory after the editor is done, then returns the edited text. <p>The edit() function can be used to make changes to large text values. For example: <tclscript>DisplayCode { sqlite> (((UPDATE docs SET body=edit(body) WHERE name='report-15';))) }</tclscript> <p>In this example, the content of the docs.body field for the entry where docs.name is "report-15" will be sent to the editor. After the editor returns, the result will be written back into the docs.body field. <p>The default operation of edit() is to invoke a text editor. But by using an alternative edit program in the second argument, you can also get it to edit images or other non-text resources. For example, if you want to modify a JPEG image that happens to be stored in a field of a table, you could run: <tclscript>DisplayCode { sqlite> (((UPDATE pics SET img=edit(img,'gimp') WHERE id='pic-1542';))) }</tclscript> <p>The edit program can also be used as a viewer, by simply ignoring the return value. For example, to merely look at the image above, you might run: <tclscript>DisplayCode { sqlite> (((SELECT length(edit(img,'gimp')) WHERE id='pic-1542';))) }</tclscript> <tcl>hd_fragment schema</tcl> <h1>Querying the database schema</h1> <p>The sqlite3 program provides several convenience commands that are useful for looking at the schema of the database. There is nothing that these commands do that cannot be done by some other |
︙ | ︙ | |||
630 631 632 633 634 635 636 | turned on.) <p>The line ".once <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". | < < < > > > > > > > > > > > > | > > > > > > > > > > > > > > > > > > > > | 692 693 694 695 696 697 698 699 700 701 702 703 704 705 706 707 708 709 710 711 712 713 714 715 716 717 718 719 720 721 722 723 724 725 726 727 728 729 730 731 732 733 734 735 736 737 738 739 740 741 742 743 744 745 746 747 748 749 750 751 752 753 754 755 756 | turned on.) <p>The line ".once <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". <p>The final line of the example (the ".system c:/work/dataout.csv") has the same effect as double-clicking on the c:/work/dataout.csv file in windows. This will typically bring up a spreadsheet program to display the CSV file. <p>That command only works as written on Windows. The equivalent line on a Mac would be: <tclscript>DisplayCode { sqlite> (((.system open dataout.csv))) }</tclscript> <p>On Linux and other unix systems you will need to enter something like: <tclscript>DisplayCode { sqlite> (((.system xdg-open dataout.csv))) }</tclscript> <tcl>hd_fragment exexcel* {export to excel}</tcl> <h2> Export to Excel </h2> <p>To make it easier to export to a spreadsheet, the CLI provides the ".excel" command, which capture the output of a single query and sends that result to the default spreadsheet program on the host computer. Use it like this: <tclscript>DisplayCode { sqlite> (((.excel))) sqlite> (((SELECT * FROM tab;))) }</tclscript> <p> The code above writes the output of the query as CSV into a temporary file, invokes the default handler for CSV files (usually the preferred spreadsheet program such as Excel or LibreOffice), then deletes the temporary file. This is essentially a short-hand method of doing the sequence of ".csv", ".once", and ".system" commands described above. <p> The ".excel" command is really an alias for ".once -x". The -x option to .once causes it to writes results as CSV into a temporary file that is named with a ".csv" suffix, then invoke the systems default handler for CSV files. <p> There is also a ".once -e" command which works similarly, except that it names the temporary file with a ".txt" suffix so that the default text editor for the system will be invoked, instead of the default spreadsheet. <tcl>hd_fragment dump {.dump}</tcl> <h1>Converting An Entire Database To An ASCII Text File</h1> <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> |
︙ | ︙ |