Export SQL data to CSV/TXT/XLS

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).

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'