Binaryworld - Export table/query to CSV, XLS or TXT format with column headers and c ... [ SQL Server -> BCP ]

If you never used BCP then I think you have not taken it seriously. BCP gives you non-logged functionality which is tremendously faster than logged operations. Many times we have requirements to export data from SQL server to disk file which can be CSV, XLS or text format. Generally people write program in their favorite language (VB, VB.net, C#...) but believe me there are some better and faster ways to do the same thing. In this article you will see how you can use BCP to export SQL Server data in required format. I had to write this SP because of BCP doesn't output column headers so I decided to write my own SP which fulfills this missing functionality in BCP.

Lets look at the SP

Click here to copy the following block

/*
Copyright © 2005 Nayan Patel. All rights reserved.

Author   : NPatel
Created On : 8/15/05
Site    : www.binaryworld.net

Description: BCP is a powerful tool for BULK data loading but it has some limitation when you want customize output. The biggest challenge I faced was it doesn't include column names in the output so I wrote this SP.

examples:
=========
--//[1] Default ouput
exec usp_ExportToFile

--//[2] create a CSV file from table name. Dump to c:\output.csv.
exec usp_ExportToFile 'c:\output.csv', 'authors', 0, 'pubs', 1, ','

--//[3] create XLS file from simple query. Dump output to c:\output.xls. Dont include column names
exec usp_ExportToFile 'c:\output.xls'
, @tableOrQuery= 'select au_lname as LastName, au_fname as FirstName, Phone from authors where au_fname like ''a%'' '
, @isquery=1
, @dbname='pubs'
, @includeheader=0
, @fld_sap='\t'

--//[4] create XLS file from simple query. Dump output to c:\output.xls. Include column names
exec usp_ExportToFile 'c:\output.xls'
, @tableOrQuery= 'select au_lname as LastName, au_fname as FirstName, Phone from authors where au_fname like ''a%'' '
, @isquery=1
, @dbname='pubs'
, @includeheader=1
, @fld_sap='\t'

Version History
===============

*/

Alter proc usp_ExportToFile
@exportpath varchar(255)='c:\exported.xls'
,@tableOrQuery varchar(8000)='select * from authors'
,@isquery bit=1 --//0=Table 1=Query
,@dbname varchar(128)='pubs'
,@includeheader bit=1
,@fld_sap char(9)='\t' -- [\t =TAB] [\n = NEWLINE] [\r = Carriage return ] [{?} = any other character(s)]
,@row_sap char(9)='\r' -- [\t =TAB] [\n = NEWLINE] [\r = Carriage return ] [{?} = any other character(s)]

--//check this URL for more info about terminators
--//http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_impt_bcp_0fqq.asp
as

declare @cnt int

declare @dummytbl varchar(128)
declare @name varchar(128)
declare @fldNameList varchar(8000)
declare @fldValueList varchar(8000)
declare @cmd varchar(8000)

set @dummytbl='_tmp_bcp_dump_1840_111222333' --//some unique name

if object_id(@dummytbl) is not null
begin
set @cmd='drop table ' + @dummytbl
execute(@cmd)
end

if @isquery=1
set @cmd='select * into dbo.' + @dummytbl + ' from (' + @tableOrQuery + ') as tmp'
else
set @cmd='select * into dbo.' + @dummytbl + ' from ' + @tableOrQuery

--print @cmd

execute(@cmd)

set @fldNameList=''
set @fldValueList=''

declare c cursor
for
select COLUMN_NAME
from INFORMATION_SCHEMA.COLUMNS
where TABLE_SCHEMA = 'dbo'
and TABLE_NAME = @dummytbl
order by ORDINAL_POSITION

OPEN c

set @cnt=0

FETCH NEXT FROM c INTO @name
WHILE (@@fetch_status <> -1)
BEGIN
set @cnt=@cnt+1
--Print @name
if @cnt=1
begin
set @fldNameList= '''''' + @name + ''''' '
set @fldValueList='cast(' + @name + ' as varchar(8000))'
end
else
begin
set @fldNameList= @fldNameList + ', ''''' + @name + ''''' '
set @fldValueList= @fldValueList + ', cast(' + @name + ' as varchar(8000)) '
end

FETCH NEXT FROM c INTO @name
END

--print @fldNameList

CLOSE c
DEALLOCATE c

if @includeheader=1
set @cmd='EXEC master..xp_cmdshell ''bcp "select top 1 ' + @fldNameList + ' from ' + @dbname + '.dbo.' + @dummytbl + ' UNION ALL Select ' + @fldValueList + ' from ' + @dbname + '.dbo.' + @dummytbl + ' " queryout ' + @exportpath + ' -T -t'+ @fld_sap +' -c -r' + @row_sap + ' -Usa'',no_output'
else
set @cmd='EXEC master..xp_cmdshell ''bcp "Select ' + @fldValueList + ' from ' + @dbname + '.dbo.' + @dummytbl + ' " queryout ' + @exportpath + ' -T -t'+ @fld_sap +' -c -r' + @row_sap + ' -Usa'',no_output'

print @cmd
execute(@cmd)

-- EXEC master..xp_cmdshell 'bcp "select top 1 ''au_id'',''au_lname'',''au_fname'',''phone'',''address'',''city'',''state'',''zip'',''contract'' from pubs..authors UNION ALL select cast(au_id as varchar(8000)),cast(au_lname as varchar(8000)),cast(au_fname as varchar(8000)),cast(phone as varchar(8000)),cast(address as varchar(8000)),cast(city as varchar(8000)),cast(state as varchar(8000)),cast(zip as varchar(8000)),cast(contract as varchar(8000)) from pubs..authors " queryout c:\authors.xls -T -t\t -c -Usa'

set @cmd='if object_id('''+ @dummytbl +''') is not null ' + char(13) + char(10) + ' Drop table ' + @dummytbl
execute(@cmd)
go