The BCP command is used to export sql server table/view to files(Text, Csv and Excel). But the BCP command doesn't product the header (Column Names).
I have provided three stored procedures to generate the Column Headings along with the data.
1) To export data to new CSV file with heading(column names), create the following procedure:
CREATE PROCEDURE dbo.proc_generate_csv
(
@db_name varchar(100),
@table_name varchar(100),
@file_name varchar(100)
) AS
Declare @Headers varchar(1000),@sql varchar(8000), @data_file varchar(100),
@x varchar(300)
--Generate column names as a recordset
Select @Headers = IsNull(@Headers + ',', '') + Column_Name
From INFORMATION_SCHEMA.COLUMNS
Where Table_Name = @table_name ORDER BY ORDINAL_POSITION ASC
print @Headers
set @sql = 'bcp "select ''' + @Headers + '''" queryout "'+@file_name+'" -c -C RAW -t "," -r \n'
print @sql
exec master..xp_cmdshell @sql
set @sql = 'exec master..xp_cmdshell ' + @sql
print @sql
--Create a dummy file to have actual data
select @data_file=substring(@file_name,1,len(@file_name)-charindex('\',reverse(@file_name)))+'\data_file.csv'
print @data_file
set @sql = 'bcp "select * from '+@db_name+'..'+@table_name+'" queryout "'+@data_file+'" -c -C RAW -t "," -r \n'
print @sql
exec master..xp_cmdshell @sql
--Copy dummy file to passed CSV file
set @sql= 'exec master..xp_cmdshell ''type '+@data_file+' >> "'+@file_name+'"'''
print @sql
exec(@sql)
--Delete dummy file
set @sql= 'exec master..xp_cmdshell ''del '+@data_file+''''
print @sql
exec(@sql)
GO
After creating the procedure, execute it by supplying database name, table name and file path
EXEC proc_generate_csv 'your dbname', 'your table name/Your View Name','your file path'
E.g
EXEC proc_generate_csv 'Northwind', 'Products','C:\Products.csv'
2) To export data to new Excel file with heading(column names), create the following procedure:
CREATE procedure proc_generate_excel
(
@db_name varchar(100),
@table_name varchar(100),
@file_name varchar(100)
)
as
--Generate column names as a recordset
declare @columns varchar(8000), @sql varchar(8000), @data_file varchar(100)
select
@columns=coalesce(@columns+',','')+column_name
from
information_schema.columns
where
table_name=@table_name
select @columns = '''''' + replace(@columns,',',''''',''''') + ''''''
--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 '+@columns+' as t" queryout "'+@file_name+'" -c'''
exec(@sql)
--Generate data in the dummy file
set @sql='exec master..xp_cmdshell ''bcp "select * from '+@db_name+'..'+@table_name+'" queryout "'+@data_file+'" -c'''
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)
GO
After creating the procedure, execute it by supplying database name, table name and file path
EXEC proc_generate_excel 'your dbname', 'your table name/Your View Name','your file path'
E.g
EXEC proc_generate_excel 'Northwind', 'Products','C:\Products.xls'
3) To export data to new Text file with heading(column names), create the following procedure:
CREATE PROCEDURE dbo.proc_generate_txt
(
@db_name varchar(100),
@table_name varchar(100),
@file_name varchar(100)
) AS
Declare @Headers varchar(1000),@sql varchar(8000), @data_file varchar(100),
@x varchar(300)
--Generate column names as a recordset
Select @Headers = IsNull(@Headers + CHAR(9) ,'') + Column_Name
From INFORMATION_SCHEMA.COLUMNS
Where Table_Name = @table_name ORDER BY ORDINAL_POSITION ASC
print @Headers
set @sql = 'bcp "select ''' + @Headers + '''" queryout "'+@file_name+'" -c -t'
print @sql
exec master..xp_cmdshell @sql
set @sql = 'exec master..xp_cmdshell ' + @sql
print @sql
--Create a dummy file to have actual data
select @data_file=substring(@file_name,1,len(@file_name)-charindex('\',reverse(@file_name)))+'\data_file.csv'
print @data_file
set @sql = 'bcp "select * from '+@db_name+'..'+@table_name+'" queryout "'+@data_file+'" -c -t"\t"'
print @sql
exec master..xp_cmdshell @sql
--Copy dummy file to passed txt file
set @sql= 'exec master..xp_cmdshell ''type '+@data_file+' >> "'+@file_name+'"'''
print @sql
exec(@sql)
--Delete dummy file
set @sql= 'exec master..xp_cmdshell ''del '+@data_file+''''
print @sql
exec(@sql)
GO
After creating the procedure, execute it by supplying database name, table name and file path
EXEC proc_generate_txt 'your dbname', 'your table name/Your View Name','your file path'
E.g
EXEC proc_generate_txt 'Northwind', 'Products','C:\Products.txt'
 Attachments
Export Sql Server table to CSV,Excel and Txt Files (28884-51147-Various BCP Export Procs.zip)
|
No responses found. Be the first to respond and make money from revenue sharing program.
|