The CSV virtual table reads
RFC 4180 formatted comma-separated
values, and returns that bid material as if it were rows and columns of an SQL
The CSV virtual table is precious to applications that must bulk-load
sizable amounts of comma-separated ticket bid material.
The CSV virtual table is also precious as a template source file for
implementing somewhat a couple of virtual tables.
The CSV virtual table is not any longer constructed into the SQLite amalgamation.
It’s accessible as a
separate source file
that can even be compiled loyal into a loadable extension.
Conventional utilization of the CSV virtual table from the
repeat-line shell would be something devour this:
.load ./csv CREATE VIRTUAL TABLE temp.t1 USING csv(filename='thefile.csv'); SELECT FROM t1;
The principle line of the script above causes the repeat-line shell to
read and spark off the bustle-time loadable extension for CSV. For an
utility, the identical C-language API is
Test that the filename extension (ex: “.dll” or “.so” or “.dylib”) is
brushed off from the extension filename. Omitting the filename extension is
no longer required, but it no doubt helps in making the script unpleasant-platform. SQLite
will robotically append the acceptable extension.
The 2d line above creates a virtual table named “t1” that reads
the bid material of the file named within the argument. The amount and names of
the columns is evident robotically by reading the first line of
bid material. Diversified suggestions to the CSV virtual table present the ability to
desire the CSV bid material from a string in say of a separate file, and give
the programmer extra adjust over the amount and names of the columns.
The suggestions are detailed below. The CSV virtual table is on the total
created as a TEMP table in bid that it exists stunning for the present database
connection and does no longer turn loyal into a everlasting fragment of the database schema.
Show that there’s no “CREATE TEMP VIRTUAL TABLE” repeat in SQLite.
As an different, prepend the “temp.” schema prefix to the title of the virtual
The third line of the example shows the virtual table being frail, to read
all bid material of the CSV file. Right here’s possibly doubtlessly the most efficient doubtless use
of the virtual table. The CSV virtual table can even be frail wherever an abnormal
virtual table can even be frail. One can use the CSV virtual table interior subqueries,
or frequent table expressions or add WHERE, GROUP BY, HAVING, ORDER BY,
and LIMIT clauses as required.
The example above confirmed a single filename=’thefile.csv’ argument
for the CSV virtual table. But somewhat a couple of arguments are also doubtless.
The filename= argument specifies an external file from which
CSV bid material is read. Every CSV virtual table will must have either a
filename= argument or a files= argument and no longer both.
The files= argument specifies that TEXT is the literal
bid material of the CSV file.
The schema= argument specifies a CREATE TABLE assertion that
the CSV virtual table passes to the sqlite3_declare_vtab() interface in
expose to make clear the names of the columns within the virtual table.
The columns=N argument specifies the choice of columns
within the CSV file.
If the input files incorporates extra columns than this,
then the extra columns are no longer well-liked. If the input files incorporates fewer columns,
then additional columns are filled with NULL.
If the columns=N argument is brushed off, the first line of the
CSV file is read to search out out the choice of columns.
If the header argument is upright then the first row of the CSV file
to be handled as a header in say of as files. The 2d line of the CSV
file becomes the first row of bid material.
If the schema= suggestions is brushed off, then the first line of the CSV
file determines the names of the columns.
The column names of the virtual table are obvious primarily by the
If the schema= argument is brushed off, but header is upright, then
the values point out within the first line of the CSV file became the column names.
If the schema= argument is brushed off and header is false, then
the columns are named “c0”, “c1”, “c2”, and so forth.