Export result from dynamic crosstab sp to excel

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

Export result from dynamic crosstab sp to excel

Author

Topic

Nazim
A custom title

1408 Posts

Posted - 2002-06-12 : 08:55:05

Herez my SP. a Straight take off from Rob's Crosstab Article one .

CREATE PROCEDURE Purchase_PO
@CATCODE VARCHAR(30)
AS
declare @sumfunc varchar(10), @pivot varchar(10)
DECLARE @sql varchar(8000), @delim varchar(1)
Declare @select varchar(5000)
SET NOCOUNT ON
SET ANSI_WARNINGS OFF

select @sumfunc='sum(d.qty)'
select @pivot='pono'
select @table='po_header'

select @select='select i.item_code as Item from items i
INNER join PO_Details d
on i.item_code = d.item_code
inner join PO_header h
on d.POhkey = h.POhkey
AND H.POSTED=''N'' and substring(h.pono,charindex(''-'',pono)+1,1)=''S''
where i.Cat_code=''' + @catcode +''' group by i.item_code'

SELECT DISTINCT pono AS pivot INTO ##pivot FROM items i inner join PO_Details d
on i.item_code = d.item_code and I.CAT_CODE=@catcode
inner join PO_header h
on d.POhkey = h.POhkey AND H.POSTED='N'
and substring(pono,charindex('-',pono)+1,1)='s'

SELECT @sql='', @sumfunc=stuff(@sumfunc, len(@sumfunc), 1, ' END)' )

PRINT @SQL
SELECT @delim=CASE Sign( CharIndex('char', data_type)+CharIndex('date', data_type) )
WHEN 0 THEN '' ELSE '''' END

FROM tempdb.information_schema.columns
WHERE table_name='##pivot' AND column_name='pivot'

SELECT @sql=@sql + '''' + convert(varchar(100), pivot) + ''' = ' +
stuff(@sumfunc,charindex( '(', @sumfunc )+1, 0, ' CASE ' + @pivot + ' WHEN '
+ @delim + convert(varchar(100), pivot) + @delim + ' THEN ' ) + ', ' FROM ##pivot

DROP TABLE ##pivot

SELECT @sql=left(@sql, len(@sql)-1)

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[POORDERDET]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table poorderdet

select @sql=@sql+' into #poheadtest'

SELECT @select=stuff(@select, charindex(' FROM ', @select)+1, 0, ', ' + @sql + ' ')
--print @select
EXEC (@select)

--SELECT * FROM POHEADTEST

select i.gp_code,i.item_code,i.item_Desc,i.ref_code,i.balance_qty-i.toship_qty-i.com_qty as ActQty,p.*
into poorderdet
from
items i
left join
#poheadtest p
on i.item_code= p.item
where i.cat_Code= @catcode
order by 1,2

drop table #poheadtest

SET ANSI_WARNINGS ON
GO

I Store the result in a table poorderdet. Now, what i want to do is transfer the poorderdet results to a excel file (would be better if we can create a new excel file with the name suppled in the @catcode parameter). i tried storing the DTS and executing it, but it generates a error might be coz the table schema changes everytime i run this. havent played with DTS much. so, cant figure out the best way out of this. Any help??

-------------------------
What lies behind you and what lies ahead of you are small matters compared to what lies within you.-Ralph Waldo Emerson

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-06-12 : 09:09:25

I used to do things like this in Excel, and it was basically a data pull. The Excel sheet would query the SQL database and retrieve the data; SQL never pushed it out to Excel. If you look at the Get External Data function in Excel you'll get an idea how to do it, it's really easy. You can even set it to refresh every time the workbook is opened.

I'm a big believer in pulling data instead of pushing it, because ultimately it saves a lot of time and effort. This is especially true in dynamic situations where data needs to be updated more than once a day. I've had a number of reports that would update every day and no one would read them for a week. When I went to a pull setup, some people complained, but they got the hang of it (probably because I completely ignored their complaints)

and many of them really liked it because it was more interactive.

If you really can't do the pull scenario, you can always bcp out the data into a CSV file, and either rename it to an .XLS extension or just leave it; Excel defaults as the opener for CSV files. That will also let you change the filename dynamically.

colinm
Yak Posting Veteran

62 Posts

Posted - 2002-06-12 : 11:27:05

I'm trying to do the same thing.
DTS doesn't seem very good for dynamic schema changes.

So I'm using BCP, which usually works fine outputting to a CSV.
However, I can't work out how to use quotes round the data!
So any data with a comma already in it goes haywire.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-06-12 : 11:32:28

Use a tab as a delimiter instead of a comma. If you ever have embedded commas in your data you shouldn't use it as a delimiter. Excel handles tab-delimited just as well as CSV.

colinm
Yak Posting Veteran

62 Posts

Posted - 2002-06-12 : 11:44:21

Good call, Robvolk.
Didn't realise that you could just rename to xls and use tab-delimiting. Nice and easy. I like it!

Nazim
A custom title

1408 Posts

Posted - 2002-06-12 : 11:48:49

This wouldnt work Rob. i have to pull the data explicity everytime i run the sp . have to go thru each step , i cant save the query coz the columns will be changed making the query obsolete.

quote:

I used to do things like this in Excel, and it was basically a data pull. The Excel sheet would query the SQL database and retrieve the data; SQL never pushed it out to Excel. If you look at the Get External Data function in Excel you'll get an idea how to do it, it's really easy. You can even set it to refresh every time the workbook is opened

bcp option looked great. i tried it, but the problem is am not getting the column names in the file .

Is there any other way out

-------------------------
What lies behind you and what lies ahead of you are small matters compared to what lies within you.-Ralph Waldo Emerson

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-06-12 : 12:21:26

Take a look at using some VB for it. There is a really handy Excel object method called CopyFromRecordset. You can create a DAO or ADO recordset, create an Excel Range object (could refer to just one cell really) and use CopyFromRecordset to dump the results into the Excel sheet. It is not bound to any number of columns, so the data and structure can be totally dynamic.

Unfortunately, <big screaming rant>Microsft has so completely FUBARed the help files with Office 2000 that you won't find any documentation on CopyFromRecordset.</big screaming rant> Therefore you might have to look on MSDN or grab the help from Excel 97, there are examples on how to use CopyFromRecordset.

EDIT: I stand corrected. If you open Excel and open the VB editor, use the Object Explorer and browse the Excel object model. You'll find the Range object, and the CopyFromRecordset method. If you click on the yellow ? (help) you'll get a code example for it.

Still, it used to be in Excel 97 you could type in CopyFromRecordset and be taken straight to it. I cannot understand why MS chose to hide everything that used to be in plain site in the help file.

Edited by - robvolk on 06/12/2002 12:30:36

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources