Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Creating Excel from SQL

Posted on 2009-05-04
5
Medium Priority
?
329 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Issue: One Windows 2008 R2 64bit server on the network unable to connect to a buffalo Device (Linkstation) with firmware version 1.56. There are a total of four servers on the network this being one of them. Troubleshooting Steps: Connect via h…
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

688 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