Export to Excel ??

[sparkbyte]

I am trying to get a query to export to excel but cannot seem to figure it out.

INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
'Excel 8.0;Database = T:\AccessDBs\Template.xls;', 
'SELECT BoxNumber, FileNumber, TrackingDate FROM [Initiated]') 
SELECT TOP (50) BoxNumber, FileNumber, TrackingDate FROM tblTrackingParse
GO

Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" reported an error.
The provider did not give any information about the error.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

Does MS Office (excel) need to be installed on the SQL Server for this to work??




Thanks

John Fuhrman

 

[gmmastros]

Working with Excel always seems to be a pain. Do you need it to be a .xls file? Specifically, if you create a comma separated values file (.csv), it can be opened directly by Excel, and is much easier to work with. Is this possible?


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[sparkbyte]

Well the file needs to have the date exported in 28-Dec-2009 for some reason. Not sure why, I was just told that this is the way they want it formatted.

The process was being done manually from an access DB. Cut-n-Paste to excel.

I am moving the data to MS SQL Server 2005 and have rewritten the UI for the mailroom. This hopefully will be one of the last requirements to complete before I can cut over to the new UI and database.

I found some code that seems to be able to do what I want.

I am getting it close to doing what I need.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926

Here is the code for the procedure.

alter procedure proc_generate_excel_with_columns
(
	@db_name	varchar(100),
	@table_name	varchar(100),	
	@file_name	varchar(100),
	@Enter_shipping_type_please int
)
as

--Generate column names as a recordset
declare @columns varchar(8000), @sql varchar(8000), @data_file varchar(100)
select 
	@columns=coalesce(@columns+',','')+column_name+' as '+column_name 
from 
	information_schema.columns
where 
	table_name=@table_name
select @columns=''''''+replace(replace(@columns,' as ',''''' as '),',',',''''')

--Create a dummy file to have actual data
select @data_file=substring(@file_name,1,len(@file_name)-charindex('\',reverse(@file_name)))+'\data_file.xls'

--Generate column names in the passed EXCEL file
set @sql='exec master..xp_cmdshell ''bcp " select BoxNumber, FileNumber,TrackingDate from (select '+@columns+') as t" queryout "'+@file_name+'" -c -T'''
exec(@sql)

--Generate data in the dummy file
set @sql='exec master..xp_cmdshell ''bcp "select BoxNumber, FileNumber,TrackingDate from '+@db_name+'..'+@table_name+'" queryout "'+@data_file+'" -c -T'''
print @sql
exec(@sql)

--Copy dummy file to passed EXCEL file
set @sql= 'exec master..xp_cmdshell ''type '+@data_file+' >> "'+@file_name+'"'''
exec(@sql)

--Delete dummy file 
set @sql= 'exec master..xp_cmdshell ''del '+@data_file+''''
exec(@sql)

-------------------------------------------------------------------------------------------
-- EXEC proc_generate_excel_with_columns 'your dbname', 'your table name','your file path'
-------------------------------------------------------------------------------------------
EXEC proc_generate_excel_with_columns 'MailroomTracking', 'tblTrackingParse','d:\testing.xls'
-------------------------------------------------------------------------------------------

The part that I need to be able to add is the WHERE portion of the script.

WHERE	(@Enter_shipping_type_please Is Null) OR (TrackingNumberShipping LIKE '%' + @Enter_shipping_type_please + '%')
	AND (NOT (UPPER(FileNumber) = '.box.end.')))

I can't seem to get it to take the addition of the where clause.

I have already made all the changes to the SQL surface area configurations and the export is working and creating the file.

Then I will need to figure out how to format the columns on the sheet. The date in particular.

Also, here is another bit of code that is close.

EXEC sp_makewebtask 
	@outputfile = 'd:\testing.xls', 
	@query = 'SELECT TOP (50) BoxNumber, FileNumber, TrackingDate FROM MailroomTracking.dbo.tblTrackingParse
				WHERE TrackingNumberShipping LIKE ''%'' + ''3'' + ''%''
				AND (NOT (UPPER(FileNumber) = ''.box.end.''))', 
	@colheaders =1, 
	@FixedFont=0,@lastupdated=0,@resultstitle='Initiated'

problem here is that you can't seem to be able to set the column format options. and it adds 3 rows of data before the column headings.



Thanks

John Fuhrman

 

[gmmastros]

I haven't had a chance to look at all of your post (it's time for me to head home). However, I would encourage you to NOT use sp_makewebtask because Microsoft is deprecating it so it will not be available in future versions.

I'll take a look at the other parts later, when I get a chance.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SkipVought]

Well the file needs to have the date exported in 28-Dec-2009

Not so! In fact, it would be a huge mistake to output a string like that.

All Dates are pure numbers. The formatting occurs in the application, in whatever format your choose, but the formatting does not change the underlying numeric value.

My suggestion would be to output the date as a STRING to your CSV file, in the UNAMBIGUOUS form of yyyy/mm/dd You cannot go wrong with that approch.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[TysonLPrice]

Another option is you can connect and run queries right from Excel with the formatting you desire. In 2007 it’s from the Data tab and the “From other Sources” option. In earlier versions it is under “External Data Sources” or something like that. Make the connection and let the wizard take you through the steps. Just do a simple query against the tables you want. At the end of the set up use the option to edit the SQL. Then put in whatever select you want. Then do all the formatting you want and save it. Each time you pull it up you will be prompted to refresh the data or not. It is great for your own reports but nothing I would roll out as a production type project.

 

[sparkbyte]

Skip, I know you are correct in that the date that is exported is the full date/time and then Excel is reformating it for display.

This is why I have been trying the find a way to format the columns after the data has been put into the sheet.

Thanks

John Fuhrman

 

[SkipVought]

As suggested above, it's probably best to query your db, from Excel, using MS Query. There are controls that will preserve and propogate cell formatting and formulas in adjacent columns if necessary, as the QueryTable is refreshed.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}