OraFAQ Forum: Server Utilities » Import data type date from excel to oracle

Home » RDBMS Server » Server Utilities » Import data type date from excel to oracle (oracle 11g in windows 7 profesional)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Import data type date from excel to oracle [message #577756]

Wed, 20 February 2013 03:43

mei tambunan
Messages: 37
Registered: February 2013
Location: Medan

Member

/forum/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 1686 times)

Report message to a moderator

Re: Import data type date from excel to oracle [message #577757 is a reply to message #577756]

Wed, 20 February 2013 03:49

Littlefoot
Messages: 21761
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).

Report message to a moderator

Re: Import data type date from excel to oracle [message #577758 is a reply to message #577756]

Wed, 20 February 2013 03:51

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

Report message to a moderator

Re: Import data type date from excel to oracle [message #577760 is a reply to message #577758]

Wed, 20 February 2013 03:52

mei tambunan
Messages: 37
Registered: February 2013
Location: Medan

Member

so what should i do sir..

Report message to a moderator

Re: Import data type date from excel to oracle [message #577761 is a reply to message #577760]

Wed, 20 February 2013 03:55

Littlefoot
Messages: 21761
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.

Report message to a moderator

Re: Import data type date from excel to oracle [message #577762 is a reply to message #577761]

Wed, 20 February 2013 04:04

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

Report message to a moderator

Re: Import data type date from excel to oracle [message #577764 is a reply to message #577762]

Wed, 20 February 2013 05:14

ThomasG
Messages: 3211
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')

Report message to a moderator

Re: Import data type date from excel to oracle [message #577779 is a reply to message #577764]

Wed, 20 February 2013 07:44

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.

Report message to a moderator

Re: Import data type date from excel to oracle [message #577783 is a reply to message #577779]

Wed, 20 February 2013 08:29

joy_division
Messages: 4963
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.

Report message to a moderator

Re: Import data type date from excel to oracle [message #577798 is a reply to message #577783]

Wed, 20 February 2013 12:57

Littlefoot
Messages: 21761
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.

Report message to a moderator

Re: Import data type date from excel to oracle [message #577935 is a reply to message #577798]

Thu, 21 February 2013 20:30

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

Report message to a moderator

Re: Import data type date from excel to oracle [message #577947 is a reply to message #577935]

Fri, 22 February 2013 01:04

Littlefoot
Messages: 21761
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Could you post a screenshot of the Data Type pane? This:

/forum/fa/10666/0/

/forum/fa/10666/0/

Attachment: data_type_pane.png
(Size: 62.66KB, Downloaded 1515 times)

Report message to a moderator

Re: Import data type date from excel to oracle [message #577993 is a reply to message #577947]

Fri, 22 February 2013 08:28

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

Report message to a moderator

Re: Import data type date from excel to oracle [message #578010 is a reply to message #577993]

Fri, 22 February 2013 12:34

joy_division
Messages: 4963
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.

Report message to a moderator

Re: Import data type date from excel to oracle [message #578049 is a reply to message #578010]

Sat, 23 February 2013 11:10

Littlefoot
Messages: 21761
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.

Report message to a moderator

Previous Topic:	MP-00058: ORA-01950
Next Topic:	Sql Loader Error

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Apr 02 01:48:48 CDT 2023