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.
Use special symbol for delimiter
+1+1Hi!
You are missing something.
man ascii
\164 = "t"
Oct Dec Hex Char
-----------------------
163 115 73 s
164 116 74 t
165 117 75 u
You are missing something.
man ascii
\164 = "t"
Oct Dec Hex Char
-----------------------
163 115 73 s
164 116 74 t
165 117 75 u
+1Hi!
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.
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.
+1Hi!
>> So, i need use E'\244' for my delimiter symbol.
range = {000 - 177}
>> So, i need use E'\244' for my delimiter symbol.
range = {000 - 177}
DELIMITER
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. For more information, see Loading UTF-8 Format Data in the Administrator's Guide.
Oct Dec Hex Char
---------------------------------------------
036 30 1E RS (record separator)
037 31 1F US (unit separator)
+1Hello Daniel!
Thank you for help.
I'll try use symbol \33 = ESCAPE as delimiter.
Oleg.
Thank you for help.
I'll try use symbol \33 = ESCAPE as delimiter.
Oleg.
Sign up
Already have an account? Login
Welcome to the Rocket Forum!
Please log in or register:
Employee Login | Registration Member Login | RegistrationEnter your E-mail address. We'll send you an e-mail with instructions to reset your password.
77 4th Avenue
Waltham, MA 02451 USA
