?
Solved

How do I send SQL query results to a spreadsheet?

Posted on 2014-08-05
12
Medium Priority
?
207 Views
Last Modified: 2014-08-22
I have developed code that produces what I need (see below).

How can I redirect this output into a single spreadsheet where each table is a tab in the spreadsheet and the tab is named after the table name?

Thanks!



This is the code that works:

***************** (begin code) ******************************
use QRMAssistant

DECLARE @TheTableName VARCHAR(100) -- Table name  

DECLARE db_cursor CURSOR FOR  
SELECT name
FROM [QRMAssistant].[sys].[sysobjects]
Where xtype = 'u'

OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @TheTableName  

WHILE @@FETCH_STATUS = 0  
      BEGIN  
       
      SELECT t.TABLE_NAME as 'Table', c.COLUMN_NAME as 'Column Name' ,c.DATA_TYPE as 'Type' ,c.CHARACTER_MAXIMUM_LENGTH as 'Length',c.NUMERIC_PRECISION as 'Numeric length',c.NUMERIC_SCALE as 'Decimals',c.DATETIME_PRECISION as 'Date/Time'
      FROM INFORMATION_SCHEMA.Tables t
      INNER JOIN INFORMATION_SCHEMA.Columns c ON t.TABLE_CATALOG=c.TABLE_CATALOG AND t.TABLE_SCHEMA=c.TABLE_SCHEMA AND t.TABLE_NAME=c.TABLE_NAME
      WHERE t.TABLE_NAME = @TheTableName
      ORDER BY t.TABLE_NAME, c.COLUMN_NAME


      FETCH NEXT FROM db_cursor INTO @TheTableName  

      END  

CLOSE db_cursor  
DEALLOCATE db_cursor

***************** (end code) ******************************
0
Comment
Question by:Lenny Gray
  • 4
  • 3
  • 3
10 Comments
 
LVL 23

Expert Comment

by:nemws1
ID: 40242145
I have a Perl script to do exactly this, but there's no way to do it easily with SSMS or any other tool I know of.  Will be interesting to see if anybody has a nice solution for this.
0
 
LVL 23

Assisted Solution

by:nemws1
nemws1 earned 600 total points
ID: 40242147
Since you're already looping through a cursor, you might be able to work up something with OPENROWSET:
INSERT INTO OPENROWSET 
   ('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=c:\Test.xls;','select * from [Sheet1$]')
SELECT * FROM dbo.whatevertable

Open in new window


You need to turn on 'Ad Hoc Distributed Queries' on your server in order for this to work, though.
0
 
LVL 35

Expert Comment

by:David Todd
ID: 40242318
Hi,

If SSMS is displaying the results in grid view, then its just a copy and paste.

Is this a one-off or a repeated thing? If repeated, then you could do this in SSIS with each result being an individual query that has a destination of a spreadsheet.

On the other hand, why not use excel and vba to execute the query and deliver the results to the relevant sheets.

Just gotta look at the problem crooked.

HTH
  David
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
LVL 35

Expert Comment

by:David Todd
ID: 40242322
ps
In Excel VBA, Execute the query,
loop through the columns
use the fieldnames as headers.
Use the type to suggest formatting for the column.
next
Loop through the rows
Loop through the columns and copy the value into the cell
next
next
0
 
LVL 23

Expert Comment

by:nemws1
ID: 40242348
SSIS would be the preferred solution, but I haven't tried taking multiple results from a single call and tried to split them out to multiple sheets in Excel, but I would think its very do-able.
0
 

Author Comment

by:Lenny Gray
ID: 40251047
How do I save each table's columns to tabs in a worksheet where the tab name is the table name?
0
 
LVL 35

Expert Comment

by:David Todd
ID: 40251050
Hi,

As per what I said above about code in Excel - really easily.

If using SSIS then part of the connection to Excel specifies the tab. To use another tab you'll need another connection. But you'll need to make sure the first connection is closed before opening the second one as only one connection can write to an excel spreadsheet at one time.

Regards
   David
0
 

Author Comment

by:Lenny Gray
ID: 40253337
David -

Thanks for your assistance. I never coded in Excel VBA. Can you provide a template for me to mimic?

Thanks!
0
 
LVL 35

Accepted Solution

by:
David Todd earned 1400 total points
ID: 40254140
Hi,

Although it is querying an access database see these for starters
http://www.excel-vba.com/vba-code-2-12-SQL.htm
http://www.excel-sql-server.com/excel-sql-server-import-export-using-vba.htm

HTH
  David
0
 

Author Comment

by:Lenny Gray
ID: 40256679
Thanks, everyone, for the assist. Each of you provided the clues that I I use to generate the end result. When that happens, i will post the code here.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

862 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