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



5 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.

Enthusiast

Re: Load Integer values in Teradata MLOAD

Hi,

 

How to import the data which has INTEGER, VARCHAR and FLOAT data types? 

 

.LAYOUT STORE;
.FIELD STORE_ID * INTEGER;
.FIELD STORE_NAME * VARCHAR(30);
.FIELD VOL * FLOAT(4,0);

 

.IMPORT INFILE store.csv
FORMAT VARTEXT ','
LAYOUT STORE

 

since VARTEXT works only when it is all VARCHAR, so this fails, what is the substitue for this to import data that is in CSV file in the defined format, can you help me out?

 

 

Teradata Employee

Re: Load Integer values in Teradata MLOAD

Please re-read the above posts. The .LAYOUT does not describe the target columns, it must describe the source file. If that file is CSV and read with FORMAT VARTEXT, then all the .FIELD definitions will be VARCHAR. Teradata will implicitly try to CAST the input to match the target column data types, or you can apply a FORMAT / explicit CAST or other function if the character data does not conform to the default format of the target type.