Creating Excel from SQL

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
COBOLforeverAsked:
Who is Participating?
 
lwadwellCommented:
COBOLforever,

Yes.  They will be the column names/aliases.

lwadwell
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
0
 
lwadwellCommented:
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
 
COBOLforeverAuthor Commented:
lwadwell - will this produce column headers?
0
 
COBOLforeverAuthor Commented:
Thanks for the help!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.