Solved

Creating Excel from SQL

Posted on 2009-05-04
5
324 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 500 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

Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Oracle Date 6 42
CTIOS error on Windows 10 3 64
Replacing unrecognized function name in SQL Query 4 49
Creating a View from a CTE 15 50
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

752 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