Forum Stats

  • 3,837,476 Users
  • 2,262,262 Discussions
  • 7,900,297 Comments

Discussions

Loading 400 excel files without manually opening them???

BillC
BillC Member Posts: 1,069 Bronze Badge
edited Oct 2, 2008 12:41PM in SQL & PL/SQL
Hi,
I have 400 excel files with the first 5 lines header info - nothing to do with the columns...15 merged columns
then the 15 and another 3 columns with data.
about half way down the file 12 over the columns turn into 6 columns (2 merged each)

Can I write a PL/SQL script to load these?? how would you tell when a column begins?

Again I don't want to open and SaveAs 400 files...


Thank you for your help, Bill
Tagged:
«1

Comments

  • JustinCave
    JustinCave Member Posts: 30,293 Gold Crown
    I don't quite understand your description of the structure of the files.

    If the structure is regular and the database is on Windows, you could use Oracle Heterogeneous Services and the Excel ODBC driver to programmatically access and extract the data. I've done this a couple of times, it tends to be a bit painful because the spreadsheet structure is almost always changing over time, so you end up having to manipulate the extract code every time you discover a new permutation. It works reasonably well, though, definitely easier than trying to save off hundreds of CSV files (particularly if there are multiple worksheets).

    Justin
  • orawarebyte
    orawarebyte Member Posts: 3,838 Silver Badge
    edited Sep 16, 2006 3:17AM
    Can I write a PL/SQL script to load these?? how would
    you tell when a column begins?
    If yours this excel file is outsource as well no
    standard of columns heading then no luck otherwise if
    it is genertating from yours own system then pass the
    tag/flag at the start of record and then you can use in
    the where clause to filter it out by using HS service.


    Khurram
  • BillC
    BillC Member Posts: 1,069 Bronze Badge
    Hi Justin, Thanks for the post.. they aren't standard.

    the first 5 lines header info - nothing to do with the columns...
    (15 merged columns)

    Next the 18 columns with data (standard)
    But then about half way down the file 12 of the columns turn into 6 columns (2 merged columns each)

    I invisioned a UTL_FILE loop reading one line at a time conditionally ... but
    aren't Excel files in a binary or some format not readable as a text file?

    I was hoping to convert them with a command or know how to read them correctly using packages maybe meant for it.

    Khurram, I don't understand what you said.. sorry..
  • BillC
    BillC Member Posts: 1,069 Bronze Badge
    I just opened an XLS file with a text editor - TextPad and it has all the hex-decimal looking stuff...

    First step I would think would be a package command to convert to a different format.

    You know when we SaveAs CSV or as Text -- if there was a command or tool that could do that.

    Thanks, Bill
  • Jens Petersen
    Jens Petersen Member Posts: 4,730 Bronze Badge
    An example for using Heterogenous Connectivity can be found here
    http://asktom.oracle.com/pls/ask/f?p=4950:61:::::P61_ID:4406708207206#18830681837358

    Another option could be using Apache Jakarta POI
    http://www.oracle.com/technology/pub/articles/saternos_tables.html
  • BillC
    BillC Member Posts: 1,069 Bronze Badge
    Apache Jakarta POI looks interesting...

    I extracted the files to:
    G:\1WORK\EXCEL\XL2ETB

    ran the bat and captured the output:
    Begin processing.
    Using working directory G:\1WORK\EXCEL\XL2ETB

    but that was it...
    Checking further.. for a setup or requirements.. db running.. etc.
    How do I extract what is in the jar file? It has been awhile since I looked at Java.
  • BillC
    BillC Member Posts: 1,069 Bronze Badge
    Got farther... is there a forum to ask for help with this?

    G:\1WORK\EXCEL\XL2ETB>java -cp XL2ExternalTables.jar;jakarta-poi-1.5.1-final-200
    20615.jar;jakarta-poi-contrib-1.5.1-final-20020615.jar com.saternos.database.uti
    lities.ExternalTableGenerator new_department_data.xls

    Begin processing.
    Using working directory G:\1WORK\EXCEL\XL2ETB
    Exception in thread "main" java.lang.NoSuchMethodError
    at com.saternos.database.utilities.ExternalTable.<init>(ExternalTable.ja
    va:62)
    at com.saternos.database.utilities.ExternalTableGenerator.processWorkook
    (ExternalTableGenerator.java:184)
    at com.saternos.database.utilities.ExternalTableGenerator.execute(Extern
    alTableGenerator.java:106)
    at com.saternos.database.utilities.ExternalTableGenerator.main(ExternalT
    ableGenerator.java:42)
  • BillC
    BillC Member Posts: 1,069 Bronze Badge
    The article said:
    The project was tested with Java Runtime Environment (JRE) 1.4.2_03 but should work with any JRE that can run POI.

    My version of java...

    G:\1WORK\EXCEL\XL2ETB>java -version
    java version "1.3.1_02"
    Java(TM) 2 Runtime Environment, Standard Edition (build 1.3.1_02-b02)
    Java HotSpot(TM) Client VM (build 1.3.1_02-b02, mixed mode)
  • BillC
    BillC Member Posts: 1,069 Bronze Badge
    Got the Apache Jakarta POI example working on my office machine..
    my home java environment was the problem...

    I tahnk you.. I will get into the java code now and see about creating a loop.. etc..

    Thanks again, Bill
  • 10690
    10690 Member Posts: 39
    edited Oct 2, 2008 12:40PM
    Good morning

    I have problems with the Jakarta POI example. The problem is my home java environment

    How can I configurate this java environment?

    My java version is:


    C:\Reporte\XL2ETB>java -version
    java version "1.3.1_01"
    Java(TM) 2 Runtime Environment, Standard Edition (build 1.3.1_01)
    Java HotSpot(TM) Client VM (build 1.3.1_01, mixed mode)

    And the error is:

    C:\Reporte\XL2ETB>java -cp XL2ExternalTables.jar;jakarta-poi-1.5.1-final-2002061
    5.jar;jakarta-poi-contrib-1.5.1-final-20020615.jar com.saternos.database.utiliti
    es.ExternalTableGenerator new_department_data.xls
    Begin processing.
    Using working directory C:\Reporte\XL2ETB
    Exception in thread "main" java.lang.NoSuchMethodError
    at com.saternos.database.utilities.ExternalTable.<init>(ExternalTable.ja
    va:62)
    at com.saternos.database.utilities.ExternalTableGenerator.processWorkook
    (ExternalTableGenerator.java:184)
    at com.saternos.database.utilities.ExternalTableGenerator.execute(Extern
    alTableGenerator.java:106)
    at com.saternos.database.utilities.ExternalTableGenerator.main(ExternalT
    ableGenerator.java:42)

    Edited by: albrico on Oct 2, 2008 12:38 PM
This discussion has been closed.