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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
ASKER
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.
Not sure if this is proper protocol on the site.
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.