TPT handling the CSV with date and timestamp column

General
Enthusiast

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

5 REPLIES
Senior Apprentice

Re: TPT handling the CSV with date and timestamp column

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'')
Enthusiast

Re: TPT handling the CSV with date and timestamp column

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..

Enthusiast

Re: TPT handling the CSV with date and timestamp column

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..!!!

Re: TPT handling the CSV with date and timestamp column

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 .

Enthusiast

Re: TPT handling the CSV with date and timestamp column

try this:     :ROW_TIME(TIMESTAMP(0),FORMAT ''YYYY-MM-DDbhh:mi:ss'')

if this doesn't help then please paste entire script and logfile.