Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

Load Data Enclosed By and Embedded With Double Quotes

Loading string data that is enclosed in double quotes where the string also contains double quotes can be problematic. We could alter the data so that it includes an escape character prior to each embedded double quote, but maybe that’s not possible. Another solution would be to use a FILLER to load the data then TRIM off the double quotes from the ends of the string.


dbadmin=> \! cat /home/dbadmin/test.txt
"1"|"Vertica just keeps getting "BETTER" and "BETTER"!"|9.3

dbadmin=> CREATE TABLE test (index INT, client_quote VARCHAR(100), version VARCHAR(5));

dbadmin=> COPY TEST FROM '/home/dbadmin/test.txt' ENCLOSED BY '"' REJECTED DATA TABLE test_bad;
Rows Loaded
(1 row)

dbadmin=> SELECT rejected_reason FROM test_bad;
Too many columns found
(1 row)

dbadmin=> COPY TEST FROM '/home/dbadmin/test.txt' ENCLOSED BY '"' ESCAPE '"' REJECTED DATA TABLE test_bad;
ERROR 3169:  ENCLOSED BY and ESCAPE AS can not be the same value

dbadmin=> COPY test(index, client_quote_f FILLER VARCHAR, client_quote AS TRIM(BOTH '"' FROM client_quote_f), version) FROM '/home/dbadmin/test.txt' REJECTED DATA TABLE test_bad;
Rows Loaded
(1 row)

dbadmin=> SELECT * FROM test;
index |                   client_quote                    | version
     1 | Vertica just keeps getting "BETTER" and "BETTER"! | 9.3
(1 row)

Helpful Links:

Sign In or Register to comment.

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.