Use special symbol for delimiter

oleg_lll

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.

Daniel_Leybovic

Hi!

You are missing something.

man ascii

\164 = "t"

Oct   Dec   Hex   Char
-----------------------
163   115   73    s
164   116   74    t
165   117   75    u

oleg_lll

Hi!
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.

Daniel_Leybovic

Hi!

>> 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.

I suggest you to use in \33 = ESCAPE, no way you have it in text, or in \36 and \37 .

       Oct   Dec   Hex   Char
    ---------------------------------------------
       036   30    1E    RS  (record separator)
       037   31    1F    US  (unit separator)

oleg_lll

Hello Daniel!

Thank you for help.
I'll try use symbol \33 = ESCAPE as delimiter.

Oleg.