1

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?

Improve this question
5
  • 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... – Angelina Jun 9 '15 at 17:28
  • I fixed the 2 spaces problem, but I'm still getting the same error. I will update the question shortly. – Daniel Paczuski Bak Jun 9 '15 at 17:32
  • 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. – vegatripy Jun 10 '15 at 15:14
  • 1
    What "above error?" There is no error message in your question. – Air Jan 27 '16 at 22:49
  • I probably edited it and accidentally removed that. Would've been a better question to ask 7 months ago :P – Daniel Paczuski Bak Jan 28 '16 at 17:32
-1

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

Improve this answer
2
  • Would be good to have a little explanation here, instead of just the Link. It will be helpfull. – Racer SQL Jun 9 '15 at 17:43
  • I already looked at that page before posting here, it didn't help. – Daniel Paczuski Bak Jun 9 '15 at 17:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.