1

I m looking to do a batch load into a table, called temp_data, where some of the columns are NULLable dates.

Here is what I have till now:

LOAD TABLE some.temp_data 
(SomeIntegerColumn ',', SomeDateColumn DATE('YYYYMMDD') NULL('NULL'), FILLER(1), SomeStringColumn ',') 
USING CLIENT FILE '{0}' ESCAPES OFF DELIMITED BY ',' ROW DELIMITED BY '#'

and I m trying to load the following file

500,NULL,Monthly#
500,NULL,Monthly#
500,NULL,Monthly#

Unfortunately the error I get is:

ERROR [07006] [Sybase][ODBC Driver][Sybase IQ]Cannot convert NULL,Mon to a date (column SomeDateColumn)

Any ideas why this wouldn't work?

Rob
  • 14,746
  • 28
  • 47
  • 65
user2624034
  • 77
  • 1
  • 6

1 Answers1

0

It appears that it's reading the 8 characters following the first delimiter and trying to interpret them as a date.

Try switching to FORMAT BCP. The following example could work on your sample file:

LOAD TABLE some.temp_data (
    SomeIntegerColumn
  , SomeDateColumn NULL('NULL')
  , SomeStringColumn
) 
USING CLIENT FILE '{0}'
ESCAPES OFF
DELIMITED BY ','
ROW DELIMITED BY '#'
FORMAT BCP

In addition,FORMAT BCP also has the advantage of not requiring trailing delimiters.

stevepastelan
  • 1,264
  • 7
  • 11