How to set an ASCII or non printable character as DELIMITER

Moderator: NorbertKrupa

Post Reply
prathapmk
Newbie
Newbie
Posts: 4
Joined: Thu Sep 12, 2013 7:27 am

How to set an ASCII or non printable character as DELIMITER

Post by prathapmk » Thu Sep 12, 2013 11:52 am

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.

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1768
Joined: Sat Jan 21, 2012 4:58 am
Contact:

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

Post by JimKnicely » Thu Sep 12, 2013 12:27 pm

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)
Jim Knicely

Image

Note: I work for HPE. My views, opinions, and thoughts expressed here do not represent those of my employer.

prathapmk
Newbie
Newbie
Posts: 4
Joined: Thu Sep 12, 2013 7:27 am

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

Post by prathapmk » Wed Sep 18, 2013 9:15 am

thank you so much..i will update you if am still getting any issues..

Post Reply

Return to “Vertica Migration”