sqlSrvrToXcl with FieldNames-nopreexisting xcl

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 2005 Forums

Transact-SQL (2005)

sqlSrvrToXcl with FieldNames-nopreexisting xcl

Author

Topic

heze
Posting Yak Master

192 Posts

Posted - 2007-06-26 : 08:11:41

hi, i found that exporting (using bcp) to excel results in very messy code, I read previous postings
ie:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926
but mostly one has to pre-build the excel repository before dumping data from sql server to excel, but what if there are many columns and they change in name and number constantly, does somebodty have a suggestion on how to dump a table from sql server to excel, including the column names?
i searched the following proc: http://weblogs.sqlteam.com/mladenp/archive/2006/07/25/10771.aspx
but broke when the size of the field string exceeded 4000, I tried tweeking it but lost all day
and created a procedure double the size trying to use the same logic, so I adapted to another (quite messy as well) procedure that does not break with
a long list of fields, if somebody could give suggestions or pointers to other useful postings would be great:
it mainly consists in creating two tables, one contianing the data and another containing the field names, then union them, the field name
table is created starting with 1 field and then fields are added with the alter statement and then filled with the update one by one so dynamic
sql string size is not an issue: thank you:
----------------------------------------------
use [myDb]
go
if object_id('exportDataSQLToExcel') is not null
drop proc exportDataSQLToExcel
go
create proc exportDataSQLToExcel
(
@dbName varchar(255) = 'master',
@sql varchar(8000) = '',
@fullFileName varchar(2000) = ''
)
as

--exec(@sql)
if @sql = '' or @fullFileName = ''
begin
select 0 as ReturnValue -- failure
return
end
-- if DB isn't passed in set it to master
select @dbName = 'use ' + @dbName + ';'
if not object_id('tempdb.dbo.##TempExportData_11A6AAE8-E268-45F6-AB1F-4A2A8114755E') is null
drop table [##TempExportData_11A6AAE8-E268-45F6-AB1F-4A2A8114755E]
if not object_id('tempdb.dbo.##TempExportData2_11A6AAE8-E268-45F6-AB1F-4A2A8114755E') is null
drop table [##TempExportData2_11A6AAE8-E268-45F6-AB1F-4A2A8114755E]
-- insert data into a global temp table
declare @columnNames varchar(8000), @columnConvert varchar(8000), @tempSQL varchar(8000)
set @columnNames=''
select @tempSQL = left(@sql, charindex('from', @sql)-1) + ',myId=identity(int,0,1) into [##TempExportData_11A6AAE8-E268-45F6-AB1F-4A2A8114755E] ' +
substring(@sql, charindex('from', @sql)-1, len(@sql))
print @tempSQL

exec(@dbName + @tempSQL)

if @@error > 0
begin
select 0 as ReturnValue -- failure
return
end
print @tempSQL
-- build 2 lists
-- 1. column names
-- 2. columns converted to nvarchar
if not object_id('tempdb.dbo.#ccases_columnTitles') is null drop table #ccases_columnTitles
create table [#ccases_columnTitles] (myIdFieldNames int)
exec('insert into #ccases_columnTitles (myIdFieldNames) values (0)')

--select * from #ccases_columnTitles

if not object_id('tempdb.dbo.#fiedCatalog') is null drop table #fiedCatalog
select
myId=identity(int,0,1),
column_name,
sqlCreatingTable=('alter table [#ccases_columnTitles] add '+ column_name+' varchar(30) '),
sqlAddingValuesToTable=('update #ccases_columnTitles set '+column_name+'= '''+column_name+'''')
into #fiedCatalog
FROM tempdb.INFORMATION_SCHEMA.Columns
WHERE table_name = '##TempExportData_11A6AAE8-E268-45F6-AB1F-4A2A8114755E'
and not column_name='myId'
order by ordinal_position

--select * from #fiedCatalog

declare @numFieldsInInfoSchema as int,@sqlAlterTablesPlaceHolder as varchar(4000)
set @numFieldsInInfoSchema=(select count(*) FROM #fiedCatalog)
declare @columnPh1 as varchar(8000)
declare @columnPh2 as varchar(8000)

set @columnPh1=''
set @columnPh2=''
set @sqlAlterTablesPlaceHolder=''
--select * from #fiedCatalog
------------
--after ##TempExportData_11A6AAE8-E268-45F6-AB1F-4A2A8114755E table is created
--we should change all the column types to varchar
declare @colIterator as int,@columnPlaceHolder as varchar(5000)
set @columnPlaceHolder=''
set @colIterator=0
while(@colIterator<@numFieldsInInfoSchema)
begin
----------------------
--data section
set @columnPlaceHolder=(select column_name from #fiedCatalog where myId=@colIterator)
if not(@columnPlaceHolder='myId')
begin
exec('
alter table [##TempExportData_11A6AAE8-E268-45F6-AB1F-4A2A8114755E]
alter column '+@columnPlaceHolder+' varchar(30); ')
end
-----------------
--column names section
set @sqlAlterTablesPlaceHolder=(select sqlCreatingTable from #fiedCatalog where myId=@colIterator)
exec (@sqlAlterTablesPlaceHolder) print @sqlAlterTablesPlaceHolder
set @sqlAlterTablesPlaceHolder=(select sqlAddingValuesToTable from #fiedCatalog where myId=@colIterator)
exec (@sqlAlterTablesPlaceHolder) print @sqlAlterTablesPlaceHolder
------------------

set @colIterator=@colIterator+1
end

--select * from #ccases_columnTitles

alter table #ccases_columnTitles drop column myIdFieldNames;

exec('insert into [##TempExportData_11A6AAE8-E268-45F6-AB1F-4A2A8114755E]
select * from #ccases_columnTitles')

select * from [##TempExportData_11A6AAE8-E268-45F6-AB1F-4A2A8114755E] order by myId desc

---bcp to excel
select @sql = 'bcp "' + @dbName + ' select * from [##TempExportData_11A6AAE8-E268-45F6-AB1F-4A2A8114755E]" queryout "' + @fullFileName + '" -c -CRAW'
-- execute BCP
Exec master..xp_cmdshell @sql

go

--mode of usage
declare @sql varchar(6800), @dbName varchar(100), @fullFileName varchar(100)
select @dbName = '[myDb]', @sql = 'select * from ...., @fullFileName = 'myFile.xls'
exec [myDb].dbo.exportDataSQLToExcel @dbName, @sql, @fullFileName

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2007-06-27 : 04:40:01

Why not just create a SSIS package for this from within SQL, it's very easy to work with and gives you options to include the column names.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources