Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Creating Excel from SQL

Posted on 2009-05-04
5
Medium Priority
?
335 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
ID: 24300391
0
 
LVL 25

Expert Comment

by:lwadwell
ID: 24300424
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
ID: 24300635
lwadwell - will this produce column headers?
0
 
LVL 25

Accepted Solution

by:
lwadwell earned 2000 total points
ID: 24300675
COBOLforever,

Yes.  They will be the column names/aliases.

lwadwell
0
 

Author Closing Comment

by:COBOLforever
ID: 31577819
Thanks for the help!
0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Background Information Recently I have fixed file server permission issues for one of my client. The client has 1800 users and one Windows Server 2008 R2 domain joined file server with 12 TB of data, 250+ shared folders and the folder structure i…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Via a live example, show how to take different types of Oracle backups using RMAN.
Suggested Courses

773 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