Home » SQL & PL/SQL » SQL & PL/SQL » Import excel file to table (Oracle 10g)
Import excel file to table [message #574466] Thu, 10 January 2013 11:26 Go to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

I have two columns in excel which i need to import in oracle table , but the problem is one column is of type date , i want the same date format to be maintained in table too.
Re: Import excel file to table [message #574467 is a reply to message #574466] Thu, 10 January 2013 11:33 Go to previous messageGo to next message
BlackSwan
Messages: 23070
Registered: January 2009
Senior Member
DATE datatype do NOT have any "format"

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
Re: Import excel file to table [message #574471 is a reply to message #574466] Thu, 10 January 2013 12:21 Go to previous messageGo to next message
Michel Cadot
Messages: 59821
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have to
1/ Search on web how date are stored in Excel (a number of days since an origin, easy to find)
2/ Create a function (or use an expression) that converts this number into an Oracle date

Regards
Michel
Re: Import excel file to table [message #574488 is a reply to message #574466] Thu, 10 January 2013 23:56 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
The date shown in the excel can be in various format. You can show them in different format by providing different formats to it. If you want to see the file data in actual format, just save that file as CSV and then open that file in text editor.

regards,
Delna
Re: Import excel file to table [message #574518 is a reply to message #574488] Fri, 11 January 2013 09:33 Go to previous messageGo to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

thanks delna , then from csv file i need to use sqlloader and upload the data into oracle is it as i tried using toad upload , actually the date in excel format is of united kingdom format dd/mm/yyyy and after importing into oracle its comming as american format mm/dd/yyyy i.e 08-jun-2011 is becomming 06-aug-2011.
Re: Import excel file to table [message #574519 is a reply to message #574471] Fri, 11 January 2013 09:35 Go to previous messageGo to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

thanks michel,i didnt get the format but i will search more on that.

[Updated on: Fri, 11 January 2013 09:35]

Report message to a moderator

Re: Import excel file to table [message #574520 is a reply to message #574518] Fri, 11 January 2013 09:52 Go to previous messageGo to next message
dariyoosh
Messages: 536
Registered: March 2009
Location: Iran / France
Senior Member
arif_md2009 wrote on Fri, 11 January 2013 16:33
actually the date in excel format is of united kingdom format dd/mm/yyyy and after importing into oracle its comming as american format mm/dd/yyyy i.e 08-jun-2011 is becomming 06-aug-2011.


You can actually define the format as an argument of TO_DATE function while uploading data by SQL*Loader.

http://docs.oracle.com/cd/E11882_01/server.112/e26088/functions203.htm#i1003589


Regards,
Dariyoosh

[Updated on: Fri, 11 January 2013 10:05]

Report message to a moderator

Re: Import excel file to table [message #574545 is a reply to message #574520] Sat, 12 January 2013 00:44 Go to previous messageGo to next message
Littlefoot
Messages: 19824
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Moreover, if I remember well (don't have TOAD here), you can specify date format mask in TOAD's import wizard. Did you not try to do that?
Re: Import excel file to table [message #574547 is a reply to message #574545] Sat, 12 January 2013 06:20 Go to previous message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

thanks everybody , i changed the date format in toad and it got loaded with correct date format .
Previous Topic: Adding an IF Statement to a Trigger?
Next Topic: calling a if block based on parameter
Goto Forum:
  


Current Time: Fri Nov 28 18:25:25 CST 2014

Total time taken to generate the page: 0.09718 seconds