write result in excel file.
-
Hi all,
I have query running fine but need to put the result directly in to excel file. How can i make it to do that.
Let me know please
-
Providing the excel file exists
INSERT INTO
OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\temp\book1.xls', [sheet1$])
(col1,col2,col3)
SELECT col1,col2,col3 FROM
Far away is close at hand in the images of elsewhere.
Anon. -
It is giving me this error:
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
[OLE/DB provider returned message: Unspecified error]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005: ].
Thanks
-
SQLRep,
Try to do it using 2 other techniques:
1. Use Data Transformation Services (DTS). Specify Destination as your Excel file and schedule the package or "Run Now"
2. Create a view with your query in the database. Open Excel. Use Data menu Pivot Tables or Get External Data and connect to the view. It will get you your results. It may be that you need ODBC source to your database created on your computer first.
Let us now if you need more help
Regards,Yelena Varsha
-
from within query analyzer and not an appliction, i created an excel file with the three columns exactly as david suggested and ran this code:
INSERT INTO
OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\Book1.xls', [sheet1$])
(col1,col2,col3)
SELECT 1 as col1,2 as col2,3 as col3
no problems, but if i had the file open in excel and then tried to run the code, i got these errors:
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. The provider did not give any information about the error.
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005: The provider did not give any information about the error.].
make sure the file is not currently open in excel and try again.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Like Microsoft Query, can we run sql query and return the results in Excel sheet?
Why Micro soft not including this feature in the Query analyser?
R. M. Joseph
-
the grid view of Query Analyzer returns results in tab delimited format; you can cop and past results directly into excel from that;
search SSC for excel and you'll see plenty of other examples for exporting results directly to excel; David's example is very good and very efficient, i would suggest sticking with that.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
How do you setup the excel file (details please)?
If there's no answer to that then I have my answer to my error message
. -
Basically, the excel file needs to have a column header for every column fromt he query: davids example had 3 columns:
col1 col2 col3 so this command worked perfectly:
INSERT INTO
OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\Book1.xls', [sheet1$])
(col1,col2,col3)
SELECT 1 as col1,2 as col2,3 as col3
if you have 20 columns, you will want to make sure row 1 fo the excel spreadsheet has all 20 column headers in place for you; change the names of col1...etc to the real column names for clarity.
then change the SQL to the real query.
ie: this is the column names for my excel document; they go in row1 from A to H columns. I can format them for bold /color etc with no problems:
Fname Fname Lname addr1 addr2 city state zip then i run this command; note that the document is still called Book1 and the worksheet I'm going to play with is still the first sheet:
INSERT INTO
OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\Book1.xls', [sheet1$])
(Fname, Lname, addr1, addr2, city, state, zip)
SELECT Fname, Lname, addr1, addr2, city, state, zip from CustomerAddress Where State='FL'
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
How do you setup the excel file (details please)? Unless that is is a rhetorical question
..... thenYou can't using the code I posted, it relies on the fact that the xls file exists with sufficient number of column headers. To create the file use DTS.
If the file did not exist then you would get a different error, not the one posted, which you will get if the file is in use.
However I also get the error if try to specify the filename using UNC even though full access to the file is available
Have not been abale to find the cause yet
Far away is close at hand in the images of elsewhere.
Anon. -
You can use this stored proc.
Just supply file name, path, and SQL query
/******************************************************************************************
'
' PROCEDURE: dbo.HPSP_UT_ExcelReport
'
' Author: Leah Kats
' Created Date: 05/25/2005
' This utility stored procedure will ALTER xls spreassheet
based on the query passed
'*******************************************************************************************/
ALTER PROC dbo.HPSP_UT_ExcelReport
@FileName varchar(25)
,@SQL varchar(8000)
,@WKS_Name varchar(100) -- spreadsheet name
,@DDL varchar(500) -- name of the columns
,@FilePath varchar(250)
AS
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(500) -- 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
, @xlsDDL varchar(1000)
, @LocalDDL varchar(1000)
, @ServerName nvarchar(128) -- Linked Server name for XLS
, @Recs int -- Number of records added to XLS
, @Log bit -- Whether to log process detail
DECLARE @chkfile int, @FileCount int, @PathToCheck varchar(500)
-- Init variables
SELECT @Recs = 0
, @Log = 1
-------------------------------------------------------------------------------------------------------
-- assign a path and name for the XLS file
-------------------------------------------------------------------------------------------------------
SET @Path = @FilePath + @FileName + '_' + CONVERT(varchar(10),GETDATE(),110) --+ '_1' +'.xls'
----------------------------------------------------------------
-- check if file exists, if yes, create a new one, don't append
----------------------------------------------------------------
SET @FileCount = 1
WHILE @FileCount <= 100
BEGIN
SET @PathToCheck = @Path + '_' + CAST(@FileCount as varchar) + '.xls'
exec master..xp_fileexist @PathToCheck, @chkfile output
IF @chkfile = 0
BEGIN
SET @Path = @Path + '_' + CAST(@FileCount as varchar) + '.xls'
BREAK
END
SET @FileCount = @FileCount + 1
END
-------------------------------------------------------------------------------------------------------
-- 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'
-------------------------------------------------------------------------------------------------------
-- assign the Linked Server name for the XLS population
-------------------------------------------------------------------------------------------------------
SET @ServerName = 'EXCEL_TEST'
SET @xlsDDL = 'CREATE TABLE '+@WKS_Name+ ' (' + @DDL + ')'
SET @LocalDDL = REPLACE(@DDL, 'text', ' ')
SET @sql = 'INSERT INTO '+@ServerName+'...'+@WKS_Name + ' (' + @LocalDDL + ') ' + @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
BEGIN
-- Return OLE error
EXEC sp_OAGetErrorInfo @Conn, @src OUT, @desc OUT
SELECT Error=convert(varbinary(4),@hr), Source=@src, <A href="mailto
escription=@desc">Description=@descEXEC @hr = sp_OADestroy @Conn
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, <A href="mailto
escription=@desc">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, <A href="mailto
escription=@desc">Description=@desc RETURN
END
EXEC @hr = sp_OAMethod @Conn, 'Execute', NULL, @xlsDDL, NULL, 129 -- adCmdText + adExecuteNoRecords
IF @hr = 0x80040E14
BEGIN
PRINT char(9)+''''+@WKS_Name+''' Worksheet exists for append'
SET @WKS_Created = 0
END
BEGIN
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, <A href="mailto
escription=@desc">Description=@descRETURN
END
IF @Log = 1 PRINT 'Destroyed OLE ADODB.Connection object'
--!!!!!!!!!!!!!! IMPORTANT !!!!!!!!!!!!!!!!!!!
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, <A href="mailto
escription=@desc">Description=@descRETURN
END
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'
EXEC sp_addlinkedsrvlogin @ServerName, 'false'
END
EXEC (@SQL)
PRINT char(9)+'Populated '''+@WKS_Name+''' table with '+CONVERT(varchar,@@ROWCOUNT)+' Rows'
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
Leah Kats
-
Leah Kats:
Would you please provide an example of how to properly run your script.
Perhaps use the following in the example:
@FileName = Test_File.xls,
@sql = select col1, col2, col3 from mytable (is select * possible)
,@WKS_Name = MyWorkSheet -- spreadsheet name
,@DDL = Column1,Column2, Column3 -- name of the columns
,@FilePath = c:\temp\
You said "Just supply file name, path, and SQL query" but don't you also have to supply the WKS_name and DDL information?
Thanks
Howard
-
Sure, you have to supply all the params.
hese is the way I am running it:
exec HPSP_UT_ExcelReport 'FileName', @sql, 'SpreadsheetName', 'COL1_Name text, COL2_Name text'
,'\\ServerName\path\'
@sql - this is a sql statement you want to run (it will be executed as dynamic SQL)
'COL1_Name text, COL2_Name text' - those are headers on the worksheet (the column names). Text stands for data type.
Leah Kats
-
Leah:
Thanks for your fast reply.
I tried your procedure with the following:
exec dbo.HPSP_UT_ExcelReport 'excel_file','select Check_JE_Type_Code,[Description] from check_je_type','Worksheet_01',
'Check_JE_Type_Code text, Description text','\\server02\c$\temp\'
The spreadsheet excel_file_10-07-2005_1.xls was created with the two column headings but no data from the select statement. I know the select statement is correct. I also received the following error.
(1 row(s) affected)
Created OLE ADODB.Connection object
Assigned ConnectionString property
Open Connection to XLS, for file Create or Append
Server: Msg 7314, Level 16, State 1, Line 1
OLE DB provider 'EXCEL_TEST' does not contain table 'Worksheet_01'. The table either does not exist or the current user does not have permissions on that table.
Destroyed OLE ADODB.Connection object
OLE DB error trace [Non-interface error: OLE DB provider does not contain the table: ProviderName='EXCEL_TEST', TableName='Worksheet_01'].
Any ideas as to what I am doing wrong?
Howard
-
here is an example with pubs DB
DECLARE @sql varchar(8000)
BEGIN
SET @sql =
'select au_lname, au_fname, phone, address, city, state, zip from dbo.authors '
-- select @sql
-- exec (@SQL)
exec HPSP_UT_ExcelReport 'Test_XLS', @sql, 'Test_XLS', 'Last_Name text, First_Name text, Phone text, Address text, City text, State text, Zip text'
,'C:\Temp\'
END
Leah Kats
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply