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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

AmickCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.