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
The sample data is created using a TEXT editor NOTEPAD++
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.
Thanks for the inputs.Declaring the data types as varchar and casting it to the respective data Integer/date type works fine.
How to import the data which has INTEGER, VARCHAR and FLOAT data types?
.FIELD STORE_ID * INTEGER;
.FIELD STORE_NAME * VARCHAR(30);
.FIELD VOL * FLOAT(4,0);
.IMPORT INFILE store.csv
FORMAT VARTEXT ','
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?
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.