SQL Developer - trying to import an excel file into a table

Question

I'm trying to import an excel file into a table. The excel file has dates in the following format: 20/08/2008 12:00:00 AM and I am telling SQLDEV the following: DD/MM/YYYY H24:MI:SS AM

To import the file, I am right clicking on the table in SQL Developer, hitting import data and then selecting the file, I map the columns and set the format to above and get the above error. Here's the table:

--------------------------------------------------------
-- DDL for Table DRUG_INFO_NOS
--------------------------------------------------------
 CREATE TABLE "DANIEL"."DRUG_INFO_NOS" 
 ( "DIN" VARCHAR2(20 BYTE), 
 "TRADE_NAME" VARCHAR2(50 BYTE), 
 "FORM" VARCHAR2(40 BYTE), 
 "GENERIC_NAME" VARCHAR2(60 BYTE), 
 "MANUFACTURER" VARCHAR2(100 BYTE), 
 "CR_DATE" DATE, 
 "NOC_DATE" DATE, 
 "FIRST_CLAIM_DATE" DATE, 
 "SEQUENTIAL_ID" NUMBER
 ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
 TABLESPACE "DATAL03" ;
--------------------------------------------------------
-- Constraints for Table DRUG_INFO_NOS
--------------------------------------------------------
 ALTER TABLE "DANIEL"."DRUG_INFO_NOS" MODIFY ("DIN" NOT NULL ENABLE);
 ALTER TABLE "DANIEL"."DRUG_INFO_NOS" MODIFY ("TRADE_NAME" NOT NULL ENABLE);
 ALTER TABLE "DANIEL"."DRUG_INFO_NOS" MODIFY ("GENERIC_NAME" NOT NULL ENABLE);
 ALTER TABLE "DANIEL"."DRUG_INFO_NOS" MODIFY ("MANUFACTURER" NOT NULL ENABLE);
 ALTER TABLE "DANIEL"."DRUG_INFO_NOS" MODIFY ("SEQUENTIAL_ID" NOT NULL ENABLE);

but I am getting the above error. What's up?

• Angelina: You have not provided enough information. Post the error message, structure and data-types of existing table in db. and what steps you are using to import excel file. How many spaces are in that excel date you provided? Looks like there are 2 spaces between date and time. Not sure if this could be an issue...
• Daniel Paczuski Bak: I fixed the 2 spaces problem, but I'm still getting the same error. I will update the question shortly.
• vegatripy: You should use the "insert script" option in order to actually see what SQL Dev is doing, and fix it on the way. I did a test and I saw that date formats from excel files doesn't import hour data in SQL Dev. May be a bug. Your best option is to save the excel file into a CSV, and import it from there.
• Air: What "above error?" There is no error message in your question.
• Daniel Paczuski Bak: I probably edited it and accidentally removed that. Would've been a better question to ask 7 months ago :P

Answer 1

This link shows how to deal with importing excel file and invalid date format: how-to-import-excel-to-oracle. Hope this helps.

• Racer SQL: Would be good to have a little explanation here, instead of just the Link. It will be helpfull.
• Daniel Paczuski Bak: I already looked at that page before posting here, it didn't help.