Load Integer values in Teradata MLOAD

Tools & Utilities
Enthusiast

Load Integer values in Teradata MLOAD

Hi,

 

I am trying to load a table using the below MLOAD script:

 

.LOGTABLE tduser.Logtable;  
.LOGON host/user,password; 
   .BEGIN MLOAD TABLES Employee_Stg;  
      .LAYOUT Employee;  
      .FIELD in_EmployeeNo * INTEGER;  
      .FIELD in_FirstName * VARCHAR(30); 
      .FIELD in_LastName * VARCHAR(30);  
      .FIELD in_BirthDate * VARCHAR(30); 
      .FIELD in_JoinedDate * VARCHAR(30);  
      .FIELD in_DepartmentNo * VARCHAR(10);

      .DML LABEL EmpLabel; 
      INSERT INTO Employee_Stg (
         EmployeeNo,
         FirstName,
         LastName,
         BirthDate,
         JoinedDate,
         DepartmentNo
      )  
      VALUES (
         :in_EmployeeNo,
         :in_FirstName,
         :in_Lastname,
         :in_BirthDate,
         :in_JoinedDate,
         :in_DepartmentNo
      );
      .IMPORT INFILE employee.txt  
      FORMAT VARTEXT ','      LAYOUT Employee      APPLY EmpLabel;  
   .END MLOAD;  
LOGOFF;

The table is created as below:
Create table
Employee_Stg
(
EmployeeNo INTEGER,
FirstName varchar(30),
LastName varchar(30),
BirthDate varchar(30),
JoinedDate varchar(30),
DepartmentNo varchar(30)
);

The sample data is created using a TEXT editor NOTEPAD++

101,Mike,James,1980-01-05,2010-03-01,1
102,Robert,Williams,1983-03-05,2010-09-01,1

When I try to execute this script it is throwing error as
"The Type for field
EmployeeNo is not supported for vartext format"

i have tried using other formats like FASTLOAD but it did not solve the problem.My source is comma separated text file.
Could anyone suggest any workaround for this since the field will be integer.


Regards,
Indranil Roy



3 REPLIES
Junior Contributor

Re: Load Integer values in Teradata MLOAD

When you define VARTEXT all input columns must be defined as VARCHAR:

 

 

  .FIELD in_EmployeeNo * VARCHAR(11); 

 

 

 

Teradata Employee

Re: Load Integer values in Teradata MLOAD

The .LAYOUT describes the inputj fields, not the table column.

 

.FIELD in_EmployeeNo * VARCHAR(10);

Enthusiast

Re: Load Integer values in Teradata MLOAD

Thanks for the inputs.Declaring the data types as varchar and casting it to the respective data  Integer/date type works fine.