1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
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
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
|
<!DOCTYPE html>
<html><head>
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
<link href="sqlite.css" rel="stylesheet">
<title>The CSV Virtual Table</title>
<!-- path= -->
</head>
<body>
<div class=nosearch>
<a href="index.html">
<img class="logo" src="images/sqlite370_banner.gif" alt="SQLite" border="0">
</a>
<div><!-- IE hack to prevent disappearing logo --></div>
<div class="tagline desktoponly">
Small. Fast. Reliable.<br>Choose any three.
</div>
<div class="menu mainmenu">
<ul>
<li><a href="index.html">Home</a>
<li class='mobileonly'><a href="javascript:void(0)" onclick='toggle_div("submenu")'>Menu</a>
<li class='wideonly'><a href='about.html'>About</a>
<li class='desktoponly'><a href="docs.html">Documentation</a>
<li class='desktoponly'><a href="download.html">Download</a>
<li class='wideonly'><a href='copyright.html'>License</a>
<li class='desktoponly'><a href="support.html">Support</a>
<li class='desktoponly'><a href="prosupport.html">Purchase</a>
<li class='search' id='search_menubutton'>
<a href="javascript:void(0)" onclick='toggle_search()'>Search</a>
</ul>
</div>
<div class="menu submenu" id="submenu">
<ul>
<li><a href='about.html'>About</a>
<li><a href='docs.html'>Documentation</a>
<li><a href='download.html'>Download</a>
<li><a href='support.html'>Support</a>
<li><a href='prosupport.html'>Purchase</a>
</ul>
</div>
<div class="searchmenu" id="searchmenu">
<form method="GET" action="search">
<select name="s" id="searchtype">
<option value="d">Search Documentation</option>
<option value="c">Search Changelog</option>
</select>
<input type="text" name="q" id="searchbox" value="">
<input type="submit" value="Go">
</form>
</div>
</div>
<script>
function toggle_div(nm) {
var w = document.getElementById(nm);
if( w.style.display=="block" ){
w.style.display = "none";
}else{
w.style.display = "block";
}
}
function toggle_search() {
var w = document.getElementById("searchmenu");
if( w.style.display=="block" ){
w.style.display = "none";
} else {
w.style.display = "block";
setTimeout(function(){
document.getElementById("searchbox").focus()
}, 30);
}
}
function div_off(nm){document.getElementById(nm).style.display="none";}
window.onbeforeunload = function(e){div_off("submenu");}
/* Disable the Search feature if we are not operating from CGI, since */
/* Search is accomplished using CGI and will not work without it. */
if( !location.origin || !location.origin.match || !location.origin.match(/http/) ){
document.getElementById("search_menubutton").style.display = "none";
}
/* Used by the Hide/Show button beside syntax diagrams, to toggle the */
function hideorshow(btn,obj){
var x = document.getElementById(obj);
var b = document.getElementById(btn);
if( x.style.display!='none' ){
x.style.display = 'none';
b.innerHTML='show';
}else{
x.style.display = '';
b.innerHTML='hide';
}
return false;
}
var antiRobot = 0;
function antiRobotGo(){
if( antiRobot!=3 ) return;
antiRobot = 7;
var j = document.getElementById("mtimelink");
if(j && j.hasAttribute("data-href")) j.href=j.getAttribute("data-href");
}
function antiRobotDefense(){
document.body.onmousedown=function(){
antiRobot |= 2;
antiRobotGo();
document.body.onmousedown=null;
}
document.body.onmousemove=function(){
antiRobot |= 2;
antiRobotGo();
document.body.onmousemove=null;
}
setTimeout(function(){
antiRobot |= 1;
antiRobotGo();
}, 100)
antiRobotGo();
}
antiRobotDefense();
</script>
<div class=fancy>
<div class=nosearch>
<div class="fancy_title">
The CSV Virtual Table
</div>
</div>
<h1 id="overview"><span>1. </span>Overview</h1>
<p>
The CSV virtual table reads
<a href="https://www.ietf.org/rfc/rfc4180.txt">RFC 4180</a> formatted comma-separated
values, and returns that content as if it were rows and columns of an SQL
table.
</p>
<p>
The CSV virtual table is useful to applications that need to bulk-load
large amounts of comma-separated value content.
The CSV virtual table is also useful as a template source file for
implementing other virtual tables.
</p>
<p>
The CSV virtual table is not built into the SQLite amalgamation.
It is available as a
<a href="https://www.sqlite.org/src/artifact?ci=trunk&filename=ext/misc/csv.c">separate source file</a>
that can be compiled into a <a href="loadext.html">loadable extension</a>.
Typical usage of the CSV virtual table from the
<a href="cli.html">command-line shell</a> would be something like this:
</p><div class="codeblock"><pre>.load ./csv
CREATE VIRTUAL TABLE temp.t1 USING csv(filename='thefile.csv');
SELECT * FROM t1;
</pre></div>
<p>
The first line of the script above causes the <a href="cli.html">command-line shell</a> to
read and activate the run-time loadable extension for CSV. For an
application, the equivalent C-language API is
<a href="c3ref/load_extension.html">sqlite3_load_extension()</a>.
Observe that the filename extension (ex: ".dll" or ".so" or ".dylib") is
omitted from the extension filename. Omitting the filename extension is
not required, but it helps in making the script cross-platform. SQLite
will automatically append the appropriate extension.
</p><p>
The second line above creates a virtual table named "t1" that reads
the content of the file named in the argument. The number and names of
the columns is determined automatically by reading the first line of
content. Other options to the CSV virtual table provide the ability to
take the CSV content from a string rather than a separate file, and give
the programmer more control over the number and names of the columns.
The options are detailed below. The CSV virtual table is usually
created as a TEMP table so that it exists only for the current database
connection and does not become a permanent part of the database schema.
Note that there is no "CREATE TEMP VIRTUAL TABLE" command in SQLite.
Instead, prepend the "temp." schema prefix to the name of the virtual
table.
</p><p>
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 <a href="lang_with.html">common table expressions</a> or add WHERE, GROUP BY, HAVING, ORDER BY,
and LIMIT clauses as required.
</p><h1 id="arguments"><span>2. </span>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.
</p><ul>
<li><p><b>filename=</b><i>FILENAME</i>
</p><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.
</p></li><li><p><b>data=</b><i>TEXT</i>
</p><p>The <b>data=</b> argument specifies that <i>TEXT</i> is the literal
content of the CSV file.
</p></li><li><p><b>schema=</b><i>SCHEMA</i>
</p><p> The <b>schema=</b> argument specifies a <a href="lang_createtable.html">CREATE TABLE</a> statement that
the CSV virtual table passes to the <a href="c3ref/declare_vtab.html">sqlite3_declare_vtab()</a> interface in
order to define the names of the columns in the virtual table.
</p></li><li><p><b>columns=</b><i>N</i>
</p><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.
</p></li><li><p><b>header=</b><i>BOOLEAN</i><br>
or just<br>
<b>header</b>
</p><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.
</p></li></ul>
<h1 id="column_names"><span>3. </span>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 named "c0", "c1", "c2", and so forth.
</p><p align="center"><small><i>This page last modified on <a href="https://sqlite.org/docsrc/honeypot" id="mtimelink" data-href="https://sqlite.org/docsrc/finfo/pages/csv.in?m=5f8e2137c1">2022-01-08 05:02:57</a> UTC </small></i></p>
|
