Question: I have seen a number of hacks to try to get the bcp utility to export column names along with the data. If all I am doing is dumping a table to a text file what is the most straightforward method to have bcp add the column headers?

Here's the bcp command I am currently using:

```
bcp myschema.dbo.myTableout myTable.csv /SmyServer01 /c /t, -T

```

Answer: 
For:

* Windows, 64 bit
* SQL Server (tested with [SQL Server 2017](https://en.wikipedia.org/wiki/History_of_Microsoft_SQL_Server#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.csv
exec 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](https://stackoverflow.com/questions/5131491/enable-xp-cmdshell-sql-server) within MSSQL.

Sample Output
-------------

File: `file.csv`:

```
ID,Name,Height
1,Bob,192
2,Jane,184
3,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 pd
df_raw = pd.read_csv("c:\dirname\file.csv")

```
Some of the solutions here are overly complex. Here's one with 4 lines of code, no batch files, no external apps and all self-contained in the SQL server.

In this example, my table is named "MyTable" and it has two columns named Column1 and Column2. Column2 is an integer, so we need to CAST it to varchar for the export:

```
DECLARE @FileName varchar(100)
DECLARE @BCPCommand varchar(8000)
DECLARE @ColumnHeader varchar(8000)

    SET @FileName = 'C:\Temp\OutputFile.csv'
 SELECT @ColumnHeader = COALESCE(@ColumnHeader+',' ,'')+ ''''+column_name +'''' FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='MyTable'
    SET @BCPCommand = 'bcp "SELECT '+ @ColumnHeader +' UNION ALL SELECT Column1, CAST(Column2 AS varchar(100)) AS Column2 FROM MyTable" queryout "' + @FileName + '" -c -t , -r \n  -S . -T'
   EXEC master..xp_cmdshell @BCPCommand

```

You could add this to a stored procedure to fully automate your .CSV file (with header row) creation.