Toad World® Forums

Import wizard issue with date in YYYYMMDD format

Value ‘20090729’ at row #218 cannot be convert to type TIMESTAMP(6) of column DATE_EFFET

When trying to import data in a YYYYMMDD format into a ‘TIMESTAMP’ type field in table from a csv file using the Import tool wizard, I always have an issue.

Example:
Could the import tool be enhanced to undestand that type of format.

I nend to know what connection type you are using. If I can reproduce on this end I can enter a ticket. Please provide database type and version. And a smaple file wouldn’t hurt

Debbie

Hello,

This occurs during an attempt of import to Oracle 10g or 11g.
I am using Orace 11g client ODBC driver.
Actully (it may be linked to my regional settings ?) only DD/MM/YYYY (HH24:MIS:SS) format is understood.

Thank you for your help.

Regards,

Alexandre DOLIQUE.

I did enter CR96341 for this enhancement. But I am not sure we would want to do this as there are many other formats that would also need to be supported. We test and verifiy that anything exported by TDA can be Imported by TDA.

I would suggest as a work around to connect to this file(as csv or excel) and use SQL to format the way you want it. Use this SQL as a Query in Import to your Oracle destination database.

Debbie

Thank you for your reply.

Actually the most flexible would be to be able to specify the date format in the import Wizzard (as it is done in a control file when using sqlldr).

Regards,

Alexandre DOLIQUE.

That sounds reasonable. I will add that to the enhancement request.

Debbie

Hello Debbie

I’m a big TOAD fan by default!

Anyway… I have a problem and I think maybe its one solved by this query. The bulk of what I want to do with Toad Data Point is importing CSV files into the database on a schedule. All the csv files have a date field. All the date fields on the CSV’s are either dd/mm/yyyy or dd-mm-yyyy.

When I import using Toad for Oracle, there is an option under Dates, Times and Numbers, Dates Order - to use a custom format. How do I use that in the TOAD Data Point as this is essential to how we operate?

Kind regards,

Joshua