The CSV Virtual Table


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');

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.

  • filename=FILENAME

    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.

  • files=TEXT

    The files= argument specifies that TEXT is the literal
    bid material of the CSV file.

  • schema=SCHEMA

    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.

  • columns=N

    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.

  • header=BOOLEAN
    or stunning

    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
schema= argument.
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.

Read More

Ava Chan

Ava Chan

I'm a researcher at Utokyo :) and a big fan of Ava Max