TPT script to load data with embedded qualifiers

Tools & Utilities
Highlighted
Enthusiast

TPT script to load data with embedded qualifiers

Hello,

We are trying to load a pipe delimited flat with qulaifiers as " utlizing the TPT script but our loads seems to be failing since there is a embedded " in certain fields. Its just sending the error record to a error file and failing after that. Do we have some option or add on in the TPT script to load this data as is.

We are on teradata 14.00 with the updated tools & utilities.

Any help would be appreciated.

Thanks.

Tags (3)
9 REPLIES
Teradata Employee

Re: TPT script to load data with embedded qualifiers

If your data is not quoted, we will process everything in between the delimiters as data.

If you are using the delimited feature "QuotedData" and you have embedded quotes inside the quoted string, then those embedded quotes have to be escaped.

-- SteveF
Enthusiast

Re: TPT script to load data with embedded qualifiers

Hi, Do you know a way how it can be espaced in script. Are you sugessting to use some feature outside TPT to do that ?

Just for your reference I am adding the script attributes currently that are set :

  DEFINE OPERATOR File_Reader

  TYPE DATACONNECTOR PRODUCER

  SCHEMA MySchema1

  ATTRIBUTES

  (

    VARCHAR PrivateLogName = 'producer_log',

    VARCHAR DirectoryPath = 'XXXXXXX',

    VARCHAR FileName = 'XXXXXX',

    VARCHAR QuotedData = 'Optional',   

    VARCHAR OpenQuoteMark = '"',

    VARCHAR CloseQuoteMark = '"',

    VARCHAR TrimColumns='Both',

    VARCHAR FORMAT         = 'Delimited',

    VARCHAR TextDelimiter      = '|',

    VARCHAR RowErrFileName = 'XXXXX',

    VARCHAR OpenMode        = 'read'

  );

  DEFINE OPERATOR Load_Operator

  TYPE Load

  SCHEMA MySchema1

  ATTRIBUTES

  (

    VARCHAR DateForm = 'IntegerDate',

    VARCHAR ErrorTable1 = 'TMP_PHARM_errors1',

    VARCHAR ErrorTable2 = 'TMP_PHARM_errors2',

    VARCHAR LogTable = '"XXXXXX"',

    VARCHAR PrivateLogName = 'consumer_log',

    VARCHAR TargetTable = '"XXXXXX"',

    VARCHAR TdpId = @MyDstTdpId,

    VARCHAR UserName = @MyDstUserName,

    VARCHAR UserPassword = @MyDstUserPassword,

    VARCHAR WorkingDatabase = @MyDstUserDatabase,

    INTEGER ErrorLimit = 1000000

  );

Teradata Employee

Re: TPT script to load data with embedded qualifiers

Your attributes look fine.

But there have to be parsing rules.

They are not complex, but they exist so that delimited data can be parsed correctly.

You cannot expect to have quoted data with embedded quotes and for the parser to know when a quote is part of data, or designating the beginning or ending of the quoted string.

Can you please provide a sample of your data with your quoted strings and embedded quotes?

-- SteveF
Enthusiast

Re: TPT script to load data with embedded qualifiers

This is the data sample. If you see the value "GABRIEL "TIT" thats is our problem record :

Data Removed by DevX Admin's at the request of the user due to possible PHI:

Issue in Data appeared to be "NAME WITH EMBEDDED DOUBLE "QUOTE" that was not Escaped : Needed to be "NAME WITH EMBEDDED DOUBLE \"QUOTE" 

Teradata Employee

Re: TPT script to load data with embedded qualifiers

That field will have to be modified.

That record will be a problem because it does not adhere to our parsing rules.

(You cannot have it both ways. You cannot indicate that you want your data quoted, but then have an embedded quote that is not escaped.)

-- SteveF
Enthusiast

Re: TPT script to load data with embedded qualifiers

Thanks for the info. Just another question to make it clear. Can we achive this using the fast load script instead of TPT ?

Teradata Employee

Re: TPT script to load data with embedded qualifiers

No. Parsing rules are the same.

-- SteveF

Re: TPT script to load data with embedded qualifiers

Hi,

I am having the same problem where one extra quote in a single record is causing the whole file loading failure.

Is it possible to configure TPT so that the record containg quote is skipped and captured in the ET table and rest of the records are loaded successfullly.

--Sameer Durrani

Teradata Employee

Re: TPT script to load data with embedded qualifiers

Row placed in the ET table are performed by the DBS, not by TPT.

TPT has no control over which rows end up in the ET table.

And rows placed in the ET table are rows containing data conversion error being processed by the DBS, not parsing errors by the client application.

As indicated above, TPT requires there to be parsing rules, otherwise we would not know when a field begins and ends. If you want your fields to be quoted, and have a quote character as a part of the data, then the quote character must be escaped.

Parsing delimited data can be a tricky process. If we do not follow certain parsing rules, then fields might be parsed incorrectly, leasing to subsequent fields to be parsed incorrectly, leading to incorrect data being loaded into the table.

We would rather reject a job than to load incorrect data.

-- SteveF