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 -----BEGIN PGP SIGNATURE----- Version: GnuPG v2 iQEcBAEBCAAGBQJXWq9HAAoJEISMxT6LrWYguvsH+gO/XQcH5eHUHQO7ivZlyUy+ HJUnJzggTw+y7R3XZOPFyrONt5sugAF3G4XS8gkjeAOv77VBwiroLoxmTMa7I9Wu uNJSStwwieGHkTXaWwQvKjprWACHr5/X/C4vNCqTkjxPgBryg60+Www0il9Ow5pG ca2QKfIOs3feUdP3FAgMSVYHWjGZ8v/rGoM5uIoOQs2zmMbwZES4w0HAMMDzd1gs HJz+FuY/He+EJ0d1MesmSiPdgiHSVaMVOfWWvLaYA/7iuadQsAqXwIT4GdbsRZ4b ppgltEHaeDQDWsr1ML/9sH+AIQftygo1Qf6WYqOaQZGeLiRNtUW6Y5/u5phZxRQ= =X4m2 -----END PGP SIGNATURE-----
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