Vertica client driver: Load data to temp table

I am using Vertica client driver version 8.1 to save data to temptable using the following query :
COPY TempTableName FROM STDIN DIRECT DELIMITER '|' RECORD TERMINATOR '\r\n' NO ESCAPE NO COMMIT;

while using this statement to add data to temp table I get a error if I have used "|" in the data to saved.

One of the ways to fix this is using ENCLOSED BY paramater like :
COPY TempTableName FROM STDIN DIRECT DELIMITER '|' RECORD TERMINATOR '\r\n' ENCLOSED BY '"' NO ESCAPE NO COMMIT;

But in this case if I used " in the data to be saved it leads to an error. This there are a generic way to handle this and insert all possible characters ('",.;/:?~!@#$%^&*|\/) on keyboard while saving.

Tagged:

Answers

  • Jim_KnicelyJim_Knicely Administrator

    Your situation is the reason my friend Maurizio Felici avoids using printable characters (commas, new-lines,…) as Field/Record separators :)

    See: https://www.linkedin.com/pulse/vertica-vsql-tips-tricks-maurizio-felici/

    Check the "Exporting data to file (the safe way)" section.

    Is it possible to have the data include an "ASCII value of 1 (CTRL-A)" as the field separator and an "ASCII value 2 (CTRL-B)" for the record separator?

    It'll be much easier to load!

    Thanks!

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file