copy into from csv with delimiters in quoted field
Hello,
i have an issue with the "copy into" statement and a csv file. In the documentation[1] it says: "Field and record separators can be embedded in quoted fields." This does not work in v11.21.19.
example.csv: 1,",,",42,, 2,",,",23,,
create table issue(id int, str varchar(32), val int, empty int); copy into issue from 'example.csv' using delimiters ',' NULL AS '';
The first line with the escaped delimiters works, the second does not: Failed to import table Leftover data '23,,'
Is this a bug i should report or am i doing something wrong with the above statements?
Regards, Frank
[1] https://www.monetdb.org/Documentation/Manuals/SQLreference/CopyInto
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA256
On 10/06/16 13:44, Frank Tetzel wrote:
Hello,
i have an issue with the "copy into" statement and a csv file. In the documentation[1] it says: "Field and record separators can be embedded in quoted fields." This does not work in v11.21.19.
example.csv: 1,",,",42,, 2,",,",23,,
create table issue(id int, str varchar(32), val int, empty int); copy into issue from 'example.csv' using delimiters ',' NULL AS '';
Use:
copy into issue from 'example.csv' using delimiters ',','\n','"' NULL AS '';
You need to say that you're using quoted strings in the input (the third delimiter). And in order to specify the third delimiter, you need to specify the second as well.
The first line with the escaped delimiters works, the second does not: Failed to import table Leftover data '23,,'
Is this a bug i should report or am i doing something wrong with the above statements?
Regards, Frank
[1] https://www.monetdb.org/Documentation/Manuals/SQLreference/CopyInto
_______________________________________________
users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
- -- Sjoerd Mullender
Hi,
i have an issue with the "copy into" statement and a csv file. In the documentation[1] it says: "Field and record separators can be embedded in quoted fields." This does not work in v11.21.19.
example.csv: 1,",,",42,, 2,",,",23,,
create table issue(id int, str varchar(32), val int, empty int); copy into issue from 'example.csv' using delimiters ',' NULL AS '';
Use:
copy into issue from 'example.csv' using delimiters ',','\n','"' NULL AS '';
You need to say that you're using quoted strings in the input (the third delimiter). And in order to specify the third delimiter, you need to specify the second as well.
thank you for the quick responds. It works like a charm. Kind of surprising that escaping the delimiters works then.
Regards, Frank
participants (2)
-
Frank Tetzel
-
Sjoerd Mullender