Automate loading of Excel or Access file into Oracle 10g Database

Hi,

Can I automate loading an Excel file or Access table into Oracle 10g database?
I was hoping maybe I could write a pl/sql proc or pkg to get the data for me and perform the etl that way. Any help or pointers to useful sites/tutorials would be greatly appreciated.

At the moment I use the tools in sql developer to load the Access db i.e Access Exporter, Repository Management and Migration. I am hoping to get away from this method as I do not want to be stuck doing this manually every week.

P.S. Which is easier to work with Excel file or Access when loading into Oracle?

Thank You.
GerardMcL_1Asked:
Who is Participating?
 
AmickConnect With a Mentor Commented:
You could use Quickload, a free utility with the purpose of loading Excel data into a database.

If you'd like a more complete ETL tool, Pentaho Data Integration and Talend are highly regarded.

I have used Pentaho and find it easy to learn, fast, and reliable, but all three of the suggested programs are well though of by their users.  Since they're free (although both Pentaho and Talend have premium support available), you may want to download them all and see which best suits your needs.
0
 
slightwv (䄆 Netminder) Commented:
As mentioned above there are third party tools that can do this for you.

You can also probably write Macros/VBA code that can use ADO and/or Linked tables.  Then just schedule a job that opens them and runs the code.
0
 
johanntagleCommented:
Would it be okay if you convert them to csv first?  Then you can use Oracle External Tables.  See

http://www.dba-oracle.com/art_ext_tabs_spreadsheet.htm
http://www.orafaq.com/node/848

Basically, you set it up once so that you can query from it as if it's a normal database table, then just replace the csv file whenever you need to (note: it has to be same file and path name).  Then you can have an oracle job to select from that external table regularly and insert to the appropriate regular tables.

Note that you cannot do any DML on the external table nor create indexes on it - you just use it to read external data.
0
 
GerardMcL_1Author Commented:
I gave a B as though the question sufficed I was hoping for a coding fix to this problem.
Not sure if this is proper protocol on the site.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.