TPT Load Operator: How to handle numeric data in file that is larger than the target table column datatype

Tools & Utilities
N/A

TPT Load Operator: How to handle numeric data in file that is larger than the target table column datatype

Hi,

I am trying to load a file into a table using TPT Load operator. I am using TPT version 14.00.00.09. I am facing the following problem while loading the data. My target table has a column TRANS_NBR INTEGER NOT NULL and the file has data like 817911111111111 for this column.

My file has 20 records of which 4 rows have good values for TRANS_NBR, ,meaning the values are small enough for the target table column to handle.When I try to load the file using the below definitions the remaining 16 records are loaded to the error table while only 4 records get loaded into the table.

My question is can I load remaining 16 records by truncating the data for column TRANS_NBR or some other way without deleting these records from the file or modifying table definition.

I tried using attribute TruncateColumnData='Y' for the dataconnector operator. But it does not work. Please help

Below is my Schema & Operator Definations

DEFINE SCHEMA Trans_n_Accts_Schema

(

REGISTER_NBR VARCHAR(10),

STORE_NBR VARCHAR(10),

TRANS_NBR VARCHAR(20),

TRANS_LINE_NBR VARCHAR(20),

TRANS_DATE VARchar(20),

TRANS_TIME VARCHAR(15),

SOURCE_SYS_CD VARCHAR(30),

BELK_ITEM_NBR VARCHAR(20),

OPERATOR_ID VARCHAR(50),

POS_UNIT_RTL VARCHAR(10),

TENDER_TP_CD VARCHAR(20),

SLS_TP_CD VARCHAR(10),

SLS_UNITS VARCHAR(10),

SLS_DLRS VARCHAR(10),

POSTED_DATE VARCHAR(10),

POSTED_TIME VARCHAR(15),

SLS_TP_DTL_CD VARCHAR(10),

COUPON_NBR VARCHAR(15),

CPN_OVERRIDE_CD VARCHAR(10),

CPN_MKD_DLRS VARCHAR(10),

PLU_PRC VARCHAR(10),

PRC_ADJ_CD VARCHAR(10),

PRC_ADJ_MKD_DLRS VARCHAR(10),

REGISTRY_ID VARCHAR(10)

);

DEFINE OPERATOR FILE_READER

TYPE DATACONNECTOR PRODUCER

SCHEMA Trans_n_Accts_Schema

ATTRIBUTES

(

VARCHAR PrivateLogName,

VARCHAR DirectoryPath,

VARCHAR FileName,

VARCHAR Format,

VARCHAR OpenMode,

VARCHAR TextDelimiter,

VARCHAR TruncateColumnData,

VARCHAR AcceptExcessColumns,

VARCHAR TrimChar,

VARCHAR TrimColumns,

VARCHAR AcceptMissingColumns

);

DEFINE OPERATOR LOAD_OPERATOR

TYPE LOAD

SCHEMA Trans_n_Accts_Schema

ATTRIBUTES

(

VARCHAR PrivateLogName = 'load_log',

VARCHAR UserName = '@#$%^&',

VARCHAR UserPassword = '@#$%^&*(',

VARCHAR TdpId = '1234567890',

VARCHAR TargetTable = 'untuned_db_gn.ODS_MDSE_SLS_HIST_C' ,

VARCHAR LogTable = 'untuned_db_gn.ODS_MDSE_SLS_HIST_C_LG_Trans',

VARCHAR ErrorTable1 = 'untuned_db_gn.ODS_MDSE_SLS_HIST_C_ET_Trans',

VARCHAR ErrorTable2 = 'untuned_db_gn.ODS_MDSE_SLS_HIST_C_UV_Trans'

);

SELECT * FROM OPERATOR(FILE_READER

ATTR (PrivateLogName = 'dataconnector2_log',

FileName = 'C:\Gautam\Bench Marking\Assignment 4\load\data\d4.txt',

Format = 'Delimited',

OpenMode = 'Read',

TextDelimiter = '|',

TruncateColumnData='Y',

AcceptExcessColumns='Y',

TrimChar='"',

TrimColumns='Both',

AcceptMissingColumns='Y'

))

1st 2 lines of my file:

0430|353|817911111111111|6|01/18/2009|14:15:00.000000|SAN|400565651127.|10006|7.19|02|M|1|7.19|01/18/2009|21:30:04.000000|4|0.|0|.00|7.19|0|.00|

0015|61|817911111111111|1|09/02/2008|16:23:00.000000|SAN|400565464659.|01228|17.99|02|M|1|17.99|09/02/2008|23:57:02.000000|4|0.|0|.00|17.99|0|.00|

Tags (1)