Home » RDBMS Server » Server Utilities » Import data type date from excel to oracle (oracle 11g in windows 7 profesional)
Import data type date from excel to oracle [message #577756] Wed, 20 February 2013 03:43 Go to next message
mei tambunan
Messages: 37
Registered: February 2013
Location: Medan
Member
./fa/10663/0/

I have imported data from excel to oracle 11g. But i found an error like

"Ensure format is entered for datatypes 'Date' and 'TIMESTAMP' on data type pane".

after that i try to modify type date in oracle become 'dd-mmm-yyyy' as i search in google
but the error still appear

what should i do?
  • Attachment: import.png
    (Size: 132.63KB, Downloaded 488 times)
Re: Import data type date from excel to oracle [message #577757 is a reply to message #577756] Wed, 20 February 2013 03:49 Go to previous messageGo to next message
Littlefoot
Messages: 18833
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
'03-Jun-2012' is not 'dd-mmm-yyyy' but 'dd-mon-yyyy' ('mmm' doesn't even exist).
Re: Import data type date from excel to oracle [message #577758 is a reply to message #577756] Wed, 20 February 2013 03:51 Go to previous messageGo to next message
mei tambunan
Messages: 37
Registered: February 2013
Location: Medan
Member
sorry not to give info about the version

i am using oracle version 11g and ms excel 2010

thanks in advance
Re: Import data type date from excel to oracle [message #577760 is a reply to message #577758] Wed, 20 February 2013 03:52 Go to previous messageGo to next message
mei tambunan
Messages: 37
Registered: February 2013
Location: Medan
Member
so what should i do sir..
Re: Import data type date from excel to oracle [message #577761 is a reply to message #577760] Wed, 20 February 2013 03:55 Go to previous messageGo to next message
Littlefoot
Messages: 18833
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
- you: after that i try to modify type date in oracle become 'dd-mmm-yyyy' as i search in google but the error still appear
- me : don't use 'dd-mmm-yyyy', try with 'dd-mon-yyyy'
- you: so what should i do


I don't know; maybe someone else does.
Re: Import data type date from excel to oracle [message #577762 is a reply to message #577761] Wed, 20 February 2013 04:04 Go to previous messageGo to next message
mei tambunan
Messages: 37
Registered: February 2013
Location: Medan
Member
i don't know how to change d-mmm-yy in excel to dd-mon-yyyy in oracle sir
i have made column date_of_issue and date_of_return in type date in oracle
and in excel also i have made column date_of_issue and date_of_return in type date
but when i try to import, the error appear sir
so what the problem make it can't be imported sir

sorry sir, i just beginner in oracle sir
please give me explanation

Re: Import data type date from excel to oracle [message #577764 is a reply to message #577762] Wed, 20 February 2013 05:14 Go to previous messageGo to next message
ThomasG
Messages: 3064
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
I just can make out a

to_date('03-Jun-2012','')


behind the error message.

Obviously the format (second parameter) is not present in the generated SQL statements. Where to enter that format in that GUI I have no clue, but the correct SQL should look like:

to_date('03-Jun-2012','dd-mon-yyyy')
Re: Import data type date from excel to oracle [message #577779 is a reply to message #577764] Wed, 20 February 2013 07:44 Go to previous messageGo to next message
mei tambunan
Messages: 37
Registered: February 2013
Location: Medan
Member
i have tried to do your suggest, but it still error sir.

have you another suggest how should i do to import data from excel that have type date to oracle sir.
i try to install XlsToOra.exe but i still can't import it sir.

Re: Import data type date from excel to oracle [message #577783 is a reply to message #577779] Wed, 20 February 2013 08:29 Go to previous messageGo to next message
joy_division
Messages: 4454
Registered: February 2005
Location: East Coast USA
Senior Member
mei tambunan wrote on Wed, 20 February 2013 08:44
i have tried to do your suggest, but it still error sir.


I would like to see what you did. Telling us does not prove anything.
Re: Import data type date from excel to oracle [message #577798 is a reply to message #577783] Wed, 20 February 2013 12:57 Go to previous messageGo to next message
Littlefoot
Messages: 18833
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I might be oldfashioned, but I'd rather save that Excel file into a CSV file, write a proper control file and load data using SQL*Loader. Doing so, I would be the operator with my pocket calculator, not vice versa.
Re: Import data type date from excel to oracle [message #577935 is a reply to message #577798] Thu, 21 February 2013 20:30 Go to previous messageGo to next message
mei tambunan
Messages: 37
Registered: February 2013
Location: Medan
Member
before code of DML

insert into BOOK_ISSUE (ISSUEID,BOOK_ID,MEMBER_ID,DATE_OF_ISSUE,DATE_OF_RETURN,STATUS) VALUES('Issue_00000000000001','B00000001','IF00000001',TO_DATE('03-Jun-2012',''),TO_DATE('05-Jun-2011',''),'Bagus');


and i try to add dd-mon-yyyy this code

insert into BOOK_ISSUE (ISSUEID,BOOK_ID,MEMBER_ID,DATE_OF_ISSUE,DATE_OF_RETURN,STATUS) VALUES('Issue_00000000000001','B00000001','IF00000001',TO_DATE('03-Jun-2012','dd-mon-yyyy'),TO_DATE('05-Jun-2011','dd-mon-yyyy'),'Bagus');


but it still show error like
"Ensure format is entered for datatypes 'DATE' and 'TIMESTAMP' on data type pane"

please give me explanation sir
Re: Import data type date from excel to oracle [message #577947 is a reply to message #577935] Fri, 22 February 2013 01:04 Go to previous messageGo to next message
Littlefoot
Messages: 18833
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Could you post a screenshot of the Data Type pane? This:

./fa/10666/0/

Re: Import data type date from excel to oracle [message #577993 is a reply to message #577947] Fri, 22 February 2013 08:28 Go to previous messageGo to next message
mei tambunan
Messages: 37
Registered: February 2013
Location: Medan
Member
i have changed data type like data type when i import it in oracle sir
after that i click DML and then i click insert
so the error appear sir.

then i install SQL*LOADER
i try to import data in excel to oracle by using SQL*LOADER
and data can import, but only a few data can be imported sir
maybe only 65 data can be imported

but actually i want to import 65536
so what is the problem?
why the data can only insert a few?

thank for your explanation sir

Re: Import data type date from excel to oracle [message #578010 is a reply to message #577993] Fri, 22 February 2013 12:34 Go to previous messageGo to next message
joy_division
Messages: 4454
Registered: February 2005
Location: East Coast USA
Senior Member
I see no reason to answer you as you have not answered to most basic of questions answered to you.
Re: Import data type date from excel to oracle [message #578049 is a reply to message #578010] Sat, 23 February 2013 11:10 Go to previous message
Littlefoot
Messages: 18833
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Quote:
then i install SQL*LOADER etc.

OK; when you ran SQLLDR, you should have created a log file. Open it and check what's in there. You'll see a reason (or reasons) why all these records weren't loaded.
Previous Topic: MP-00058: ORA-01950
Next Topic: Sql Loader Error
Goto Forum:
  


Current Time: Sat Apr 19 15:01:39 CDT 2014

Total time taken to generate the page: 0.10527 seconds