This discussion is archived
4 Replies Latest reply: May 4, 2010 12:51 AM by Irian RSS

Data Import Wizard from Excel on DATE column

742416 Newbie
Currently Being Moderated
I have exported data to a csv in Oracle SQL Developer and one of the columns are of an Oracle Date datatype. In opening up Excel I see the Date column represented as 15-MAY-01 12.00.00.000000000 AM.

I am now going through the Data Import Wizard to create a new table and import the data. When I get to Step 3 of 4 it reads the data and assigns it a VARCHAR2. I want the column to be Date or Timestamp. In this case I choose timestamp and for format entered DD-MON-YY hh.mi.ss.fffffffff AM.

When I go to Step 4 of 4 to and click the Verify button I get:
"Verifying if the Date columns have date formats" Date columns DOT_LOGN_EFDT, have invalid or null date formats.

I am using Oracle SQL Developer 2.1.0.63 Build 63.73.

Any idea what I put in the format window to read the excel value of 15-MAY-01 12.00.00.000000000 AM correctly?
  • 1. Re: Data Import Wizard from Excel on DATE column
    JoyceScapicchio Journeyer
    Currently Being Moderated
    I am not able to reproduce your problem. When I export a date column, I do not get the time portion. This is the expected behavior. When re-importing the date, I therefore have no problem. I have tried manipulating the tools->preferences->database->nls->date format mask to include the time (and fractional seconds), but export still only exports the date portion. The fractional seconds is not valid for a date field, which explains the import error you are getting. The question remains as to why the exported data has the time. Can you describe your exact steps for doing the export?
  • 2. Re: Data Import Wizard from Excel on DATE column
    JoyceScapicchio Journeyer
    Currently Being Moderated
    On further research, I needed to restart sqldeveloper to pickup changes in the NLS_DATE format. If I have a valid date format set which includes the time without fractional part, then export/import works. Please check your NLS_DATE format to ensure that it does not have a fractional part included.
  • 3. Re: Data Import Wizard from Excel on DATE column
    user13038177 Newbie
    Currently Being Moderated
    Hi Keith Brockman,

    Have you solved this issue? I am encountering the same problem as yours. I cannot import the dates from excel with timecomponent.
    Sample exported data: '31-MAY-09 10.44.22.000000000 AM'

    In the data import wizrd of SQL developer, what should I enter as the date format mask?

    Edited by: user13038177 on May 3, 2010 10:48 PM
  • 4. Re: Data Import Wizard from Excel on DATE column
    Irian Pro
    Currently Being Moderated
    You can actually import a time component into a DATE field, but only without the millisecond fraction, so in your case you should remove the ending .000000000 from your source file, or modify your DATE field to a TIMESTAMP and then run the import again.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points