Documentation Source Text

Check-in [c0ef40b603]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Improvements to documentation on the CSV virtual table.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: c0ef40b6037e3f3fbf7ddbc7e4038458eed8638d63b17b71a044100144b23688
User & Date: drh 2018-11-16 01:43:54.532
Context
2018-11-16
01:45
Fix a typo in csv.html. (check-in: ccee8fa12e user: drh tags: trunk)
01:43
Improvements to documentation on the CSV virtual table. (check-in: c0ef40b603 user: drh tags: trunk)
2018-11-15
16:53
Remove all hyperlinks to CVSTrac in preparation for shutting down all CVSTrac servers, which are now unmaintained for 10 years and are showing their age. (check-in: 3c24a2c7f6 user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to pages/csv.in.
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95

96
97
98











99










The third line of the example shows the virtual table being used, to read
all content of the CSV file.  This is perhaps the simplest possible use
of the virtual table.  The CSV virtual table can be used anywhere an ordinary
virtual table can be used.  One can use the CSV virtual table inside subqueries,
or [common table expressions] or add WHERE, GROUP BY, HAVING, ORDER BY,
and LIMIT clauses as required.

<h1>Recognized Arguments</h1>

<p>
The example above showed a single <b>filename='thefile.csv'</b> argument
for the CSV virtual table.  But other arguments are also possible.

<ul>
<li><p><b>filename=</b><i>FILENAME</i>
<p>The <b>filename=</b> argument specifies an external file from which
CSV content is read.  Every CSV virtual table must have either a 
<b>filename=</b> argument or a <b>data=</b> argument and not both.

<li><p><b>data=</b><i>TEXT</i>
<p>The <b>data=</b> argument specifies that <i>TEXT</i> is the literal
content of the CSV file.

<li><p><b>schema=</b><i>SCHEMA</i>
<p> The <b>schema=</b> argument specifies a [CREATE TABLE] statement that
the CSV virtual table passes to the [sqlite3_declare_vtab()] interface in
order to define the number and names of the columns in the virtual table.
If both the <b>schema=</b> and the <b>columns=</b> arguments are omitted,
then the CSV virtual table reads the first row of the input content in order
to determine the number of columns and names the columns <b>cNNN</b> where
<b>NNN</b> values are consecutive integers.  It is not allowed to have
both <b>schema=</b> and <b>columns=</b> arguments.

<li><p><b>columns=</b><i>N</i>
<p>The <b>columns=</b><i>N</i> argument causes the virtual table to have

exactly <i>N</i> columns.  If the input data contains more columns than this,
then the excess columns are ignored.  If the input data contains fewer columns,
then extra columns are filled with NULL.











</u>

















|


















|
<
<
<
<
<


|
>
|


>
>
>
>
>
>
>
>
>
>
>
|
>
>
>
>
>
>
>
>
>
>
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
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
The third line of the example shows the virtual table being used, to read
all content of the CSV file.  This is perhaps the simplest possible use
of the virtual table.  The CSV virtual table can be used anywhere an ordinary
virtual table can be used.  One can use the CSV virtual table inside subqueries,
or [common table expressions] or add WHERE, GROUP BY, HAVING, ORDER BY,
and LIMIT clauses as required.

<h1>Arguments</h1>

<p>
The example above showed a single <b>filename='thefile.csv'</b> argument
for the CSV virtual table.  But other arguments are also possible.

<ul>
<li><p><b>filename=</b><i>FILENAME</i>
<p>The <b>filename=</b> argument specifies an external file from which
CSV content is read.  Every CSV virtual table must have either a 
<b>filename=</b> argument or a <b>data=</b> argument and not both.

<li><p><b>data=</b><i>TEXT</i>
<p>The <b>data=</b> argument specifies that <i>TEXT</i> is the literal
content of the CSV file.

<li><p><b>schema=</b><i>SCHEMA</i>
<p> The <b>schema=</b> argument specifies a [CREATE TABLE] statement that
the CSV virtual table passes to the [sqlite3_declare_vtab()] interface in
order to define the names of the columns in the virtual table.






<li><p><b>columns=</b><i>N</i>
<p>The <b>columns=</b><i>N</i> argument specifies the number of columns
in the CSV file.
If the input data contains more columns than this,
then the excess columns are ignored.  If the input data contains fewer columns,
then extra columns are filled with NULL.
If the <b>columns=</b><i>N</i> argument is omitted, the first line of the
CSV file is read to determine the number of columns.

<li><p><b>header=</b><i>BOOLEAN</i><br>
or just<br>
<b>header</b>
<p>If the <b>header</b> argument is true then the first row of the CSV file
to be treated as a header rather than as data.  The second line of the CSV
file becomes the first row of content.
If the <b>schema=</b> options is omitted, then the first line of the CSV
file determines the names of the columns.
</ul>

<h1>Column Names</h1>

<p>
The column names of the virtual table are determined primarily by the
<b>schema=</b> argument.
If the <b>schema=</b> argument is omitted, but <b>header</b> is true, then
the values found in the first line of the CSV file become the column names.
If the <b>schema=</b> argument is omitted and <b>header</b> is false, then
the columns are name "c0", "c1", "c2", and so forth.