This content has been marked as final. Show 8 replies
You need to match the date format for the data as it's stored in the Excel column so we know how to read it.
What's an example value from your spreadsheet?
My excel column is formatted as 09/19/2012 02:46:13 PM - it has a custom format: MM/DD/YYYY HH:MM:SS AM/PM. If I use this same format on my import, I get the error "Effectivity Date GDK-05010: DUPLICATE FORMAT CODE FOUND. Thanks for your help.
A fairly common error when dealing with date formats is MM (months) versus MI (minutes). Plus I believe AM and PM are interchangeable...
MM/DD/YYYY HH:MM:SS AM/PM vs MM/DD/YYYY HH:MI:SS AM
If I use MM/DD/YYYY HH:MI:SS AM in Excel, this is what I get: 09/12/2012 02:46I:13 PM
If I use MM/DD/YYYY HH:MI:SS AM in Excel, this is what I get: 09/12/2012 02:46I:13 PMMy intention was that you supply that date format to SQL Developer's Import wizard. Not sure if it conforms to whatever Excel might require in a date format. So are you saying the import into SQL Developer does not succeed?
Yes, that's correct. The import keeps failing. I've tried many different format combinations in Excel/Sql Developer. If I supply a format during the import process, should it override the default date format that is set up under Tools>Preferences? Thanks.
Yes, the date format provided in the Import wizard will override the format in the NLS preferences.
Anyway, although I do not have Excel installed, I was able to use Open Office to produce xls and csv files with an equivalent test case. I suspect there may be a bug with how the Import wizard interprets a date/time string for xls files since the wizard's Data Preview panel only shows the date/time as MM/DD/YYYY. Proceeding with the import, using the longer date format, it failed because the format was longer than the data provided in the xls (at least as far as Data Preview is concerned).
As a workaround I saved the xls in csv format, carefully specifying comma as the only field delimiter (the Save As dialog had Space checked off as a field delimiter also). That way Data Preview shows the full date/time string in a single column. Otherwise data, time, and AM/PM show as being in 3 distinct columns. The csv imported perfectly using the long date format.
My test used "MM/DD/YYYY HH:MM:SS AM/PM" as the Open Office format (actually a user-defined format since the closest seeded format does not include the ":SS") and MM/DD/YYYY HH:MI:SS AM as the Import wizard format (for both date or timestamp as the target column). This test used release 3.2.20.09.87.
I will ping the developer.
Edited by: Gary Graham on Jan 31, 2013 5:02 PM