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

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.

5 Replies to “How to load multiple files into one single Table using TPT in Teradata?”

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

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

Leave a Reply

Your email address will not be published. Required fields are marked *