ERROR Loading Pipe delimited file thorugh MLOAD

Tools
Enthusiast

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|ERere
12345454|Prpr
331|sasa

MLOAD 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.emp1
WORKTABLES DV1E_WORK_IN.EMP1_WORK
ERRORTABLES DV1E_WORK_IN.EMP1_ERR1 DV1E_WORK_IN.EMP1_ERR2
SESSIONS 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;
EOF

Data loaded in Emp1 after mload execution
--------------------------
Empno Empname
------ ---------
NULL ERere
12345454 Prpr
331 sasa

i 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
2 REPLIES
Teradata Employee

Re: ERROR Loading Pipe delimited file thorugh MLOAD

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.

Enthusiast

Re: ERROR Loading Pipe delimited file thorugh MLOAD

Thanks a lot Jeff. I would try as suggested.

The userId and pwd mentioned in the script were just Dummy..

Cheers,
Saur