Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2004
    Location
    New York
    Posts
    45

    Unanswered: load data from xls file to oracle

    Hello,

    is there a way in Oracle to load data to a table from xls file.
    I need to create a procedure that will do it automaticaly every week or every two weeks.

    Custome file Data.xls located in D:\custome_data\data.xls

    Thank you.

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    you need to convert the xls file into a csv file or a flat file.
    there are many easy ways to do this.

    once you convert the file, you can use sqlloader to load the data.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Feb 2004
    Location
    New York
    Posts
    45
    Originally posted by The_Duck
    you need to convert the xls file into a csv file or a flat file.
    there are many easy ways to do this.

    once you convert the file, you can use sqlloader to load the data.
    I already did it
    I convert it to crv (comma sep. val) then I create a sql loadr
    then i create a bat file to execute it.
    but the thing is ...its so many steps to go trough

    I thought to have a stored proc in oracle that does it all
    without steps
    1 open original and convert it to crv
    2 execute bat

    Is there a way to do so ?

    Thank you

  4. #4
    Join Date
    Jan 2004
    Posts
    492
    Originally posted by alexis200487
    I already did it
    I convert it to crv (comma sep. val) then I create a sql loadr
    then i create a bat file to execute it.
    but the thing is ...its so many steps to go trough

    I thought to have a stored proc in oracle that does it all
    without steps
    1 open original and convert it to crv
    2 execute bat

    Is there a way to do so ?

    Thank you
    If its already in CSV format, you can write a quick load program using UTL_FILE. The file has to be placed on the server, but if you have the same columns every time, its very re-usable.

  5. #5
    Join Date
    Dec 2003
    Location
    Noida, India.
    Posts
    171
    As of now, there is no API which can help you access excel file directly from stored proc. You have following options
    - Use a programing language that can communicate with both excel as well as oracle (eg VB). This can be a complete automation of your xl-oracle dataload.
    - Or you convert excel to a format that oracle can read (CSV, XML). This conversion part could be manual depending on what format/tool you choose for conversion.

    From performance perspective, excel to csv to oracle would be best solution.

    Check out the SQL*XL tool which provides XL to database interface.
    http://www.oraxcel.com/projects/sqlxl/index.htm
    Oracle can do wonders !

  6. #6
    Join Date
    Feb 2004
    Location
    New York
    Posts
    45
    Originally posted by cmasharma
    As of now, there is no API which can help you access excel file directly from stored proc. You have following options
    - Use a programing language that can communicate with both excel as well as oracle (eg VB). This can be a complete automation of your xl-oracle dataload.
    - Or you convert excel to a format that oracle can read (CSV, XML). This conversion part could be manual depending on what format/tool you choose for conversion.

    From performance perspective, excel to csv to oracle would be best solution.

    Check out the SQL*XL tool which provides XL to database interface.
    http://www.oraxcel.com/projects/sqlxl/index.htm

    Thanks for a site, but while install process it says that I need one more exe to be able to install it
    sqlxl3[1].2 ?

    where to get this one ?

  7. #7
    Join Date
    Feb 2004
    Location
    New York
    Posts
    45
    Originally posted by ss659
    If its already in CSV format, you can write a quick load program using UTL_FILE. The file has to be placed on the server, but if you have the same columns every time, its very re-usable.

    I have a proc to read data from xls to oracle but while executing it gave me an error invalid directory path,
    I create a directory my_dir and grant permis on it to public and it still not working.

    Please help.

  8. #8
    Join Date
    Feb 2004
    Location
    New York
    Posts
    45
    Originally posted by alexis200487
    I have a proc to read data from xls to oracle but while executing it gave me an error invalid directory path,
    I create a directory my_dir and grant permis on it to public and it still not working.

    Please help.


    Can problem be because I use mu d:\temp directory where original xls file is located?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •