Problem with LOAD TABLE

[Former Member]

I'm loading a table and is giving error.

FILE:

4762613000|00131002|5600|        |01/01/1970|10/03/201409:40:57.000000|10/03/2014 10:14:24.000000|0|

4762613001|00131002|5601|        |01/01/1970|10/03/201409:43:46.000000|10/03/2014 10:14:24.000000|0|

4762613002|00131002|5605|        |01/01/1970|10/03/201409:47:25.000000|10/03/2014 10:14:24.000000|0|

TABLE:

create table T1 (

C1 numeric(12) null,

C2 varchar(8) null,

C3 numeric(12) null,

C4 varchar(8) null,

C5 date null,

c6 timestamp null,

C7 timestamp null,

C8 numeric(12) null)

LOAD TABLE T1 (C1, C2, C3, C4, C5 DATE ('MM/DD/YYYY'),  FILLER('|'), C6 DATETIME('MM/DD/YYYY hh:nn:ss.ssssss'), FILLER('|'), C7 DATETIME ('MM/DD/YYYY hh:nn:ss.ssssss'), C8, FILLER('|')) from '/file.txt' ESCAPES OFF STRIP OFF  DELIMITED BY '|' MESSAGE LOG '/msg.LOG' ROW LOG '/rowlog.LOG' ONLY LOG ALL;

The table is loaded, but the last column is null. In the file, the last column is number 0(zero).

*****************************************

In another test:

FILE(Without the | (pipe) in the end of the line)

4762613000|00131002|5600|        |01/01/1970|10/03/201409:40:57.000000|10/03/2014 10:14:24.000000|0

4762613001|00131002|5601|        |01/01/1970|10/03/201409:43:46.000000|10/03/2014 10:14:24.000000|0

4762613002|00131002|5605|        |01/01/1970|10/03/201409:47:25.000000|10/03/2014 10:14:24.000000|0

LOAD TABLE T1 (C1, C2, C3, C4, C5 DATE ('MM/DD/YYYY'),  FILLER('|'), C6 DATETIME ('MM/DD/YYYY hh:nn:ss.ssssss'), FILLER('|'), C7 DATETIME ('MM/DD/YYYY hh:nn:ss.ssssss'), C8) from '/file.txt' ESCAPES OFF STRIP OFF DELIMITED BY '|' MESSAGE LOG '/msg.LOG' ROW LOG '/rowlog.LOG' ONLY LOG ALL;

Does not return error, but does not load the table.

  Thanks!

[saroj_bagai]

Ok problem is with data file and load statement, filler ('|')  cannot be pipe filler is the filler width e.g

filler (5)

in datafile for column6:

10/03/201409:40:57.000000

should  be

10/03/2014 09:40:57.000000

I tested loading data using your ddl and data file and it works. correct load table statement:

cat loadtab.sql

set temporary option timestamp_format='MM/DD/YYYY hh:nn:ss.ssssss' ;

SET TEMPORARY OPTION NON_ANSI_NULL_VARCHAR = 'ON';

SET TEMPORARY OPTION LOAD_ZEROLENGTH_ASNULL = 'ON';

set temporary option date_format='MM/DD/YYYY';

set temporary option date_order='MDY';

LOAD TABLE T1 (C1, C2, C3, C4 NULL(BLANKS) , C5 ,  C6 ,  C7  , C8) from '/work/demo1010/loaddat.txt' ESCAPES OFF STRIP OFF  DELIMITED BY '|'  row delimited by '\n' format bcp MESSAGE LOG '/work/demo1010/msg.LOG' ROW LOG '/work/demo1010/rowlog.LOG' ONLY LOG ALL;

[Former Member]

Saroj Hello, again!

Thank you very much for your help.

I believe I was going for the more complex way.

In each column that accepts null I can put 'NULL (BLANKS)', right?

I had not found the options: "set temporary option" in the manual. There are some more options such as "set temporary option" in some documentation that I can study?

Thanks for your help again!

[markmumy]

So long as your null data is represented as spaces, then yes, NULL(blanks) will work.

You also get more creative with it:

     NULL( blanks, zeros, 'NULL_VALUE', 'NULL', '<NULL>' )

Anything inside single quotes is a literal string, so NULL, <NULL>, and NULL_VALUE would be literals in the data.

Word of caution, beware of using NULL() with dates.  The DATE() and NULL() operators for load table can get tricky.

Mark