Debian Codegraph @

www/csv.html

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