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.
How to set an ASCII or non printable character as DELIMITER
Moderator: NorbertKrupa
- JimKnicely
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: How to set an ASCII or non printable character as DELIMI
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:
Now I can load the data with the COPY command:
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)
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
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)
Jim Knicely
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Re: How to set an ASCII or non printable character as DELIMI
thank you so much..i will update you if am still getting any issues..