I have an Excel file that contains dates as 8-digit numbers (not text strings) in YYYYMMDD format. I’m trying to create a query in TDP that will convert the numbers into dates, eventually to import them into an Oracle table. In the past, I’ve had to create, copy and paste an Excel function into a new column to create an Excel date. Or I’ve imported the data into a work table and used Oracle functions to convert into a date. I would like to eliminate these work-arounds.
I’ve spent hours researching VBA and Excel ODBC to come up with an approach. I tried combinations of CDate, STR_TO_DATE(), DateSerial, and DateValue, as well as text conversion ideas. Tried putting the function into { … } format. All have come up with syntax or unknown function errors.
Are you trying to convert the number to date in Excel or you want to manipulate the data after you imported it in Oracle? We currently don’t support custom format during import, this will be an enhancement in future release.
I’m trying to load the value directly into an Oracle date column. My workaround is to use Excel formulas to convert it into text, rearrange and format the string into an acceptable date, and then convert it into a date value. Then I can use the regular import wizard.
I saw some examples in TOADWorld blogs of custom queries that could SELECT from an Excel spreadsheet and use as an import to an Oracle table. I understand, from examples and my own errors, that the SQL would have to be compatible with Excel ODBC. I just cannot get the right conversion and/or string manipulation functions to work together.
Please try use the string functions to extract year, month and day info and concatenate them into a string in Oracle datetime format. Then connect to Oracle, use import query to import the string directly to date column.
SELECT id,mid (numdate, 5, 2)+ '/' + left (numdate, 4)
Thanks. I wasn’t even getting the Excel query to work before. I was trying to concatenate the pieces with ‘&’, like I would in a spreadsheet, instead of ‘+’ (both forms appear to work today). I don’t remember what the error message was.
I’ll try the import/datatype piece later, but I’m sure it’s fine.
