Forum Stats

  • 3,836,747 Users
  • 2,262,180 Discussions
  • 7,900,092 Comments

Discussions

How to load excel files into Oracle DB using COMAutomation

930808
930808 Member Posts: 62
edited Jul 8, 2015 1:03PM in SQL & PL/SQL

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

«13

Answers

  • Saubhik
    Saubhik Member Posts: 5,805 Gold Crown
    edited Jul 1, 2015 10:01AM
  • John Stegeman
    John Stegeman Member Posts: 24,269 Blue Diamond
    edited Jul 1, 2015 10:27AM
    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
    Mike Kutz Member Posts: 6,195 Silver Crown
    edited Jul 1, 2015 12:03PM

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

    MK

  • gaverill
    gaverill Member Posts: 390 Silver Badge
    edited Jul 1, 2015 4:31PM

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

    Gerard

  • Unknown
    edited Jul 1, 2015 8:48PM
    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
    930808 Member Posts: 62
    edited Jul 2, 2015 3:55AM

    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
    Saubhik Member Posts: 5,805 Gold Crown
    edited Jul 2, 2015 4:34AM

    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
    930808 Member Posts: 62
    edited Jul 2, 2015 4:39AM

    Thanks Saubhik. Will check and get back.

    Regards,

    Anand

  • chris227
    chris227 Member Posts: 3,517 Bronze Crown
    edited Jul 2, 2015 5:43AM

    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
    930808 Member Posts: 62
    edited Jul 2, 2015 11:58AM

    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.?

This discussion has been closed.