Tips to convert data on TPT

Database
Enthusiast

Tips to convert data on TPT

Hi everyone, i need load some data from a csv file to a teradata table using TPT.

I´m having some troubles on a field what has the following mask in the table: DD/MM/YYYYBHH:MI

The problem its here: In csv file the field has data like this: 04/04/2016 16:38, this sample has the correct mask but on the other hand, data what has hour between 0:00 and 9:59 not uses the correct mask, an example 04/04/2016 9:30, uses DD/MM/YYYYBH:MI and tpt detects this like an error.

I need some tip to put a 0 before the hour if the hour its between 0:00 and 9:59.

I think uses case and compare the field length, 16 for the correct date and 15 for the error date, compare and put a 0 before the hour in 15 length field on the mask, to make this mask DD/MM/YYYYB0H:MI to transform error date before tpt try to insert in the table

Thanks ind advance and sorry for my english T.T

5 REPLIES
Junior Supporter

Re: Tips to convert data on TPT

Hi

You can do it easily using FORMATIN clause for VARDATE type in the schema definition.

FORMATIN ('DD/MM/YYYYbhh:mi') will accept the one digit hours.

HTH.

Cheers.

Carlos.

Enthusiast

Re: Tips to convert data on TPT

Hi Carlos thanks for your help

I didnt use before vardate to insert data, i need a formatout clause

I was using this to insert my timestamp data on Apply zone:

:DATE(TIMESTAMP(0),FORMAT ''DD/MM/YYYYBHH:MI'')

Thanks in advance

Junior Supporter

Re: Tips to convert data on TPT

Hi:

For example:

DEFINE SCHEMA your_schema

...

your_timestamp VARDATE FORMATIN('DD/MM/YYYYbhh:mi') FORMATOUT('YYYY-MM-DDbhh:mi:ss')

...

HTH

Cheers.

Carlos.

Enthusiast

Re: Tips to convert data on TPT

I think the mask what you give me on the Formatout clause its the native mask on TD.

There´s not problem.

Using this tip what you give me TPT have the following error:

TPT10679 Error: MONTH value supplied in data row does not fall into supported range [1,12].

It's strange because i checked all data and i didnt see any month what is not between 1 -12

I checked data using an excel filter at the column and only shows me Year => April, no other data, so every date is correct.

Enthusiast

Re: Tips to convert data on TPT

There´s no problem, we choose another option, we make a temp table of full varchar and insert there all the data.

Finally with a procedure, we format correctly the data to insert them on final table.

Thanks for all