How to load excel files into Oracle DB using COMAutomation

930808

Hi,

I have a requirement where n number of excel files (xls or xlsx) containing multiple tabs needs to be loaded to oracle.

Available solutions in hand.

1 PHP script to convert to csv and load using sqlloader. tested and verified for both xls and xlsx formats with single and multi tabs.

2. PERL script to convert to csv and load using sqlloader. Tested and verified for xls.(single and multi tab). For xlsx, successfully converted, but issue with data wherever it is date.

I(actually the client) am looking for a solution in PLSQL. I came across the package called COM Automation.

But I see that the examples show, that it loads data into the excel rather loading from excel from oracle tables.

If anyone has used COM Automation, please clarify me the following.

1. Does this help in loading data from excel files(XLS and XLSX) from a predefined folder structure.

2. If so, does the excel file need to have data in some specific format or any format of data can be loaded. i.e, merged columns, formulas, first row and first column being blank etc.

3. Does it help in extracting each and every tab of the excel file and load it into a table.? or Is it possible to selectively load specific tabs.?

4. With respect to maintenance, which one is a better option. PERL, PHP or PLSQL.

Correct me if I have asked anything wrong.

Ready to answer any further questions.

Thanks,

Anand

Saubhik

John Stegeman

4. With respect to maintenance, which one is a better option. PERL, PHP or PLSQL.

That would depend on which language you and your team have the most experience with.

Mike Kutz

The easiest to maintain would probably be Option #3 - Oracle APEX with the "Excel2Collection" plugin.

MK

gaverill

Whether you end up using COM automation or not, please note that it is no longer supported starting with version 12c.

Gerard

Unknown

I(actually the client) am looking for a solution in PLSQL. 

Why?

You already have TWO solutions - why do you want to spend time creating a third one?

I'm a firm believer in: if it ain't broke don't fix it! Unless there is a problem with your current solution your time and resources could be better spent dealing with REAL problems.

Also, any PL/SQL solution will ONLY work on the server. The solutions you have will work on a server on client.

Even if you wanted to use PL/SQL there is no point is using it to prepare the data for loading. You already have scripts that can create delimited files - so any PL/SQL solution should just load the files after the scripts create them.

  I came across the package called COM Automation.  

I suggest you forget about that 'solution' as it is has MANY disadvantages:

1. rarely used

2. few developers/others know ANYTHING about it

3. represents a proprietary solution

It might bring you job security as you would likely be the only one in your area, or even your country, that could understand and maintain it.

4. With respect to maintenance, which one is a better option. PERL, PHP or PLSQL.

Who knows? What 'maintenance' are you talking about? Adding new columns? Changing the column specs?

For a well-designed and tested component a stable table design shouldn't require much, if any, maintenance. You need to take the skill set of your developers into account when you choose a language. As I mentioned earlier don't create a 'solution' that only ONE person knows.

Ready to answer any further questions.

Ok - what is the point in spending time and resources to create a third solution when you already have two? Would you spend your OWN money to do that?

930808

Why I am looking for a third solution when I already have two.?

Ans: Someone or from somewhere my client was given a notion that, this is possible using PL/SQL. That is why i am looking for the feasiblity of PL/SQL.

        Even if I need to make my client understand, PERL or PHP are simple solutions, first I need to know what are the cons of PL/SQL solution.

When I checked Oracle heterogeneous Services,

Process requires to setup each excel file as DB source, which is not feasible for me, since I have 100s of sheets coming in and that too updated on a quarterly basis.

I am looking for an automated solution. But this would need a manual intervention.

Also, I am not sure how OHS will handle files with different kinds of cells. I have merged cells and formula cells.

I am going to try OHS today. But still this cannot be automated.

OrdExcel package, loads data into the excel sheet, but does not read from it.

Is there any solution, using only PL/SQL coding ? Any packages available to read xls,xlsx??

With respect to maintenance,

Even I don't know what is the skill set of the support team, that will be formed in future(as my client says).

My question was with the intention of, if I get to know which one is a better option, I can ask the client to have the team with those skill sets.

Thanks,

Anand

Saubhik

ORDExcel can also read from excel, see one modification of ORDExcelSB in the FAQ of this forum and build your own on that, but as others mentioned there may be some issue on support and maintainability in future. You can search this forum with ORDExcelSB to find some old examples. I don't have WIN* server right now to demonstrate anything (in fact I don't have any database server right now!).

One of the old thread I can find :

930808

Thanks Saubhik. Will check and get back.

Regards,

Anand

chris227

Is your DB running on a WIN server in fact.

Than i personally dont understand why you didnt do the csv  generation in VB/VB instead of using some third party API.

Let's outline the idea briefly:

- depending on your requirement you will create some notify to start the import job if any new excel sheet is dropped in your "import-folder" or you start it manually.

Dont know exactly but i would take a look at Advandce queuing (AQ) feature if that is capable to support this.

- I would write a Excel-VBA Script that converts the excel-files to csv.

- Finally the csv would be imported into the target tables job based, dont know exactly how AQ and external tables could play together here. At least you can do an os based sqlldr-job.

930808

hi Saubhik,

I went through your code. I am yet to test by myself. But based on my analysis, I have one clarifications.

The code is written to print only the first few lines of the excel sheet using FOR loop. In an actual spreadsheet, the number of rows that will be coming in is unknown. Are there any functions to get the total count of rows, so that the for loop can be used in an actual scenario.?