All Forums Tools
novice 38 posts Joined 07/07
15 Jul 2008
ERROR Loading Pipe delimited file thorugh MLOAD

Hi All,I am struggling to load a simple pipe | delimited file into a table. The table is loaded but the UPI is populated with NULL or a different value.Table Structure ---------------CREATE SET TABLE DV1E_WORK_IN.emp1 ,NO FALLBACK , NO BEFORE JOURNAL, NO AFTER JOURNAL, CHECKSUM = DEFAULT ( EmpNo INTEGER, Empname VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC)UNIQUE PRIMARY INDEX ( EmpNo );Flat file being loaded-------------------- 456|ERere12345454|Prpr331|sasaMLOAD script--------------mload << EOF.logtable DV1E_WORK_IN.abclog;.Logon Nun015/d304128,"ssdsdssdsds";dROP TABLE DV1E_WORK_IN.EMP1_ERR1;dROP TABLE DV1E_WORK_IN.EMP1_ERR2;.begin import mload tables DV1E_WORK_IN.emp1WORKTABLES DV1E_WORK_IN.EMP1_WORKERRORTABLES DV1E_WORK_IN.EMP1_ERR1 DV1E_WORK_IN.EMP1_ERR2SESSIONS 2;.layout Insfilelayout;.field Empno * varchar(50);.field Empname * varchar(50); .dml label insertdml;insert into DV1E_WORK_IN.Emp1 (EmpNo,Empname) values(:Empno,:Empname);.import infile insertfile format vartext '|' layout Insfilelayout apply insertdml;.end mload;.logoff;EOFData loaded in Emp1 after mload execution--------------------------Empno Empname ------ ---------NULL ERere12345454 Prpr331 sasai have two questions-1) Why the empno for first record i.e. Empno 456 is getting populated as NULL? If all the other rows except the first row is being loaded corrrectly then what's wrong with the first record?2) It seems for FORMAT VARTEXT the layout should(not must) specify fields with datatype as VARCHAR. When target table contains Date/Datetime columns then how do we cast them? Can we do this in the DML statement?Thanks in advance...regards,Saur

j355ga 100 posts Joined 12/05
16 Jul 2008

First, I think you should be careful to always remove your id and password when sharing scripts.1. If the first record fails but all the other records are ok then there may be some unprintable character in that first column first row.2. You have to specify VARCHAR because the input file is ASCII characters. Datatype DATE is an internal format represented as a 4 byte binary data. So, mload reads the character field containing CHARACTERS (not numbers). Then these are cast internally as DATES. If your file were NOT character representations then you would have to specify the field as DATE, or INTEGER etc.You can prove this by trying a Fastexport. Create a table with a date column. Then use FastExport and specify FORMAT TEXT to extract. Next, try the same export but specify FORMAT BINARY.You will see that the characters are represented as ASCII in the FORMAT TEXT example and in the FORMAT BINARY the date is unreadable.

Jeff

novice 38 posts Joined 07/07
16 Jul 2008

Thanks a lot Jeff. I would try as suggested.The userId and pwd mentioned in the script were just Dummy..Cheers,Saur

You must sign in to leave a comment.