Solved

Creating Excel from SQL

Posted on 2009-05-04
5
314 Views
Last Modified: 2013-12-18
I have 130 tables that I need to apply specific SQL against, cut the results with column headers, and paste into an Excel spreadsheet, each column to an Excel column. The SQL I need to run is in a text file on my desktop. The SQL is executed against a database on a remote desktop connected via Secure Access to a remote site.

Currently, I am doing this by:
1. Cut the SQL from the desktop file
2. Paste into SQL Developer running on my remote desktop
3. Execute
4. Right click on SQL Dev results grid, select Export, Text, Clipboard
5. Paste into Excel on my desktop.

I will need to do this exercise several times to over 130 tables.

Is there an easier way? I only have SQL Developer, SQL Plus as database tools.

Attached a quick example.
Thanks.
Book5a.xls
0
Comment
Question by:COBOLforever
  • 2
  • 2
5 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
Comment Utility
0
 
LVL 25

Expert Comment

by:lwadwell
Comment Utility
Hi COBOLforever,

When I have to do things like this I user Perl.  There is a good package that allows you to create excel (xls) files based on SQL statements.

http://search.cpan.org/~tmtm/Spreadsheet-WriteExcel-FromDB-1.00/lib/Spreadsheet/WriteExcel/FromDB.pm

This requires Perl (obviously) and several packages including DBI - so it need to run on machine that can directly connect to the database.



lwadwell
0
 

Author Comment

by:COBOLforever
Comment Utility
lwadwell - will this produce column headers?
0
 
LVL 25

Accepted Solution

by:
lwadwell earned 500 total points
Comment Utility
COBOLforever,

Yes.  They will be the column names/aliases.

lwadwell
0
 

Author Closing Comment

by:COBOLforever
Comment Utility
Thanks for the help!
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now