Home » SQL & PL/SQL » SQL & PL/SQL » loading data fro excell to a table (oracle 9i , windows xp)
loading data fro excell to a table [message #348028] Mon, 15 September 2008 07:54 Go to next message
yairk30
Messages: 14
Registered: May 2006
Location: israel
Junior Member
hey!
i'm trying to insert data from an excell file into a table.
i can do it through a form (using ole container), but i wish to do it from an outside procedure. i have search the forums and find out that it can be done using java. this is no good solution for me!! i rather stick with pl/sql.
the complete task is to load into a table data from a number of xls files located in a given directory. i hope i could compleesh it with this proc.

any ideas?
thanks!!
yair
Re: loading data fro excell to a table [message #348031 is a reply to message #348028] Mon, 15 September 2008 08:01 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
By a happy coincidence, I@m working on exactly the same problem at the moment.

Here's how I'm doing it:

1) Set up Rracle HS so it will talk to each of the spreadsheets, as described here.

2) Treat your spreadsheets like tables across database links, Select away.

EachSpreadsheet accessed this way will have it's own set of metaata tables Appendix C 3

[EDITED by LF: fixed the first URL tags]

[Updated on: Thu, 18 September 2008 00:03] by Moderator

Report message to a moderator

Re: loading data fro excell to a table [message #348034 is a reply to message #348031] Mon, 15 September 2008 08:08 Go to previous messageGo to next message
yairk30
Messages: 14
Registered: May 2006
Location: israel
Junior Member
"Set up Rracle HS so it "
Sorry, what do you mean by that
can you be more specific here?
Re: loading data fro excell to a table [message #348051 is a reply to message #348034] Mon, 15 September 2008 08:46 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Oracle HS - Oracle Heterogeneous Services
It's a bridge connecting an Oracle TNSNames entry to an ODBC connection in such a way as to allow you to execute SQL against the remote data source.

The link I provided (from the articles on this site) talks you through setting it up.
Re: loading data fro excell to a table [message #348052 is a reply to message #348051] Mon, 15 September 2008 08:49 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If you prefer a lower tech solution, you can export the data from the spreadsheets into .csv files, nd then create External Tables to access the data from the .CSV files.

{typos}

[Updated on: Mon, 15 September 2008 08:49]

Report message to a moderator

Re: loading data fro excell to a table [message #348260 is a reply to message #348051] Tue, 16 September 2008 04:06 Go to previous messageGo to next message
yairk30
Messages: 14
Registered: May 2006
Location: israel
Junior Member
the link attached - http://www.orafaq.com/node/60 -
does'nt work (it gives "the page cannot be displayed" message).
can you supply a better link?

thanks in advance!

[EDITED by LF: fixed the URL]

[Updated on: Thu, 18 September 2008 00:12] by Moderator

Report message to a moderator

Re: loading data fro excell to a table [message #348261 is a reply to message #348260] Tue, 16 September 2008 04:07 Go to previous messageGo to next message
yairk30
Messages: 14
Registered: May 2006
Location: israel
Junior Member
sorry,
now it's worked!
Re: loading data fro excell to a table [message #348348 is a reply to message #348034] Tue, 16 September 2008 08:29 Go to previous messageGo to next message
yairk30
Messages: 14
Registered: May 2006
Location: israel
Junior Member
If i understood the access example provided at your link,
i should create an odbc connection with a given target (mdb or xls) file to connect to.
my problem is that i have to load automaticly data from up to 10 files that i should received every day (with variables names) , and of course i can't predefine odbc connection for each one of them day by day.

regarding the 2nd way (sql loader)- the data in my files can contain any kind of chars , including the " , " sign , which make the fields separation task a bit difficult.

Re: loading data fro excell to a table [message #348796 is a reply to message #348028] Wed, 17 September 2008 23:53 Go to previous messageGo to next message
ka_wish
Messages: 85
Registered: October 2007
Location: karachi
Member

yes i done this process i converto to rep to excel suceesfully.
perfectly good converting for use query no any software
thanks
Regards
Rizwan Admani
I have create succesfully chat room in developer form [message #348797 is a reply to message #348028] Wed, 17 September 2008 23:55 Go to previous message
ka_wish
Messages: 85
Registered: October 2007
Location: karachi
Member


i have create one autommatically referesh chat room with chanel support in oracle developer.
Razz
Previous Topic: write a calendar
Next Topic: how can i change/update the length of a datatype
Goto Forum:
  


Current Time: Mon Dec 05 08:41:46 CST 2016

Total time taken to generate the page: 0.05313 seconds