Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

How to load excel files into Oracle DB using COMAutomation

930808Jul 1 2015 — edited Jul 8 2015

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

Comments

843834
I agree with your hunch. The parser should treat dlmd:record as just another element (colons are allowed in XML element names when namspace feature is turned off). Maybe you could elaborate a bit on what parser you're using, what Document (JDOM or DOM) you're building and where the exception comes from (io exception is kind of strange, too).
843834
Hi
I am using JAXP 1.1 and I am builidng a DOM document. The exception I get is a SAX Exception and the following is the stack trace

java.lang.NullPointerException
at org.apache.crimson.parser.Parser2.parseInternal(Parser2.java:523)
at org.apache.crimson.parser.Parser2.parse(Parser2.java:304)
at org.apache.crimson.parser.XMLReaderImpl.parse(XMLReaderImpl.java:433)
at org.apache.crimson.jaxp.DocumentBuilderImpl.parse(DocumentBuilderImpl.java:179)

at javax.xml.parsers.DocumentBuilder.parse(DocumentBuilder.java:161)
at dlnet.DLXMLParse.dlDocumentBuild(DLXMLParse.java:282)
at dlnet.DLXMLParse.dlStart_Parse(DLXMLParse.java:50)
at dlnet.DLTrigger.main(DLTrigger.java:40)

Hope you can suggest a solution.....
843834
I see. I only tried xerces with your example and it was just fine. You're using Crimson, though and there seems to be a bug in crimson1.1. Go, get crimson1.1.1 from http://xml.apache.org and it'll parse fine.

Good luck,
Leo.
843834
Using the new version of Crimson does not help either. After I build the new Document Factory I set the namespace awareness to false and yet it gives me a sax exception......Could something else be wrong too!??

Thanks for your help....
Prashanth
843834
Prashanth,
this is what I'm doing. First, the parser test code:
import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import org.w3c.dom.Document;

public class JAXPDOMTest {

    public void domParse(String url) {
        DocumentBuilder parser;
        DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();
        factory.setValidating(false);
        factory.setNamespaceAware(false);

        try {
            parser = factory.newDocumentBuilder();
            Document doc = parser.parse(url);

        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    static public void main(String[] args)
    {
       JAXPDOMTest x=new JAXPDOMTest();
       x.domParse("test.xml");
    }
}
and here's test.xml: 
<?xml version="1.0"?>

<metadata>
   <schema>Schema 1</schema>
   <schemaversion>DX:MD1.0 S:1.0</schemaversion>
   <dlmd:record>Record</dlmd:record>
</metadata>
jaxp-1.1 is my directory containing the official 1.1 release and my complete classpath look like this:
CLASSPATH=d:\xml_tools\jaxp-1.1\crimson.jar;d:\xml_tools\jaxp-1.1\jaxp.jar;.

I run the above (java JAXPDOMTest), I get:
java.lang.NullPointerException
at org.apache.crimson.parser.Parser2.parseInternal(Parser2.java:523)
at org.apache.crimson.parser.Parser2.parse(Parser2.java:304)
at org.apache.crimson.parser.XMLReaderImpl.parse(XMLReaderImpl.java:433)

at org.apache.crimson.jaxp.DocumentBuilderImpl.parse(DocumentBuilderImpl.java:179)
at javax.xml.parsers.DocumentBuilder.parse(DocumentBuilder.java:134)
at JAXPDOMTest.domParse(JAXPDOMTest.java:15)
at JAXPDOMTest.main(JAXPDOMTest.java:24)

I set the class path to:
CLASSPATH=d:\xml_tools\crimson-1.1.1\crimson.jar;d:\xml_tools\crimson-1.1.1\jaxp.jar;.

and, voila, no exception.

Hope that helps,
Leo.
843834
Hi

This is what I did just now....I created a file out of the pseudo string and passed it to be parsed with crimson.....it gives me an error irrespective of whether I turn Namespace awareness true or not!

What could the reason be?

Thanks
Prashanth
843834
Well, that seems to be classic dilemma. The same code works fine on my machine, but has trouble on yours. I'm running on 95 with JSDK 1.3 (build 1.3.0-C).
I'm afraid I can't help you any further, since I can't reproduce this behaviour.

Leo.
843834
Thanks anyways....guess I have to figure it out myself.
Prashanth
843834
It finally worked!!!! This is what I did. I had to delete the jar files in the \lib\ext directory of Java Runtime Environment and it started working. Thanks Leo you have been of great help.......

Prashanth
843834
Yeah, that's a valuable lesson. jar files in lib/ext, I believe, are accessed before stuff in the class path. Glad to hear it's working.
843834
P.S.
check out http://forums.java.sun.com/rewardFaq.jsp#assign

;-)
843834
Yup! I did....It would be helpful if yo could throw some light one one other question i've posted!

http://forum.java.sun.com/thread.jsp?forum=34&thread=150639

Thanks
843834
Thx for the DDs. I saw this question before, but haven't done anything like this and I'm afraid, I can't give you advice on that one, but my instincts tell me, you'd be better off with the database solution.

Good luck.
843834
Thanks for the info.
I had the same problem, I have it no more

Small note: I had to put the XALAN.jar file AFTER the crimson.jar and jaxp.jar in order to make it work.


1 - 14
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Aug 5 2015
Added on Jul 1 2015
#excel, #load, #oracle, #plsql
24 comments
3,570 views