All Forums General
simplesim092 11 posts Joined 04/14
30 Apr 2014
TPT handling the CSV with date and timestamp column

Hi ,
 I am trying to load data from CSV to TD table with many columns out of which two columns have data type as Date and timestamp. I am getting an error when i do in the Insert as
:ROW_CREATED_DATE (timestamp(6),format '''yyyy-mm-ddBhh:mi:ss.SSSSSS''')..also :ROW_UPDATED_DATE  (Date,format '''yyyy-mm-dd''')..
When I am running the TPT , i am getting error as below
TPT_INFRA: At "yyyy" missing RPAREN_ in Rule: DML Statement List
TPT_INFRA: TPT02932: Error: Invalid token near line 170 (text was ':')
TPT_INFRA: TPT02932: Error: Invalid token near line 170 (text was ':')
Compilation failed due to errors. Execution Plan was not generated.
 
Need your help asap..!! Kindly revert about handing date and timestamp data type while laoding data from CSV to TD ..using TPT
 

dnoeth 4628 posts Joined 11/04
01 May 2014

Assuming this is from te APPLY section you need to use two single quotes, but you got three, which simply finishes the SQL string:

:ROW_CREATED_DATE (timestamp(6),format ''yyyy-mm-ddBhh:mi:ss.SSSSSS'')

 

Dieter

simplesim092 11 posts Joined 04/14
02 May 2014

Hi ,
Thanks for the reply...I tried to run this ..But i got below error...!!!
FILE_READER: TPT19008 DataConnector Producer operator Instances: 1
FILE_READER: TPT19003 ECI operator ID: FILE_READER-24260
FILE_READER: TPT19222 Operator instance 1 processing file '/data/developer/simple/tpt_scripts/Prod.csv'.
LOAD_OPERATOR: preparing target table
LOAD_OPERATOR: entering Acquisition Phase
LOAD_OPERATOR: TPT10508: RDBMS error 3619: Only one level of data type conversion allowed, column ROW_CREATED_DATE.
It giving this error...Can you please help for loading date from csv to TD table usingTPT..i am using Load operator..

simplesim092 11 posts Joined 04/14
03 May 2014

Can anyone suggest about loading the date column from csv to TPT...I tried laoding the current_timestamp from TPT to Td table..in table for that column had datatype as timestamp(0)..
In tpt ..insert stmt..I added value as  ,CAST(SUBSTRING(CAST(CURRENT_TIMESTAMP AS CHAR(26)) FROM 1 FOR 19) AS TIMESTAMP(0))
Can anyone please come up with solution if I had data in csv with timestamp..how can I load dat..I tried inserting from
CAST(SUBSTRING(CAST(:ROW_CREATED_DATE AS CHAR(26)) FROM 1 FOR 19) AS TIMESTAMP(0))
Its not working..Its giving an error..Need ur repsonse..!!!

samra.teradata 2 posts Joined 04/14
04 May 2014

Sir , I have been assigned with a new task and I am new in TD , the task we are facing some bad characters (for this we are using TRIM function ) error and duplication of PK (we are using Qualify) , to overcome this problem we are using these everytime in production , but we need to get rid of this . what could be the possible reason why is it happening and what should be done in order to correct it . It will be a great help .Thanks in advance .

vikas_yadav 19 posts Joined 09/12
04 May 2014

try this:     :ROW_TIME(TIMESTAMP(0),FORMAT ''YYYY-MM-DDbhh:mi:ss'')
if this doesn't help then please paste entire script and logfile.

You must sign in to leave a comment.