Page 1 of 1

How to set an ASCII or non printable character as DELIMITER

Posted: Thu Sep 12, 2013 11:52 am
by prathapmk
I need to COPY data from SQL Server to Vertica. I need to delimit the records with an unique non printable, non-keyboard character to ensure the same doesn't exists in the data.

As per Vertica docs - "A single ASCII character that separates columns within each record of a file. The default in HP Vertica is a vertical bar (|). You can use any ASCII value in the range E'\000' to E'\177' inclusive. You cannot use the same character for both the DELIMITER and NULL options."

Kindly provide me with an example on how to set an ASCII charcater as delimiter.

Re: How to set an ASCII or non printable character as DELIMI

Posted: Thu Sep 12, 2013 12:27 pm
by JimKnicely
Hi,

Use the DELIMITER option of the COPY command to specify the delimiter using the escape string syntax...

Example:

I created a data text file called "/home/dbadmin/ascii_delim_test.txt" using an ESC ascii character as a field delimiter for three fields. This is the contents of the file as seen in the vi editor:

col1 data^[col2 data^[col3 data

In Vertica I created the following table to store the data:

Code: Select all

dbadmin=> create table ascii_delim_test (col1 varchar(100), col2 varchar(100), col3 varchar(100));
CREATE TABLE
Now I can load the data with the COPY command:

Code: Select all

dbadmin=> copy ascii_delim_test from '/home/dbadmin/ascii_delim_test.txt' delimiter E'\033';
 Rows Loaded
-------------
           1
(1 row)

dbadmin=> select * from ascii_delim_test;
   col1    |   col2    |   col3
-----------+-----------+-----------
 col1 data | col2 data | col3 data
(1 row)
Note that I used E'\033' as the delimiter because the octal value for the ascii ESC character is 033. (See http://www.asciitable.com)

Re: How to set an ASCII or non printable character as DELIMI

Posted: Wed Sep 18, 2013 9:15 am
by prathapmk
thank you so much..i will update you if am still getting any issues..