The Best Kept Secret About SQL Query Analyzer
-
The tip is great for those that weren't aware of it but as a previous poster noted these exports don't handle column formatting (like a SQL CHAR column that contains 0400160 - this will end up being imported as 400160). I am still looking for a simple to use tool that will export the query results to an excel spreadsheet with the following criteria:
1)column headers
2)preserve the sql data type (date columns remain as date, character columsn remain as text
3)the user does not have to manually format the excel page first.
Now if someone could provide a way of meeting the above that would be, in my opinion, The Best Kept Secret! Any takers?
-
The only way I can think of is with my earlier posted VB Script. You have to enter the Query, put the column headers in the script, and edit the 'loop though the recordset', none of which is brilliant.
But it does meet the requirement. 
Dave J
Dim connectionString'The connection string goes here
connectionString = "Provider = SQLOLEDB;Data Source=(local);" & _
"Trusted_Connection=Yes;Initial Catalog=Northwind;"
Dim Query
' The query goes here
Query = "SELECT [CompanyName], [ContactName], [ContactTitle], [Address], [City], [Region], " & _
"[PostalCode], [Country], [Phone], [Fax] FROM [Northwind].[dbo].[Customers]"
Const adOpenStatic = 3
Const adLockOptimistic = 3
Dim i
Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")
objConnection.Open connectionString
' creating the Excel object application
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
objExcel.ScreenUpdating = False
Set objWorkbook = objExcel.Workbooks.Add()
Set objWorksheet = objWorkbook.Worksheets(1)
'msgBox Query
objRecordSet.Open Query , objConnection, adOpenStatic, adLockOptimistic
i = 1
objRecordSet.MoveFirst
objExcel.Columns("A:J").Select
objExcel.Selection.NumberFormat = "@"
' This is setting the column names, font, colors, etc.
' This code can be simplified by ranging if desired.
objExcel.Cells(1, 1).Value = "Company Name"
objExcel.Cells(1, 1).Font.Size = 10
objExcel.Cells(1, 1).Font.Bold = TRUE
objExcel.Cells(1, 1).Interior.ColorIndex = 6
objExcel.Cells(1, 2).Value = "Contact Name"
objExcel.Cells(1, 2).Font.Size = 10
objExcel.Cells(1, 2).Font.Bold = TRUE
objExcel.Cells(1, 2).Interior.ColorIndex = 6
objExcel.Cells(1, 3).Value = "Contact Title"
objExcel.Cells(1, 3).Font.Size = 10
objExcel.Cells(1, 3).Font.Bold = TRUE
objExcel.Cells(1, 3).Interior.ColorIndex = 6
objExcel.Cells(1, 4).Value = "Address"
objExcel.Cells(1, 4).Font.Size = 10
objExcel.Cells(1, 4).Font.Bold = TRUE
objExcel.Cells(1, 4).Interior.ColorIndex = 6
objExcel.Cells(1, 5).Value = "City"
objExcel.Cells(1, 5).Font.Size = 10
objExcel.Cells(1, 5).Font.Bold = TRUE
objExcel.Cells(1, 5).Interior.ColorIndex = 6
objExcel.Cells(1, 6).Value = "Region"
objExcel.Cells(1, 6).Font.Size = 10
objExcel.Cells(1, 6).Font.Bold = TRUE
objExcel.Cells(1, 6).Interior.ColorIndex = 6
objExcel.Cells(1, 7).Value = "PostalCode"
objExcel.Cells(1, 7).Font.Size = 10
objExcel.Cells(1, 7).Font.Bold = TRUE
objExcel.Cells(1, 7).Interior.ColorIndex = 6
objExcel.Cells(1, 8).Value = "Country"
objExcel.Cells(1, 8).Font.Size = 10
objExcel.Cells(1, 8).Font.Bold = TRUE
objExcel.Cells(1, 8).Interior.ColorIndex = 6
objExcel.Cells(1, 9).Value = "Phone"
objExcel.Cells(1, 9).Font.Size = 10
objExcel.Cells(1, 9).Font.Bold = TRUE
objExcel.Cells(1, 9).Interior.ColorIndex = 6
objExcel.Cells(1, 10).Value = "Fax"
objExcel.Cells(1, 10).Font.Size = 10
objExcel.Cells(1, 10).Font.Bold = TRUE
objExcel.Cells(1, 10).Interior.ColorIndex = 6
objExcel.Range("A1:J1").Borders.LineStyle = True
Do Until objRecordset.EOF
i = i + 1
' Now we are getting the data and highlighting certain columns
objExcel.Cells(i, 1).Value = objRecordset.Fields.Item("CompanyName")
objExcel.Cells(i, 1).Font.Size = 10
objExcel.Cells(i, 1).Borders.LineStyle = True
objExcel.Cells(i, 2).Value = objRecordset.Fields.Item("ContactName")
objExcel.Cells(i, 2).Font.Size = 10
objExcel.Cells(i, 2).Borders.LineStyle = True
objExcel.Cells(i, 3).Value = objRecordset.Fields.Item("ContactTitle")
objExcel.Cells(i, 3).Font.Size = 10
objExcel.Cells(i, 3).Borders.LineStyle = True
objExcel.Cells(i, 4).Value = objRecordset.Fields.Item("Address")
objExcel.Cells(i, 4).Font.Size = 10
objExcel.Cells(i, 4).Borders.LineStyle = True
objExcel.Cells(i, 5).Value = objRecordset.Fields.Item("City")
objExcel.Cells(i, 5).Font.Size = 10
objExcel.Cells(i, 5).Borders.LineStyle = True
objExcel.Cells(i, 6).Value = objRecordset.Fields.Item("Region")
objExcel.Cells(i, 6).Font.Size = 10
objExcel.Cells(i, 6).Borders.LineStyle = True
objExcel.Cells(i, 7).Value = objRecordset.Fields.Item("PostalCode")
objExcel.Cells(i, 7).Font.Size = 10
objExcel.Cells(i, 7).Borders.LineStyle = True
objExcel.Cells(i, 8).Value = objRecordset.Fields.Item("Country")
objExcel.Cells(i, 8).Font.Size = 10
objExcel.Cells(i, 8).Borders.LineStyle = True
objExcel.Cells(i, 9).Value = objRecordset.Fields.Item("Phone")
objExcel.Cells(i, 9).Font.Size = 10
objExcel.Cells(i, 9).Borders.LineStyle = True
objExcel.Cells(i, 10).Value = objRecordset.Fields.Item("Fax")
objExcel.Cells(i, 10).Font.Size = 10
objExcel.Cells(i, 10).Borders.LineStyle = True
objRecordset.MoveNext
Loop
' automatically fits the data to the columns
Set objRange = objWorksheet.UsedRange
objRange.EntireColumn.Autofit()
objExcel.ScreenUpdating = True
' cleaning up
objRecordSet.Close
objConnection.Close
-
Dave, I guess I wasn't very clear in my requirements. I wanted something that once done would work for the output of any query/sp without having to set up a custom table each time. I do a lot of one time queries that need to be put into a spreadsheet - doing manual steps to craete code/format tables etc each time is time consuming esp. if there are 50+ columns in the spreadsheet!. I have come up with an approach which I think will work:
1) Capture the resultset of the query/sp in a temporary table usuing a loopbback linked server.
2) Extract the table column names/types from syscolumns.
3) Create the column headers in the Excel spreadsheet using T-SQL. This should format the columns as the types specified. (If the columns aren't formatted then would need to use sp_OA.. to access the Excel object directly and format them)
4) Save the temporary table from 1) into the spreadsheet.
The above steps could be put into a sp that takes the query string and a file name as input parameters and optionally could even email the file.
-
I've got this, but to be fair I don't use it. It is very heavily based on a script posted on this site by David A. Long, currently No 3 in the all time top scripts list:
create PROCEDURE WRITE_EXCEL_FILE
@SQLServerName VarChar(50) = @@ServerName, --Server to run on
@columnNames VarChar(255) = Null, --Names only, used in insert Query
@columnTypes VarChar(255) = Null, --Names & Types
@query VarChar(7999) = Null, --The Query
@fileLocation varChar(255) = Null --Output File
AS
/*
Object Name: WRITE_EXCEL_FILE
Author: David Jackson
Date created: 16 December 2004
Version: 1
Input Parameters: Server Name to run on, Column Names, Query to run, File Location
Output Parameters: Creates/append XLS file in @fileLocation
Calls:
Called By:
Description:
Takes a Query & outputs to an Excel Worksheet. Bit clunky at the moment, needs work.
Example:
exec zdba..WRITE_EXCEL_FILE
@columnNames = '(au_lname, au_fname) ', --Names only, used in insert Query
@columnTypes = '(au_lname Text, au_fname Text)', --Names & Types
@query = 'select au_lname, au_fname from pubs..authors', --The Query
@fileLocation = 'C:\Test' --Output File
*/
-- Create XLS script - 5th Oct 2003
--
-- Designed for Agent scheduling, turn on "Append output for step history"
--
-- Search for %%% to find adjustable constants and other options
--
-- Uses OLE for ADO and OLE DB to create the XLS file if it does not exist
-- Linked server requires the XLS to exist before creation
-- Uses OLE ADO to Create the XLS Worksheet for use as a table by T-SQL
-- Uses Linked Server to allow T-SQL access to XLS table
-- Uses T-SQL to populate te XLS worksheet, very fast
--
PRINT 'Begin CreateXLS script at ' + RTRIM(CONVERT(varchar(24),GETDATE(),121)) + ' '
PRINT ''
SET NOCOUNT ON
DECLARE @Conn int -- ADO Connection object to create XLS
, @hr int -- OLE return value
, @src varchar(255) -- OLE Error Source
, @desc varchar(255) -- OLE Error Description
, @Path varchar(255) -- Drive or UNC path for XLS
, @Connect varchar(255) -- OLE DB Connection string for Jet 4 Excel ISAM
, @WKS_Created bit -- Whether the XLS Worksheet exists
, @WKS_Name varchar(128) -- Name of the XLS Worksheet (table)
, @ServerName nvarchar(128) -- Linked Server name for XLS
, @DDL varchar(8000) -- Jet4 DDL for the XLS WKS table creation
, @SQL varchar(8000) -- INSERT INTO XLS T-SQL
, @Recs int -- Number of records added to XLS
, @Log bit -- Whether to log process detail
-- Init variables
SELECT @Recs = 0
, @Log = 1 -- %%% 1 = Verbose output detail, helps find problems, 0 = minimal output detail
-- %%% assign the UNC or path and name for the XLS file, requires Read/Write access
-- must be accessable from server via SQL Server service account
-- & SQL Server Agent service account, if scheduled
-- UNC Paths do not appear to work here
-- As a hack setup a two step job to copy excel file after creation to central location
-- %%% assign the Linked Server name for the XLS population
SET @ServerName = 'QUERY_TO_EXCEL'
SET @Path = @FileLocation
SET @Path = @Path + '\' + @ServerName + Convert(Varchar(10),Year(GetDate())) + Right('00' + CONVERT(varchar(10),Month(GETDATE())),2) + '.xls'
IF @Log = 1 PRINT char(9) + @Path
-- assign the ADO connection string for the XLS creation
SET @Connect = 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=' + @Path + ';Extended Properties=Excel 8.0'
-- %%% Rename Table as required, this will also be the XLS Worksheet name
--one sheet per day
SET @WKS_Name = '[' + @SQLServerName + '_' + CONVERT(varchar(10),GETDATE(),112) + ']'
-- one sheet per month
SET @WKS_Name = '[' + @SQLServerName + ' ' + substring(CONVERT(varchar(10),GETDATE()),1,4) + CONVERT(varchar(10),Year(GETDATE())) + ']'
SET @WKS_Name = Replace(@WKS_Name,'-','_')
SET @WKS_Name = Replace(@WKS_Name,' ','_')
-- %%% Table creation DDL, uses Jet4 syntax,
-- Text data type = varchar(255) when accessed from T-SQL
SET @DDL = 'CREATE TABLE ' + @WKS_Name + ' ' + @columnTypes + ' '
-- %%% T-SQL for table population, note the 4 part naming required by Jet4 OLE DB
-- INSERT INTO SELECT, INSERT INTO VALUES, and EXEC sp types are supported
-- Linked Server does not support SELECT INTO types
SET @SQL = 'INSERT INTO ' + @ServerName + '...' + @WKS_Name + ' ' + @columnNames + ' '
SET @SQL = @SQL + @Query
IF @Log = 1 PRINT char(9) + @SQL
--==================================================
IF @Log = 1 PRINT 'Created OLE ADODB.Connection object'
-- Create the Conn object
EXEC @hr = sp_OACreate 'ADODB.Connection', @Conn OUT
IF @hr <> 0 -- have to use <> as OLE / ADO can return negative error numbers
BEGIN
-- Return OLE error
EXEC sp_OAGetErrorInfo @Conn, @src OUT, @desc OUT
SELECT Error=convert(varbinary(4),@hr), Source=@src, Description=@desc
RETURN
END
IF @Log = 1 PRINT char(9) + 'Assigned ConnectionString property'
-- Set a the Conn object's ConnectionString property
-- Work-around for error using a variable parameter on the Open method
EXEC @hr = sp_OASetProperty @Conn, 'ConnectionString', @Connect
IF @hr <> 0
BEGIN
-- Return OLE error
EXEC sp_OAGetErrorInfo @Conn, @src OUT, @desc OUT
SELECT Error=convert(varbinary(4),@hr), Source=@src, Description=@desc
RETURN
END
IF @Log = 1 PRINT char(9) + 'Open Connection to XLS, for file Create or Append'
-- Call the Open method to create the XLS if it does not exist, can't use parameters
EXEC @hr = sp_OAMethod @Conn, 'Open'
IF @hr <> 0
BEGIN
-- Return OLE error
EXEC sp_OAGetErrorInfo @Conn, @src OUT, @desc OUT
SELECT Error=convert(varbinary(4),@hr), Source=@src, Description=@desc
RETURN
END
-- %%% This section could be repeated for multiple Worksheets (Tables)
IF @Log = 1 PRINT char(9) + 'Execute DDL to create ''' + @WKS_Name + ''' worksheet'
-- Call the Execute method to Create the work sheet with the @WKS_Name caption,
-- which is also used as a Table reference in T-SQL
-- Neat way to define column data types in Excel worksheet
-- Sometimes converting to text is the only work-around for Excel's General
-- Cell formatting, even though the Cell contains Text, Excel tries to format
-- it in a "Smart" way, I have even had to use the single quote appended as the
-- 1st character in T-SQL to force Excel to leave it alone
EXEC @hr = sp_OAMethod @Conn, 'Execute', NULL, @DDL, NULL, 129 -- adCmdText + adExecuteNoRecords
-- 0x80040E14 for table exists in ADO
IF @hr = 0x80040E14
-- kludge, skip 0x80042732 for ADO Optional parameters (NULL) in SQL7
OR @hr = 0x80042732
BEGIN
-- Trap these OLE Errors
IF @hr = 0x80040E14
BEGIN
PRINT char(9) + '''' + @WKS_Name + ''' Worksheet exists for append'
SET @WKS_Created = 0
END
SET @hr = 0 -- ignore these errors
END
IF @hr <> 0
BEGIN
-- Return OLE error
EXEC sp_OAGetErrorInfo @Conn, @src OUT, @desc OUT
SELECT Error=convert(varbinary(4),@hr), Source=@src, Description=@desc
RETURN
END
IF @Log = 1 PRINT 'Destroyed OLE ADODB.Connection object'
-- Destroy the Conn object, +++ important to not leak memory +++
EXEC @hr = sp_OADestroy @Conn
IF @hr <> 0
BEGIN
-- Return OLE error
EXEC sp_OAGetErrorInfo @Conn, @src OUT, @desc OUT
SELECT Error=convert(varbinary(4),@hr), Source=@src, Description=@desc
RETURN
END
-- Linked Server allows T-SQL to access the XLS worksheet (Table)
-- This must be performed after the ADO stuff as the XLS must exist
-- and contain the schema for the table, or worksheet
IF NOT EXISTS(SELECT srvname from master.dbo.sysservers where srvname = @ServerName)
BEGIN
IF @Log = 1 PRINT 'Created Linked Server ''' + @ServerName + ''' and Login'
EXEC sp_addlinkedserver @server = @ServerName
, @srvproduct = 'Microsoft Excel Workbook'
, @provider = 'Microsoft.Jet.OLEDB.4.0'
, @datasrc = @Path
, @provstr = 'Excel 8.0'
-- no login name or password are required to connect to the Jet4 ISAM linked server
EXEC sp_addlinkedsrvlogin @ServerName, 'false'
END
-- Have to EXEC the SQL, otherwise the SQL is evaluated
-- for the linked server before it exists
EXEC (@SQL)
PRINT char(9) + 'Populated ''' + @WKS_Name + ''' table with ' + CONVERT(varchar,@@ROWCOUNT) + ' Rows'
-- %%% Optional you may leave the Linked Server for other XLS operations
-- Remember that the Linked Server will not create the XLS, so remove it
-- When you are done with it, especially if you delete or move the file
IF EXISTS(SELECT srvname from master.dbo.sysservers where srvname = @ServerName)
BEGIN
IF @Log = 1 PRINT 'Deleted Linked Server ''' + @ServerName + ''' and Login'
EXEC sp_dropserver @ServerName, 'droplogins'
END
SET NOCOUNT OFF
PRINT ''
PRINT 'Finished CreateXLS script at ' + RTRIM(CONVERT(varchar(24),GETDATE(),121)) + ' '
SET QUOTED_IDENTIFIER OFF
GO -
A big let down after reading the title and after reading about how it seems you are going to be told how to get the results from the grid.
And yes, it is obvious that if you format the results to tab delimited it will fit into Excel without a fuss.
But the article contained no secrets and no useful tips that any half decent person could have worked out in 30 seconds.
-
Test the query in QA. then copy the working query.
Open Excel.
from data menu select get external data.
Select correct odbc connection
Paste Query into Excel Query builder.
Press Get data
Select in Sheet where you want results.
done...
even has pretty sortable column headings.
-
Once you have written your query in QA stick the results into a global temporary table (SELECT * INTO ##WeAreAllClearlyBonkers FROM sometable). Use a query table to get the data from this table. There is an option to include headers which will probably by default return the headers. You can re-use this query table to return the data from the temporary table.
You should definitely be very careful when copying and pasting anything into excel. Excel seems to do a fair amount of guess work behind the scenes not all of it desirable.
-
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Good comment from Dan Collier: Maximum characters per column = 8100 (why 8100? I usually do 7999)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Both values seem a little arbitrary.
Why not use the maximun allowed, 8192?
-
Uh huh... The only difference between what you and the author did is you didn't list all the steps.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Yakov,
As many have said, this is (or should be) common knowledge. But, I for one, appreciate your boldness for publishing what you did. You found a nifty tool in Query Analyzer and wanted to share it and that's what forums are supposed to be about... sharing of information. Thank you for sharing the information you found and please don't take the lesser responses to your article as an insult. Your posting has sparked many replies and that's also what a forum is supposed to be about. Judging from the favorable responses from those relatively new to the forum, you've done an outstanding job.
Sushila and Ivan... your comments were right on.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
At the risk of incurring wrath from the experts.....
I often tend to do this in a different way, bear with me - its pretty quick once you have done it a couple of times (I actually wrote a macro but I am doing it longhand)
Create the qry as normal in Qry Analyser, open up Excel, click, Data, Get External Data, new Qry, select doubleclick the database (assuming you have an ODDC link to it)
Then click '>', next, next, next, select 'View data or edit Qry' finish
( I know its nothing like the Qry that you want at this stage )
Click the SQL button and then Copy and paste your Query from the Analyser over the qry that is already in the box, click File, Return to sheet
As well as maintaining the column headers - you can also then save the worksheet as an XLT, Excel Template so that it autorefreshes when you open it again
Dave
-
Hey thanks for the new trick. It was news to me and I will use it moving forward.
Scott
-
Hey All,
This is not the first time that I am not impressed by an article on this news group. All these techniques are well known, not "best kept secrets". If its a secret at all then its the worst kept. Thanks to grasshopper above who showed the method of breaking out the text into different columns once in excel. Now, I have a much better method to achieve the same results faster and more efficiently into excel. In excel, click "data" -> click "get external data" click -> "new database query". If you do not have this feature installed then you will need your office 200whatever CD and add the necessary components. Just follow the wizard. You will need to have and select an ODBC connection. Once done, avoid using the wizard offered and you will come to a window with many options for selecting and joining tables from you database connection. Click the "SQL" button at the top and put you query right into the window. This will run and return all the data directly into excel sheet all properly formatted, each field in its own column. How's that for a little secret 😉 all the best.
-
wow... im reading through the thread now and the SQL vets are really kicking the newbies a$$es on this one. Hint: if this stuff is new to you or you were impressed by this article, say nothing. You'd only look foolish defending it. IMO the author is a goof with all his exclamation points!!!!!!!!!
-
The article was pretty good, but it was kind of anti-climatic. I realize that there are probably lots of people that did not already know the things in the article, but being able to paste the header line into Excel isn't the best anything. It needed a more appropriate title.
My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url]
Viewing 15 posts - 61 through 75 (of 94 total)
You must be logged in to reply to this topic. Login to reply