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
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
|
# name: test/sql/copy/csv/test_copy.test
# description: Test copy statement
# group: [csv]
statement ok
PRAGMA enable_verification
# generate CSV file with ',' as delimiter and complex strings
# load CSV file into a table
statement ok
CREATE TABLE test (a INTEGER, b INTEGER, c VARCHAR(10));
query I
COPY test FROM '{DATA_DIR}/csv/test/test.csv';
----
5000
query IR
SELECT COUNT(a), SUM(a) FROM test;
----
5000 12497500
query IIT
SELECT * FROM test ORDER BY 1 LIMIT 3;
----
0 0 test
1 1 test
2 2 test
# create CSV file from table
query I
COPY test TO '{TEMP_DIR}/test2.csv';
----
5000
# load the same CSV file back again
statement ok
CREATE TABLE test2 (a INTEGER, b INTEGER, c VARCHAR(10));
query I
COPY test2 FROM '{TEMP_DIR}/test2.csv' ;
----
5000
query IIT
SELECT * FROM test2 ORDER BY 1 LIMIT 3;
----
0 0 test
1 1 test
2 2 test
# test too few rows
statement ok
CREATE TABLE test_too_few_rows (a INTEGER, b INTEGER, c VARCHAR, d INTEGER);
statement error
COPY test_too_few_rows FROM '{TEMP_DIR}/test2.csv' (NULL_PADDING 0);
----
It was not possible to automatically detect the CSV parsing dialect
# create CSV file from query
query I
COPY (SELECT a,b FROM test WHERE a < 4000) TO '{TEMP_DIR}/test3.csv';
----
4000
# load the same CSV file back again
statement ok
CREATE TABLE test3 (a INTEGER, b INTEGER);
query I
COPY test3 FROM '{TEMP_DIR}/test3.csv';
----
4000
query II
SELECT * FROM test3 ORDER BY 1 LIMIT 3;
----
0 0
1 1
2 2
# export selected columns from a table to a CSV file
query I
COPY test (a,c) TO '{TEMP_DIR}/test4.csv' (DELIMITER ',', HEADER false);
----
5000
# import selected columns from CSV file
statement ok
CREATE TABLE test4 (a INTEGER, b INTEGER, c VARCHAR(10));
query I
COPY test4 (a,c) FROM '{TEMP_DIR}/test4.csv' (DELIM ',');
----
5000
query IIT
SELECT * FROM test4 ORDER BY 1 LIMIT 3;
----
0 NULL test
1 NULL test
2 NULL test
# unsupported type for HEADER
statement error
COPY test4 (a,c) FROM '{TEMP_DIR}/test4.csv' (SEP ',', HEADER 0.2);
----
"HEADER" expected an argument of type BOOLEAN
# empty delimiter
statement error
COPY test4 (a,c) FROM '{TEMP_DIR}/test4.csv' (SEP);
----
"SEP" requires an argument of type VARCHAR
# number as delimiter
statement error
COPY test4 (a,c) FROM '{TEMP_DIR}/test4.csv' (SEP 1);
----
"SEP" expected an argument of type VARCHAR
# multiple format options
statement error
COPY test4 (a,c) FROM '{TEMP_DIR}/test4.csv' (FORMAT 'csv', FORMAT 'some_other_copy_function');
----
duplicate option
statement error
COPY test4 (a,c) FROM '{TEMP_DIR}/test4.csv' (FORMAT 'some_other_copy_function', FORMAT 'csv');
----
duplicate option
# number as escape string
statement error
COPY test4 (a,c) FROM '{TEMP_DIR}/test4.csv' (ESCAPE 1);
----
"ESCAPE" expected an argument of type VARCHAR
# no escape string
statement error
COPY test4 (a,c) FROM '{TEMP_DIR}/test4.csv' (ESCAPE);
----
"ESCAPE" requires an argument of type VARCHAR
# number as quote string
statement error
COPY test4 (a,c) FROM '{TEMP_DIR}/test4.csv' (QUOTE 1);
----
"QUOTE" expected an argument of type VARCHAR
# no quote string
statement error
COPY test4 (a,c) FROM '{TEMP_DIR}/test4.csv' (QUOTE);
----
"QUOTE" requires an argument of type VARCHAR
# no format string
statement error
COPY test4 (a,c) FROM '{TEMP_DIR}/test4.csv' (FORMAT);
----
Unsupported parameter type for FORMAT: expected e.g. FORMAT 'csv', 'parquet'
# encoding must not be empty and must have the correct parameter type and value
statement error
COPY test4 (a,c) FROM '{TEMP_DIR}/test4.csv' (ENCODING);
----
"ENCODING" requires an argument of type VARCHAR
statement error
COPY test4 (a,c) FROM '{TEMP_DIR}/test4.csv' (ENCODING 42);
----
"ENCODING" expected an argument of type VARCHAR
statement error
COPY test4 (a,c) FROM '{TEMP_DIR}/test4.csv' (ENCODING 'utf-42');
----
The CSV Reader does not support the encoding: "utf-42"
# don't allow for non-existant copy options
statement error
COPY test4 (a,c) FROM '{TEMP_DIR}/test4.csv' (MAGIC '42');
----
Unrecognized option "MAGIC"
# Try new_line option
query I
COPY test TO '{TEMP_DIR}/test_crlf.csv' (new_line '\r\n');
----
5000
query I
select count(*) from '{TEMP_DIR}/test_crlf.csv'
----
5000
# Try CR LF lines
query I
COPY test TO '{TEMP_DIR}/test_r.csv' (new_line '\r');
----
5000
query I
select count(*) from '{TEMP_DIR}/test_r.csv'
----
5000
query I
COPY test TO '{TEMP_DIR}/test_n.csv' (new_line '\n');
----
5000
query I
select count(*) from '{TEMP_DIR}/test_n.csv'
----
5000
query I
COPY test TO '{TEMP_DIR}/test_crlfe.csv' (new_line e'\r\n');
----
5000
query I
select count(*) from '{TEMP_DIR}/test_crlfe.csv'
----
5000
query I
COPY test TO '{TEMP_DIR}/test_re.csv' (new_line e'\r');
----
5000
query I
select count(*) from '{TEMP_DIR}/test_re.csv'
----
5000
query I
COPY test TO '{TEMP_DIR}/test_en.csv' (new_line e'\n');
----
5000
query I
select count(*) from '{TEMP_DIR}/test_en.csv'
----
5000
# use a different delimiter
# create new table
statement ok
DROP TABLE test;
statement ok
CREATE TABLE test (a INTEGER, b INTEGER, c VARCHAR(10));
query I
COPY test FROM '{DATA_DIR}/csv/test/test_pipe.csv' (SEPARATOR '|');
----
10
# throw exception if a line contains too many values
statement error
COPY test FROM '{DATA_DIR}/csv/test/too_many_values.csv';
----
It was not possible to automatically detect the CSV parsing dialect
# test default null string
query I
COPY test FROM '{DATA_DIR}/csv/test/test_null_csv.csv' DELIMITER '|';
----
1
# test invalid UTF-8
statement error
COPY test FROM '{DATA_DIR}/csv/test/invalid_utf.csv' DELIMITER '|';
----
Invalid unicode (byte sequence mismatch) detected.
# empty file
statement ok
CREATE TABLE empty_table (a INTEGER, b INTEGER, c VARCHAR(10));
statement error
COPY empty_table FROM '{DATA_DIR}/csv/test/empty.csv' (HEADER 0);
----
It was not possible to automatically detect the CSV parsing dialect
# unterminated quotes
statement ok
CREATE TABLE unterminated (a VARCHAR);
statement error
COPY unterminated FROM '{DATA_DIR}/csv/test/unterminated.csv' (HEADER 0, AUTO_DETECT FALSE, strict_mode TRUE);
----
Value with unterminated quote found.
# 1024 rows (vector size)
# load CSV file into a table
statement ok
CREATE TABLE vsize (a INTEGER, b INTEGER, c VARCHAR(10));
query I
COPY vsize FROM '{DATA_DIR}/csv/test/vsize.csv';
----
1024
|
