BCP command to export data to excel with column headers

,

Be careful for following lines

replace <element> with your local environment elements

remove "<" ," >" signs

e.g. use master

1 use <database>

8 @dbName varchar(100) = '<database name>',

9 @sql varchar(8000) = 'select <col1,col2,col3....col4> from <table>',

10 @fullFileName varchar(100) = '<output file path>'

57 SET @bcpCommand = @bcpCommand + ' C:\Temp\test.csv -c -w -T -U sa -P sa","-CRAW'
58 EXEC <database>..xp_cmdshell @bcpCommand

 

 

use <database> 
go 
if object_id('spExportData_n') is not null 
drop proc spExportData_n 
go 
create proc spExportData_n 
( 
@dbName varchar(100) = '<database name>', 
@sql varchar(8000) = 'select <col1,col2,col3....col4> from <table>', 

) 
as 
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 object_id('##TempExportData') is not null 
drop table ##TempExportData 
if object_id('##TempExportData2') is not null 
drop table ##TempExportData2 
-- insert data into a global temp table 
declare @columnNames varchar(8000), @columnConvert varchar(8000), @tempSQL varchar(8000) 

select @tempSQL = left(@sql, charindex('from', @sql)-1) + ' into ##TempExportData ' + 
substring(@sql, charindex('from', @sql)-1, len(@sql)) 

exec(@dbName + @tempSQL) 
if @@error > 0 
begin 
select 0 as ReturnValue -- failure 
return 
end 
-- build 2 lists 
-- 1. column names 
-- 2. columns converted to nvarchar 
SELECT @columnNames = COALESCE( @columnNames + ',', '') + column_name, 
@columnConvert = COALESCE( @columnConvert + ',', '') + 'convert(nvarchar(4000),' 
+ column_name + case when data_type in ('datetime', 'smalldatetime') then ',121' 
when data_type in ('numeric', 'decimal') then ',128' 
when data_type in ('float', 'real', 'money', 'smallmoney') then ',2' 
when data_type in ('datetime', 'smalldatetime') then ',120' 
else '' 
end + ') as ' + column_name 
FROM tempdb.INFORMATION_SCHEMA.Columns 
WHERE table_name = '##TempExportData' 

-- execute select query to insert data and column names into new temp table 
SELECT @sql = 'select ' + @columnNames + ' into ##TempExportData2 from (select ' + @columnConvert + ', ''2'' as [temp##SortID] from ##TempExportData union all select ''' + replace(@columnNames, ',', ''', ''') + ''', ''1'') t order by [temp##SortID]' 

exec (@sql) 

-- build full BCP query 
DECLARE @bcpCommand VARCHAR(8000) 
SET @bcpCommand = 'bcp " SELECT * from ##TempExportData2" queryout' 
SET @bcpCommand = @bcpCommand + ' C:\Temp\test.csv -c -w -T -U sa -P sa","-CRAW' 
EXEC <database>..xp_cmdshell @bcpCommand 
if @@error > 0 
begin 
select 0 as ReturnValue -- failure 
return 
end 
drop table ##TempExportData 
drop table ##TempExportData2 
set @columnNames =' ' 
set @columnConvert =' ' 
set @tempSQL =' ' 
select 1 as ReturnValue

Rate

4.5 (6)

Share

Share

Rate

4.5 (6)