Export table to file with column headers (column names) using the bcp utility and SQL Server 2008

This method automatically outputs column names with your row data using BCP.

The script writes one file for the column headers (read from INFORMATION_SCHEMA.COLUMNS table) then appends another file with the table data.

The final output is combined into TableData.csv which has the headers and row data. Just replace the environment variables at the top to specify the Server, Database and Table name.

set BCP_EXPORT_SERVER=put_my_server_name_hereset BCP_EXPORT_DB=put_my_db_name_hereset BCP_EXPORT_TABLE=put_my_table_name_hereBCP "DECLARE @colnames VARCHAR(max);SELECT @colnames = COALESCE(@colnames + ',', '') + column_name from %BCP_EXPORT_DB%.INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='%BCP_EXPORT_TABLE%'; select @colnames;" queryout HeadersOnly.csv -c -T -S%BCP_EXPORT_SERVER%BCP %BCP_EXPORT_DB%.dbo.%BCP_EXPORT_TABLE% out TableDataWithoutHeaders.csv -c -t, -T -S%BCP_EXPORT_SERVER%set BCP_EXPORT_SERVER=set BCP_EXPORT_DB=set BCP_EXPORT_TABLE=copy /b HeadersOnly.csv+TableDataWithoutHeaders.csv TableData.csvdel HeadersOnly.csvdel TableDataWithoutHeaders.csv

Note that if you need to supply credentials, replace the -T option with -U my_username -P my_password

This method has the advantage of always having the column names in sync with the table by using INFORMATION_SCHEMA.COLUMNS. The downside is that it creates temporary files. Microsoft should really fix the bcp utility to support this.

This solution uses the SQL row concatenation trick from here combined with bcp ideas from here

The easiest is to use the queryout option and use union all to link a column list with the actual table content

    bcp "select 'col1', 'col2',... union all select * from myschema.dbo.myTableout" queryout myTable.csv /SmyServer01 /c /t, -T

An example:

create table Question1355876(id int, name varchar(10), someinfo numeric)insert into Question1355876values (1, 'a', 123.12)     , (2, 'b', 456.78)     , (3, 'c', 901.12)     , (4, 'd', 353.76)

This query will return the information with the headers as first row (note the casts of the numeric values):

select 'col1', 'col2', 'col3'union allselect cast(id as varchar(10)), name, cast(someinfo as varchar(28))from Question1355876

The bcp command will be:

bcp "select 'col1', 'col2', 'col3' union all select cast(id as varchar(10)), name, cast(someinfo as varchar(28)) from Question1355876" queryout myTable.csv /SmyServer01 /c /t, -T

For:

• Windows, 64 bit
• SQL Server (tested with SQL Server 2017 and it should work for all versions):

Option 1: Command Prompt

sqlcmd -s, -W -Q "set nocount on; select * from [DATABASE].[dbo].[TABLENAME]" | findstr /v /c:"-" /b > "c:\dirname\file.csv"

Where:

• [DATABASE].[dbo].[TABLENAME] is table to write.
• c:\dirname\file.csv is file to write to (surrounded in quotes to handle a path with spaces).
• Output .csv file includes headers.

Note: I tend to avoid bcp: it is legacy, it predates sqlcmd by a decade, and it never seems to work without causing a whole raft of headaches.

Option 2: Within SQL Script

-- Export table [DATABASE].[dbo].[TABLENAME] to .csv file c:\dirname\file.csvexec master..xp_cmdshell 'sqlcmd -s, -W -Q "set nocount on; select * from [DATABASE].[dbo].[TABLENAME]" | findstr /v /c:"-" /b > "c:\dirname\file.csv"'

Troubleshoooting: must enable xp_cmdshell within MSSQL.

Sample Output

File: file.csv:

ID,Name,Height1,Bob,1922,Jane,1843,Harry,186

Speed

As fast as theoretically possible: same speed as bcp, and many times faster than manually exporting from SSMS.

Parameter Explanation (optional - can ignore)

In sqlcmd:

• -s, puts a comma between each column.

• -W eliminates padding either side of the values.
• set nocount on eliminates a garbage line at the end of the query.

For findstr:

• All this does is remove the second line underline underneath the header, e.g. --- ----- ---- ---- ----- --.
• /v /c:"-" matches any line that starts with "-".
• /b returns all other lines.

Importing into other programs

In Excel:

• Can directly open the file in Excel.

In Python:

import pandas as pddf_raw = pd.read_csv("c:\dirname\file.csv")