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



  • Options

    You are missing something.

    man ascii

    \164 = "t"

    Oct   Dec   Hex   Char
    163   115   73    s
    164   116   74    t
    165   117   75    u
  • Options
    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.


  • Options

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

  • Options
    Hello Daniel!

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


Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file