Skip to Content
avatar image
Former Member

Using LOAD TABLE to load data containing newline characters

Hi - I am trying to transfer data from one IQ server to another IQ server using LOAD TABLE statement. The data transfer is generally working fine except for one issue wherein data contains the linefeed characters. In some of our data columns, we do have newline characters and when extracted using OUTPUT statement, the newline characters are extracted with their hexadecimal codes such as "\x0d\x0a". I thought this was ok since this is the hexadecimal code for linefeed character. However, when I load the extracted data using the Load Table statement, the data is loaded as is with these hexadecimal codes. That is, the codes are not treated as special characters and are inserted as is instead of linefeed characters. After reading the help for Load Table I thought turning on the ESCAPES option ON would help, but for Sybase IQ, you must set ESCAPES OFF. Please let me know if you have any idea on how to get around this issue.

Add comment
10|10000 characters needed characters exceeded

  • Former Member

    can you share the statement? Also please check if you are using column-spec under 'load specification: { column-name [ column-spec ] }' to identify the data in each column or field of source file. Please refer to below example where tab character is the default column delimiter, and the newline character is the row delimiter.

    LOAD TABLE product_new
    ( id,
    name,
    description,
    size,
    color '\x09' NULL( 'null', 'none', 'na' ),
    quantity PREFIX 2,
    unit_price PREFIX 2 )
    FROM '/s1/mydata/source2.dump',
    '/s1/mydata/source3.dump'
    QUOTES OFF
    ESCAPES OFF
    BLOCKSIZE 100000
    FORMAT ascii
    DELIMITED BY '\x09'
    ON FILE ERROR CONTINUE
    ROW DELIMITED BY '\x0a'

    Regards,

    Amit T

  • Get RSS Feed

0 Answers