Skip to Content
avatar image
Former Member

Problem with LOAD TABLE

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!

numeric.png (7.9 kB)
Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

1 Answer

  • Apr 01, 2016 at 07:50 PM

    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;

    Add comment
    10|10000 characters needed characters exceeded

    • 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