2 Replies Latest reply: May 29, 2009 10:55 AM by gdarling - oracle RSS

    How to extract Oracle data into Excel file?

    user4587979
      For a small automation project I have to extract data from a table/
      tables and append it to the existing excel file and feed that excel
      file to a command that will load data into some other environment. I
      am totally new to this. So to get started I wanted to know,
      1) How to extract data from sample table Foo which has columns A,B,C
      and append these values as new columns to an existing excel say
      fooresults.csv ?
      2) Can I achieve this in pl/sql script or do I need to write unix or
      perl script or some other programing language, please advise?
        • 1. Re: How to extract Oracle data into Excel file?
          Bjoern Rost
          You can receive sql output in csv-style like this:
          SELECT A||','||B||','||C FROM FOO;
          Just call it from whatever script language you like.
          • 2. Re: How to extract Oracle data into Excel file?
            gdarling - oracle
            The "extract data from a table" part is easy, you could do that with VB/ADO, or .NET/ODP.NET. It's then a matter of taking that data and appending it to a spreadsheet that might be the hard part, and how you'd do that exactly is really more of a Microsoft question than an Oracle one.

            If you want to be able to do this from the database itself and your database is on Windows, you could use either [.NET Stored Procedures|http://www.oracle.com/technology/tech/dotnet/ode/index.html] if you can manipulate the spreadsheet in .net code, or you could also use Oracle's [COM Automation Feature|http://www.oracle.com/technology/tech/windows/com_auto/index.html] if you're handy with the COM object model for Excel.

            How you'd do that exactly via either .net or com or vb is the crux of the problem and is something you'd need to know before it turns into an Oracle question, but if you already know how to do that and now just need to figure out a way to do that from Oracle, either of the above might help.


            Hope it helps,
            Greg