Load Data Enclosed By and Embedded With Double Quotes
Jim_Knicely
- Select Field - Administrator
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.
Example:
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));
CREATE TABLE
dbadmin=> COPY TEST FROM '/home/dbadmin/test.txt' ENCLOSED BY '"' REJECTED DATA TABLE test_bad;
Rows Loaded
-------------
0
(1 row)
dbadmin=> SELECT rejected_reason FROM test_bad;
rejected_reason
------------------------
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
(1 row)
dbadmin=> SELECT * FROM test;
index | client_quote | version
-------+---------------------------------------------------+---------
1 | Vertica just keeps getting "BETTER" and "BETTER"! | 9.3
(1 row)
Helpful Links:
https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/SQLReferenceManual/Statements/COPY/COPYParameters.htm
https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/AdministratorsGuide/BulkLoadCOPY/IgnoringColumnsAndFieldsInTheLoadFile.htm
https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/SQLReferenceManual/Functions/String/TRIM.htm
0