0

I am trying to import data from CSV file in to Oracle Table.

One of the column is DATETIME, with an example value- "6/26/2018 12:41:00 PM" (With 2 spaces between date and time)

In Data Import Wizard --> Column Definition --> Data Type

I select "Data Type" as Timestamp.

What should select in the below fields- Size/Precision- ?? (I tried different sizes like 15, 23, 25,50 ) Format- ?? (I tried various formats- MM/DD/YYYY HH:Mi:SS AM, MM/DD/YYYY HH:Mi:SS)

Please advise.

Thanks!

Deepu298
  • 65
  • 2
  • 11
  • 1
    Is the column in your Oracle table of type `TIMESTAMP`, or `DATE`? – kfinity Oct 16 '18 at 18:09
  • 1
    If it's coming from a CSV, it is text. I believe you can use `TO_DATE(string, format)` to tell Oracle what your string means. – Shawn Oct 16 '18 at 18:09
  • Basically, I want to upload the columns in timestamp format (from Text DATETIME column). If not, I can load the column as varchar2 and then probably use this- TO_DATE(string, format) or TO_DATE (SUBSRT("DATETIME, 1, 10), 'MM/DD/YYYY') but the question remains unresolved – Deepu298 Oct 16 '18 at 18:53

3 Answers3

1
Try below format in database and also in excel

Import DB: while loading in table MM/DD/YYYY HH:MI:SS AM in sql developer using import.

CSV file : Also format the csv file date field with format using custom  m/d/yyyy h:mm:ss AM/PM
kanagaraj
  • 442
  • 1
  • 3
  • 8
  • MM/DD/YYYY HH:MI:SS AM, already tried this, it won't work. m/d/yyyy h:mm:ss AM/PM - This isn't working either. – Deepu298 Oct 16 '18 at 18:47
  • 1
    It worked for me ... I changed format in csv file for timestamp field as m/d/yyyy h:mm:ss AM/PM and then use the file with import wizard in sql developer. While loading in table use format as MM/DD/YYYY HH:MI:SS AM it will load successfully. – kanagaraj Oct 16 '18 at 18:57
1

Make sure it's stored as a date and not as a VARCHAR2.

Then put in a valid NLS_DATE_FORMAT, so we know how to properly insert the record. For your sample, this will do: MM/DD/YYYY HH:MI:SS AM

enter image description here

We validate the format, so you can see there's no warning next to the list of dates we are previewing below.

One might consider this question a duplicate of sorts...

But you're not dealing with the RAW INSERT statement, our GUI is, and this is how you tell us that same information.

thatjeffsmith
  • 20,522
  • 6
  • 37
  • 120
  • For my .csv file with the date values as **"6/26/2018 12:41:00 PM"** The **DATE** format I used **MM/DD/YYYY HH24:MI** and it worked for me probably I have all 00 values in my time (SS). – Deepu298 Oct 19 '18 at 14:27
0

For my .csv file with the date value as "6/26/2018 12:41:00 PM" select the DATE (No timestamp) and used format- MM/DD/YYYY HH24:MI and it worked for me probably I have all 00 values in my time (SS).

Deepu298
  • 65
  • 2
  • 11