I am new to Teradata and I have a requirement to load the data FROM EXCEL using BTEQ UTILITIY. However as far as I know we cannot load data from excel but can load in CSV format.
requirement is as below
I have to load the set of records and below is the source data ie CSV
234567 01AUG2016 5:57:51
Target table in TD which we have is
create set table xx
I tried writing the BTEQ Script ie
Date --------how to convert 01AUG2016 5:57:51 TO timestamp (6) in Teradata ??????
insert into xx
FastLoad supports only limited data type conversions, either implicit or using Teradata-style syntax. And at a minimum you would need to change the input to always have 2 digits for the hour. If you are saving from Excel as CSV, apply a custom format to the column to make it compatible with Teradata / ANSI standard: yyyy-mm-dd hh:mm:ss
Also if the input is delimited text, all the fields in your FastLoad DEFINE must be VARCHAR.
thank you very much for your inputs howver I have tried to create the staging table with the below query and used oTranslate funcion to pad 0 if its single digit
Not sure I understand your question at this point. Are you saying that you loaded to a staging table as character, and now you want a SQL expression that will CAST the character string to a Timestamp, such as:
CASE WHEN SUBSTRING(DIARY_DATE FROM 12 for 1) = ':'
THEN SUBSTRING(DIARY_DATE from 1 for 10)||'0'||SUBSTRING(DIARY_DATE from 11 for 7)
ELSE DIARY_DATE END
AS TIMESTAMP FORMAT'ddmmmyyyy hh:mm:ss')