Load Flat File using TDE 12.1.5.0

Teradata Applications
JKB
Fan

Load Flat File using TDE 12.1.5.0

#!/bin/ksh
TBUILD <<EOF
DEFINE JOB FILE_LOAD
DESCRIPTION 'Load a Teradata table from a file'
(
DEFINE SCHEMA Trans_n_Accts_Schema
(
Account_Number VARCHAR(50),
Trans_Number VARCHAR(50),
Trans_Date VARCHAR(50),
Trans_ID VARCHAR(50),
Trans_Amount VARCHAR(50)
);
 
DEFINE OPERATOR DDL_OPERATOR
TYPE DDL
ATTRIBUTES
(
VARCHAR PrivateLogName = 'ddl_log',
VARCHAR TdpId = 'tdeuat',
VARCHAR UserName = '****',
VARCHAR UserPassword = '****',
VARCHAR ErrorList = '3807'
);
 
DEFINE OPERATOR FILE_READER
TYPE DATACONNECTOR PRODUCER
SCHEMA Trans_n_Accts_Schema
ATTRIBUTES
(
VARCHAR PrivateLogName = 'dataconnector_log',
VARCHAR DirectoryPath = 'C:\Users\mv118\Desktop\',
VARCHAR FileName = 'accounts.txt',
VARCHAR Format = 'Delimited',
VARCHAR OpenMode = 'Read',
VARCHAR TextDelimiter = '|'
);
 
DEFINE OPERATOR LOAD_OPERATOR
TYPE LOAD
SCHEMA *
ATTRIBUTES
(
VARCHAR PrivateLogName = 'load_log',
VARCHAR TdpId = 'tdeuat',
VARCHAR UserName = 'ln574',
VARCHAR UserPassword = 'ln574',
VARCHAR TargetTable = TDE_STAGE || '.FF_Trans',
VARCHAR LogTable = TDE_STAGE || '.LG_Trans',
VARCHAR ErrorTable1 = TDE_STAGE || '.ET_Trans',
VARCHAR ErrorTable2 = TDE_STAGE || '.UV_Trans'
);
 
STEP Setup_Tables
(
APPLY
('DROP TABLE ' || TDE_STAGE || '.ET_Trans;'),
('DROP TABLE ' || TDE_STAGE || '.UV_Trans;'),
('DROP TABLE ' || TDE_STAGE || '.FF_Trans;'),
('CREATE TABLE ' || TDE_STAGE
|| '.FF_Trans (Account_Number VARCHAR(50),
Trans_Number VARCHAR(50),
Trans_Date VARCHAR(50),
Trans_ID VARCHAR(50),
Trans_Amount VARCHAR(50));')
TO OPERATOR (DDL_OPERATOR);
);
 
STEP Load_Trans_Table
(
APPLY
('INSERT INTO ' || TDE_STAGE || '.FF_Trans(Account_Number,
Trans_Number,
Trans_Date,
Trans_ID,
Trans_Amount)
VALUES(:Account_Number,
:Trans_Number,
:Trans_Date,
:Trans_ID,
:Trans_Amount);')
TO OPERATOR (LOAD_OPERATOR[2])
 
SELECT * FROM OPERATOR(FILE_READER[2]);
);
);
EOF

Hi,

I have a requirment to Load Flat file using TDE. I tried to use a Script option in TDE and tried to execute the Above script using Scheduler in TDE. But it is not getting executed. Please let me know what i am missing here. Also, Let me know is there any option to add New Data Source fro Flat Files in TDE. Please Help!

Tags (2)
2 REPLIES
Teradata Employee

Re: Load Flat File using TDE 12.1.5.0

The first thing that jumps out is that tbuild doesn't read stdin for the script. You need to supply the script filename via the -f option on the tbuild command line. And you also presumably would need to use the -u or -v option to supply a value for TDE_STAGE.

JKB
Fan

Re: Load Flat File using TDE 12.1.5.0

DEFINE OPERATOR FILE_READER
TYPE DATACONNECTOR PRODUCER
SCHEMA Prod_Units_Schema
ATTRIBUTES
(
VARCHAR PrivateLogName = 'dataconnector_log',
VARCHAR DirectoryPath = @DATAFILES_PATH,
VARCHAR FileName = 'Production Units_Mapping Document.xlsx',
VARCHAR Format = 'Delimited',
VARCHAR OpenMode = 'Read',
VARCHAR TextDelimiter = 'TAB'
);

Thanks Fred!

I loaded a sample Text Pipe Delimited File using TPT. But, i am NOT able to load a Excel (xlsx) as Input File. I am getting the below Error.

"FILE _READER: TPT19134! ERROR! Delimited Data Parsing error: Column Lenght overflows in Row 1"

Below is the options i gave in the TPT script