We recently received a comment on our previous TPT post,(Click here to read it) requesting for a sample TPT script to load multiple data files into one single Teradata Table using Load operator.The TPT script is simple and is very much similar to our TPT script we shared in earlier posts. The main component which will change is the PRODUCER operator. Here we'll use 2 new variables :
VARCHAR FileList = 'YES',
VARCHAR FileName = @File1
FileList : represent here that we are going to process list of files.
FileName: the Filename which has list of DATA files in it. One in each line.
So we will have following 6 files for this example:
- TPT_Name_LOAD.tpt: This is the main TPT file which will hold code to load DATA FILES into TARGET table.
- TPT_Variables.txt: This is the variable file in which we will define all the parameters used in TPT script.
- FILELIST.TXT: This is the file which will hold name of all the DATA FILES in it.
- emp.csv: First Data File.
- emp1.csv: Second Data File.
- emp2.csv: Third Data File.
Let us see now content of each file:
1) TPT_Name_LOAD.tpt
DEFINE JOB SAMPLE_FILE_LOAD
DESCRIPTION 'Load multiple files into one single table in Teradata'
(
DEFINE SCHEMA SCHEMA_EMP_NAME
(
emp_NAME VARCHAR(50),
AGE VARCHAR(500)
);
DEFINE OPERATOR op_EMP_NAME
DESCRIPTION 'TERADATA PARALLEL TRANSPORTER DATACONNECTOR PRODUCER'
TYPE DATACONNECTOR PRODUCER
SCHEMA SCHEMA_EMP_NAME
ATTRIBUTES
(
VARCHAR FileList = 'YES',
VARCHAR FileName = @File1,
VARCHAR Format = 'Delimited',
VARCHAR OpenMode = 'Read',
VARCHAR TextDelimiter ='|'
);
DEFINE OPERATOR od_EMP_NAME
DESCRIPTION 'TPT DDL OPERATOR FOR LOGIN DETAILS'
TYPE DDL
ATTRIBUTES
(
VARCHAR PrivateLogName = 'ddl_log',
VARCHAR LogonMech = @LogonMech,
VARCHAR TdpId = @Tdpid,
VARCHAR UserName = @Usrid,
VARCHAR UserPassword = @Psswd,
VARCHAR ErrorList = '3807'
);
DEFINE OPERATOR ol_EMP_NAME
DESCRIPTION 'TERADATA PARALLEL TRANSPORTER DATACONNECTOR LOAD OPERATOR'
TYPE LOAD
SCHEMA *
ATTRIBUTES
(
VARCHAR LogonMech = @LogonMech,
VARCHAR TdpId = @Tdpid,
VARCHAR UserName = @Usrid,
VARCHAR UserPassword = @Psswd,
VARCHAR LogTable = 'EMP_NAME_LG',
VARCHAR ErrorTable1 = 'EMP_NAME_ET',
VARCHAR ErrorTable2 = 'EMP_NAME_UV',
VARCHAR TargetTable = 'EMP_NAME'
);
STEP stSetup_Tables
(
APPLY
('DROP TABLE EMP_NAME_LG;'),
('DROP TABLE EMP_NAME_ET;'),
('DROP TABLE EMP_NAME_UV;'),
('DROP TABLE EMP_NAME;'),
('CREATE TABLE EMP_NAME(NAME VARCHAR(50), AGE VARCHAR(500));')
TO OPERATOR (od_EMP_NAME);
);
STEP stLOAD_FILE_NAME
(
APPLY
('INSERT INTO EMP_NAME(Name,Age)
VALUES(:emp_Name,:Age);
')
TO OPERATOR (ol_EMP_NAME)
SELECT * FROM OPERATOR(op_EMP_NAME);
);
);2) TPT_Variables.txt
Tdpid='', LogonMech='', Usrid='', Psswd='', File1='FILELIST.TXT'
3) FILELIST.TXT
emp.csv emp1.csv emp2.csv
4) emp.csv
Pat|26
5) emp1.csv
Mark|48
6) emp2.csv
Nitin|29
Place all the 6 files in a directory and run below command:
tbuild -f TPT_Name_LOAD.tpt -v TPT_Variables.txt
Note: Each of the 3 Data Files have 1 record only.
Once the script executes successfully run below command in Teradata:
SELECT * from EMP_NAME;
Output should be 3 rows one from each file.
8 thoughts on “How to load multiple files into one single Table using TPT in Teradata?”
what happens if the files have headers and you want to disregard it , can it skip the header for each of the files or does the file have to have no headers to start with in order to load all into one table ?
Hi Raj,
Thanks for helping us in TPT.
I have a small doubt, is it possible to use the Bteq commands like activitycount, errorcount etc in DDL operator.
Could you also please provide the list of Operators that support tpt and its descriptions.
I savour, lead to I discovered just what I was having a look for. You have ended my 4 day long hunt! God Bless you man. Have a great day. Bye|
Hi Raj
Using TPT after exporting to a file from a table ,can we also send that file to another location , say from mainframe server to windows server.
Hi Arjun
Yes you can send the file to any location.
However you may have to use some FTP/SFTP for that.
Thanks for your effort ,l have searshed for the same issue a lot ,thanks a lot
Asma
Happy to help.
I am new to TPT ,not able to understand how to use TPT Wizard available , can we write and execute the scripts there itself