Results 1 to 10 of 10
  1. #1
    Join Date
    Jul 2009
    Posts
    6

    Question Unanswered: How to read Excel from PL/SQL?

    Can you anyone tell me how to read the first column of Excel file and display the same using PL/SQL?

    For example: Excel file contains

    Employee id:
    1
    2
    3
    4
    5

    Instead of using the following SQL command in PL/SQL
    select * from ps_vendor a
    where a.setid = '007' and a.vendor_id in ('1', '2', '3', '4', '5');

    I would like to open the excel file read it and provide the same as input in the sql statement.

    Any idea??

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I haven't got a scooby, but then again PL/SQL is Oracle...

    so do you want this post moving there....?
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jul 2009
    Posts
    6

    Question How to read Excel from PL/SQL?

    Can you anyone tell me how to read the first column of Excel file and display the same using PL/SQL?

    For example: Excel file contains

    Employee id:
    1
    2
    3
    4
    5

    Instead of using the following SQL command in PL/SQL
    select * from ps_vendor a
    where a.setid = '007' and a.vendor_id in ('1', '2', '3', '4', '5');

    I would like to open the excel file read it and provide the same as input in the sql statement.

    Any idea??

  4. #4
    Join Date
    Jul 2009
    Posts
    6
    thanks I just posted in oracle

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Any idea??
    You need to reverse engineer Excel & program PL/SQL to read & parse Excel files.

    Alternatively you could install Oracle client s/w on PC running Excel
    & then use ODBC to allow Excel to log into Oracle directly & issue SQL.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  6. #6
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    or set us a heterogeneous ODBC link to the excel table in the oracle database.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  7. #7
    Join Date
    Jul 2009
    Posts
    6
    Does the following works in PL/SQL, OPENROWSET seems to be an invalid identifier :-(

    select * from employee a
    where a.setid = '007' and a.vendor_id in (OPENROWSET('Microsoft.Jet.OLEDB.4.0',
    'Excel 8.0;Database=C:\abc.xls', 'SELECT * FROM [Sheet1$]'));

  8. #8
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Does the following works in PL/SQL,
    Why are you asking a question, when you already know the answer?

    PL/SQL runs deep inside Oracle RDBMS engine & knows nothing about the Micro$oft world.

    You have been told twice what needs to be done.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  9. #9
    Join Date
    Jul 2009
    Posts
    6
    Thanks, since I am new to SQL I was not aware of the information.

  10. #10
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Alternatively, you could have Excel create a text file; which could then be treated as an External Table by Oracle.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

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