The .mode command sets the output mode. This
determines how the output data is formatted and presented. The
optional table name is only used by the insert mode. The default mode is list. Supported modes
include:
Output is in a tabular
format, with one row per line. The width of each
column is defined by the values provided by the
.width command.
Output will be clipped to fit into the column
width. If headers are on, the first two lines will
be the column names, followed by separator dashes.
In most cases, this is the easiest format for
humans to read, assuming the column widths are set
to something useful.
Output is in comma-separated values, with one row per line. Each value is separated by a single comma character with no trailing space. If headers are on, the first line will be the set of column names. If a value contains a comma, the value will be enclosed in double quotes. If the value contains a double quote character, the SQL rules for escaping quotes will be used and the literal double quote will be replaced with two double quote characters in sequence. This is not what many CSV importers expect, however.
Output is in an HTML
table format. Each database row will be output as
a <tr> element (table row).
If headers are on, the first table row element
will contain the column names. For older versions
of HTML, the output is suitable to place within a
<table> element.
Output is a series of
SQL INSERT
statements. If a table name is given as part of
the .mode
command, that name will be used as the table in
the INSERT
statement. If no table name is given, the name
table will be
used. The header flag has no effect in this
mode.
Output is one value per
line in the format column_name = value (with some possible
leading white space before the column name). Each
row is separated by a blank line. The header flag
has no effect in this mode.
Output consists of a sequence of values, with
one row per line. Each value is separated by the
current separator string (default is |, a bar or pipe). No
separator will appear after the final value. There
is no provision to quote or escape the separator
string if it appears in a value. If headers are
on, the first line will contain the column names.
In general, this is the easiest format to parse,
assuming a unique separator is used.
Output is in tab-separated values, with one row per line. There is no provision to quote or escape tab characters included in values. If headers are on, the first line will contain the column names.
Output consists of a sequence of values, with
one row per line. Each value is separated by the
current separator string (default is |, a bar or pipe). A
separator will be included after the final value.
All values will be put in double quotes, although
there is no provision to quote or escape double
quote characters included in values. If headers
are on, the first line will contain the column
names. This mode is designed to be used with the
Tcl scripting language.
If the output of sqlite3 is fed directly into a script or other
automated system, be very sure you understand how values are
delimited and that there are provisions to quote, escape, or
avoid any separators within the returned values. Of specific
note, be aware that the CSV format is not nearly as universal as
many people think. While the format works well for numbers and
simple data, if text values require quoting or escape sequences,
compatibility should be tested before being used.