Options
Use special symbol for delimiter
I need load data from mssql db into Vertica.
Mssql data has symbol "|" and I want use special symbol(code 164) for delimiter for Copy command.
For uploading data into file i use such command
cat get_SCR_TREATYSTATES.sql | isql -v web-mssql vertica fff -b -x0x0a4 > SCR_TREATYSTATES.csv
All is ok - i can see special symbol in file.
Then i use such command to load data into Vertica
vsql -w Vertica -c "copy stage.SCR_TREATYSTATES from local '/tmp/test_load/SCR_TREATYSTATES.csv' delimiter E'\164' null as '' rejected data '/tmp/test_load/TREATYSTATES.csv.bad' EXCEPTIONS '/tmp/test_load/SCR_TREATYSTATES.csv.ex' direct;"
I see all rows are rejected and copy command can't find delimiter(E'\164').
May be i use wrong parameters and etc...
Oleg.
Mssql data has symbol "|" and I want use special symbol(code 164) for delimiter for Copy command.
For uploading data into file i use such command
cat get_SCR_TREATYSTATES.sql | isql -v web-mssql vertica fff -b -x0x0a4 > SCR_TREATYSTATES.csv
All is ok - i can see special symbol in file.
Then i use such command to load data into Vertica
vsql -w Vertica -c "copy stage.SCR_TREATYSTATES from local '/tmp/test_load/SCR_TREATYSTATES.csv' delimiter E'\164' null as '' rejected data '/tmp/test_load/TREATYSTATES.csv.bad' EXCEPTIONS '/tmp/test_load/SCR_TREATYSTATES.csv.ex' direct;"
I see all rows are rejected and copy command can't find delimiter(E'\164').
May be i use wrong parameters and etc...
Oleg.
0
Comments
You are missing something.
man ascii
\164 = "t"
Oct Dec Hex Char
-----------------------
163 115 73 s
164 116 74 t
165 117 75 u
Yes you are right.
I think 164 is the Dec format.
I understand my mistake.
Thank you.
So, i need use E'\244' for my delimiter symbol.
Oleg.
>> So, i need use E'\244' for my delimiter symbol.
range = {000 - 177}
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 rangeE'\000'toE'\177'inclusive. You cannot use the same character for both the DELIMITER and NULL options. For more information, see Loading UTF-8 Format Data in the Administrator's Guide.
I suggest you to use in \33 = ESCAPE, no way you have it in text, or in \36 and \37 .Thank you for help.
I'll try use symbol \33 = ESCAPE as delimiter.
Oleg.