How to load multiple files into one single Table using TPT in Teradata?

by

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:

  1. TPT_Name_LOAD.tpt: This is the main TPT file which will hold code to load DATA FILES into TARGET table.
  2. TPT_Variables.txt: This is the variable file in which we will define all the parameters used in TPT script.
  3. FILELIST.TXT: This is the file which will hold name of all the DATA FILES in it.
  4. emp.csv: First Data File.
  5. emp1.csv: Second Data File.
  6. 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?”

  1. 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 ?

    Reply

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

    Reply

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

    Reply

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

    Reply

  6. I am new to TPT ,not able to understand how to use TPT Wizard available , can we write and execute the scripts there itself

    Reply

Leave a Reply to Chris Jones Cancel reply