Load Table not working

[Former Member]

Hi,

Is there a limit on number of columns that I can enter for load table query?

I am trying to load a file with 10 rows and 105 columns.

I am getting following error -

SQL Anywhere Error -1013028: An invalid size (too large) was specified for a LOAD option.

-- (db_sqlins.cxx 5113)

Please help.

[markmumy]

Can you provide more details?  CREATE TABLE, LOAD TABLE, and sample data would be best.

It is likely a mismatch between the data and the LOAD statement.

Mark

[Former Member]

Hi Mark,

Load table statements is as follows

set temporary option ESCAPE_CHARACTER='ON';

LOAD TABLE dc.tablename(c1,c2,c3...c104,c104)

from '/home/dcuser/import1.csv'

ESCAPES OFF

QUOTES OFF

DELIMITED BY '\x124'

ROW DELIMITED BY '\n'

WITH CHECKPOINT OFF ;

I wont be able to send the exact file as it has Customer data. But it has following format which is exactly same as it would be if I export table data to csv.

column Header

Column values

How can I get create table definition in Sybase?

[tayeb_hadjou]

Hi,

The error is related to "Delimited By". It should be a SINGLE character.

From IQ manuals:

"DELIMITED BY –  You can specify an alternative column delimiter by providing a SINGLE ASCII character or the hexadecimal character representation."

To check that, execute query

select char_length('\x124') from iq_dummy;

In your case, It gives 2. Thus it violates the restriction above.

Regards,

Tayeb.

[tayeb_hadjou]

See also related SAP KBA 2023723 :

https://service.sap.com/sap/support/notes//2023723

[Former Member]

Thanks Tayeb.

But I have pipe as my column separator .

If I change to

DELIMITED BY '|'

I am getting below error -

  [Error Code: 21, SQL State: QCA62]  SQL Anywhere Error -1013062: Expected 'column' delimited column type on input, but encountered a row delimiter instead. Probable cause is a mismatch between the specified column delimiter and the column delimiter used in the input file.

Any idea?

[Former Member]

Just to add, as some column values contain comma.

I have changed delimiter to pipe.

[tayeb_hadjou]

In data file, last field of each row shoud be also delimited by '|', unless you use LOAD with  "format BCP" :

•For FORMAT BCP, the default column delimiter for the LOAD TABLE statement is <tab> and the default row terminator is <newline>.

•For FORMAT BCP, the last column in a row must be terminated by the row terminator, not by the column delimiter. If the column delimiter is present before the row terminator,then the column delimiter is treated as a part of the data.

[Former Member]

Hi Tayeb,

I tried adding '|' for last field.. but same error.

Regarding BCP, I dont know how to use this utlity in Sybase.

Not sure, if I have this utility in my unix system?

[tayeb_hadjou]

"Format bcp" is an option for LOAD table statement.
:

Load table ...

(...

)

from ..

ESCAPES OFF

QUOTES OFF

....

Format BCP

....

Load statement

http://help.sap.com/saphelp_iq1608_iqrefso/helpdata/en/a6/209de484f21015bcb2d858c21ab35e/frameset.ht...

[Former Member]

Thanks all for the help.

There was a problem with the format of Date column mismatch.

Got it working now.