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

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

User · Answer

As well as the solution from marc s  you can also use osql or sqlcmd  This includes headers and it can act like bcp using -Q and -o  However  they don t support format files like bcp

User · Answer

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 here set BCP EXPORT DB put my db name here set BCP EXPORT TABLE put my table name here  BCP  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 csv  del HeadersOnly csv del 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

User · Answer

Here is a pretty simple stored procedure that does the trick as well         CREATE PROCEDURE GetBCPTable      table name varchar 200  AS BEGIN     DECLARE  raw sql nvarchar 3000       DECLARE  columnHeader VARCHAR 8000      SELECT  columnHeader   COALESCE  columnHeader                column name       FROM INFORMATION SCHEMA COLUMNS WHERE TABLE NAME    table name      DECLARE  ColumnList VARCHAR 8000      SELECT  ColumnList   COALESCE  ColumnList            CAST   column name    AS VARCHAR   FROM INFORMATION SCHEMA COLUMNS WHERE TABLE NAME    table name      SELECT  raw sql    SELECT     columnHeader    UNION ALL SELECT      ColumnList     FROM      table name     --PRINT  raw SQL     EXECUTE sp executesql   raw sql END GO

User · Answer

I got a version based on what I saw previously   It helped me a lot to create export files as CSV or TXT   I m storing a table into a    Temp Table       IF OBJECT ID  tempdb    TmpExportFile   IS NOT NULL DROP TABLE   TmpExportFile    DECLARE  columnHeader VARCHAR 8000  DECLARE  raw sql nvarchar 3000   SELECT                 INTO   TmpExportFile               ------ FROM THE TABLE RESULTS YOU WANT TO GET               ------ COULD BE A TABLE OR A TEMP TABLE BASED ON INNER JOINS               ------  ETC   FROM TableName -- optional WHERE        DECLARE  table name  VARCHAR 50       TmpExportFile  SELECT                columnHeader   COALESCE  columnHeader                      c name             as          c name      FROM tempdb sys columns c INNER JOIN tempdb sys tables t               ON c object id   t object id WHERE t NAME    table name  print  columnheader                  DECLARE  ColumnList VARCHAR max  SELECT                ColumnList   COALESCE  ColumnList               CAST      c name      AS CHAR     LTRIM STR max length          FROM tempdb sys columns c INNER JOIN tempdb sys tables t               ON c object id   t object id WHERE t name    table name print  ColumnList  --- CSV FORMAT                                        SELECT                raw sql    bcp  SELECT      columnHeader     UNION all SELECT      ColumnList     FROM      table name       queryout   networkdrive networkfolder datafile csv -c -t   S      SQLserverName  T  --PRINT  raw sql EXEC xp cmdshell  raw sql    --- TXT FORMAT        SET  raw sql    bcp  SELECT      columnHeader     UNION all SELECT      ColumnList     FROM      table name       queryout   networkdrive networkfolder MISD datafile txt  c  S     SQLserverName  T         EXEC xp cmdshell  raw sql    DROP TABLE   TmpExportFile

User · Answer

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  quot set nocount on  select   from  DATABASE   dbo   TABLENAME  quot    findstr  v  c  quot - quot   b  gt   quot c  dirname file csv quot   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  quot set nocount on  select   from  DATABASE   dbo   TABLENAME  quot    findstr  v  c  quot - quot   b  gt   quot c  dirname file csv quot    Troubleshoooting  must enable xp cmdshell 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  quot - quot  matches any line that starts with  quot - quot    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  quot c  dirname file csv quot

User · Answer

A good alternative is SqlCmd  since it does include headers  but it has the downside of adding space padding around the data for human readability   You can combine SqlCmd with the GnuWin32 sed  stream editing  utility to cleanup the results   Here s an example that worked for me  though I can t guarantee that it s bulletproof     First  export the data    sqlcmd -S Server -i C  Temp Query sql -o C  Temp Results txt -s         The -s       is a tab character in double quotes   I found that you have to run this command via a batch file  otherwise the Windows command prompt will treat the tab as an automatic completion command and will substitute a filename in place of the tab   If Query sql contains   SELECT name  object id  type desc  create date FROM MSDB sys views WHERE name LIKE  sysmail     then you ll see something like this in Results txt   name                                          object id   type desc           create date             -------------------------------------------   ----------- ------------------- ----------------------- sysmail allitems                               2001442204 VIEW                2012-07-20 17 38 27 820 sysmail sentitems                              2017442261 VIEW                2012-07-20 17 38 27 837 sysmail unsentitems                            2033442318 VIEW                2012-07-20 17 38 27 850 sysmail faileditems                            2049442375 VIEW                2012-07-20 17 38 27 860 sysmail mailattachments                        2097442546 VIEW                2012-07-20 17 38 27 933 sysmail event log                              2129442660 VIEW                2012-07-20 17 38 28 040   6 rows affected     Next  parse the text using sed   sed -r  s    t  t g  C  Temp Results txt   sed -r  s  t    t g    sed -r  s            g    sed 2d   sed  d   sed      d   gt  C  Temp Results New txt   Note that the 2d command means to delete the second line  the  d command means to delete the last line  and      d  deletes any blank lines   The cleaned up file looks like this  though I replaced the tabs with   so they could be visualized here     name object id type desc create date sysmail allitems 2001442204 VIEW 2012-07-20 17 38 27 820 sysmail sentitems 2017442261 VIEW 2012-07-20 17 38 27 837 sysmail unsentitems 2033442318 VIEW 2012-07-20 17 38 27 850 sysmail faileditems 2049442375 VIEW 2012-07-20 17 38 27 860 sysmail mailattachments 2097442546 VIEW 2012-07-20 17 38 27 933 sysmail event log 2129442660 VIEW 2012-07-20 17 38 28 040

User · Answer

I have successfully achieved this with the below code  Put the below code in an SQL Server new query window and try   CREATE TABLE tempDBTableDetails   TableName VARCHAR 500    RowCount  VARCHAR 500   TotalSpaceKB VARCHAR 500                   UsedSpaceKB VARCHAR 500   UnusedSpaceKB VARCHAR 500         -- STEP 1        DECLARE  cmd VARCHAR 4000   INSERT INTO tempDBTableDetails SELECT  TableName    RowCount    TotalSpaceKB    UsedSpaceKB    UnusedSpaceKB  INSERT INTO tempDBTableDetails SELECT     S name       T name as TableName      Convert varchar Cast SUM P rows  as Money  1  as  RowCount       Convert varchar Cast SUM a total pages    8 as Money  1  AS TotalSpaceKB      Convert varchar Cast SUM a used pages    8 as Money  1  AS UsedSpaceKB       SUM a total pages  - SUM a used pages     8 AS UnusedSpaceKB FROM sys tables T INNER JOIN sys partitions P ON P OBJECT ID   T OBJECT ID INNER JOIN sys schemas S ON T schema id   S schema id INNER JOIN sys allocation units A ON p partition id   a container id WHERE T is ms shipped   0 AND P index id IN  1 0  GROUP BY S name  T name ORDER BY SUM P rows  DESC  -- SELECT   FROM  FIINFRA-DB-SIT  dbo tempDBTableDetails ORDER BY LEN  RowCount   DESC  SET  cmd    bcp  SELECT   FROM  FIINFRA-DB-SIT  dbo tempDBTableDetails ORDER BY LEN  RowCount   DESC  queryout  D  Milind export xls  -U sa -P dbowner -c      Exec xp cmdshell  cmd  --DECLARE  HeaderCmd VARCHAR 4000  --SET  HeaderCmd    SELECT   TableName      RowCount      TotalSpaceKB      UsedSpaceKB      UnusedSpaceKB    exec master  xp cmdshell  BCP  SELECT   TableName      RowCount      TotalSpaceKB      UsedSpaceKB      UnusedSpaceKB    queryout  d  milind header xls  -U sa -P dbowner -c  exec master  xp cmdshell  copy  b  d  Milind header xls   d  Milind export xls   d  Milind result xls    exec master  xp cmdshell  del  d  Milind header xls   exec master  xp cmdshell  del  d  Milind export xls    DROP TABLE tempDBTableDetails

User · Answer

With a little PowerShell script    x000D   x000D  sqlcmd -Q  set nocount on select top 0   from  DB   schema   table   -o c  temp header txt x000D  bcp  DB   schema   table  out c  temp query txt -c -T -S BRIZA x000D  Get-Content c  temp   txt   Set-Content c  temp result txt x000D  Remove-Item c  temp header txt x000D  Remove-Item c  temp query txt x000D   x000D   x000D    Warning  The concatenation follows the  txt file name  in alphabetical order

User · Answer

I was having the same issue  I needed to export the column header using SQL server bcp utility This way I exported table  headers  with data into same exported file in one go    DECLARE  table name  VARCHAR 50    mytable  DECLARE  columnHeader VARCHAR 8000  SELECT  columnHeader   COALESCE  columnHeader                column name       FROM Nal2013 INFORMATION SCHEMA COLUMNS WHERE TABLE NAME  table name SELECT  raw sql    bcp  SELECT     columnHeader    UNION ALL SELECT   FROM mytable  queryout c  datafile csv -c -t  -T -S      servername EXEC  xp cmdshell  raw sql   Happy coding

User · Answer

I was trying to figure how to do this recently and while I like the most popular solution at the top  it simply would not work for me as I needed the names to be the alias s that I entered in the script so I used some batch files  with some help from a colleague  to accomplish custom table names    The batch file that initiates the bcp has a line at the bottom of the script that executes another script that merges a template file with the header names and the file that was just exported with bcp using the code below  Hope this helps someone else that was in my situation   echo Add headers from template file to exported sql files     Echo School 0031 copy e  genin templates TEMPLATE Courses csv   e  genin 0031 courses0031 csv e  genin finished courses0031 csv  b

User · Answer

Everyone s versions do things a little different   This is the version that I have developed over the years  This version seems to account for all of the issues I have encountered   Simply populate a data set into a table then pass the table name to this stored procedure   I call this stored procedure like this   EXEC     return value    DB You Create The SP In   dbo   Export CSVFile           DB   N  YourDB             TABLE NAME   N  YourTable             Dir   N  YourOutputDirectory             File   N  YourOutputFileName     There are also two other variables     NullBlanks -- This will take any field that doesn t have a value and null it   This is useful because in the true sense of the CSV specification each data point should have quotes around them   If you have a large data set this will save you a fair amount of space by not having     two double quotes  in those fields  If you don t find this useful then set it to 0   IncludeHeaders -- I have one stored procedure for outputting CSV files  so I do have    that flag in the event I don t want headers    This will create the stored procedure   CREATE PROCEDURE  dbo   Export CSVFile     DB varchar 128   TABLE NAME varchar 128    Dir varchar 255    File varchar 250   NULLBLANKS bit 1  IncludeHeader bit 1  AS  DECLARE  CSVHeader varchar max      --CSV Header    CmdExc varchar max               --EXEC commands    SQL varchar max                  --SQL Statements    COLUMN NAME varchar 128          --Column Names    DATA TYPE varchar 15             --Data Types  DECLARE  T table  COLUMN NAME varchar 128  DATA TYPE varchar 15    --BEGIN Ensure Dir variable has a backslash as the final character IF NOT RIGHT  Dir 1        BEGIN SET  Dir  Dir     END --END  --BEGIN Drop TEMP Table IF Exists SET  SQL  IF  EXISTS  SELECT   FROM    DB   INFORMATION SCHEMA TABLES WHERE TABLE NAME     TEMP    TABLE NAME       BEGIN EXEC   DROP TABLE     DB     dbo   TEMP    TABLE NAME       END  EXEC  SQL  --END  SET  SQL  SELECT COLUMN NAME DATA TYPE FROM    DB   INFORMATION SCHEMA COLUMNS WHERE TABLE NAME       TABLE NAME     ORDER BY ORDINAL POSITION  INSERT INTO  T EXEC   SQL   SET  SQL    WHILE exists SELECT   FROM  T      BEGIN         SELECT top 1   DATA TYPE DATA TYPE  COLUMN NAME COLUMN NAME FROM  T         IF  DATA TYPE LIKE   char   OR  DATA TYPE LIKE   text              BEGIN              IF  NULLBLANKS   1                 BEGIN                     SET  SQL   CASE PATINDEX     0-9 a-z        COLUMN NAME    WHEN   0   THEN NULL ELSE       RTRIM LTRIM    COLUMN NAME           END AS     COLUMN NAME                       END             ELSE                 BEGIN                     SET  SQL         RTRIM LTRIM    COLUMN NAME           AS     COLUMN NAME                       END             END         ELSE             BEGIN SET  SQL   COLUMN NAME     END             SET  CSVHeader       COLUMN NAME                  DELETE top 1   T     END   IF LEN  CSVHeader  gt 1 BEGIN SET  CSVHeader RTRIM LTRIM LEFT  CSVHeader LEN  CSVHeader -1    END  IF LEN  SQL  gt 1 BEGIN SET  SQL   SELECT    LEFT  SQL LEN  SQL -1      INTO     DB     dbo   TEMP    TABLE NAME    FROM     DB     dbo      TABLE NAME     END EXEC  SQL   IF  IncludeHeader 0      BEGIN         --BEGIN Create Data file         SET   CmdExc   BCP     DB   dbo TEMP    TABLE NAME    out     Dir  Data    TABLE NAME   csv   c  t  -T           EXEC master  xp cmdshell  CmdExc         --END         SET   CmdExc   del    Dir  File EXEC master  xp cmdshell  CmdExc         SET   CmdExc   ren    Dir  Data    TABLE NAME   csv    File EXEC master  xp cmdshell  CmdExc     END  else     BEGIN          --BEGIN Create Header and main file         SET   CmdExc   echo    CSVHeader   gt     Dir  File EXEC master  xp cmdshell  CmdExc         --END          --BEGIN Create Data file         SET   CmdExc   BCP     DB   dbo TEMP    TABLE NAME    out     Dir  Data    TABLE NAME   csv   c  t  -T           EXEC master  xp cmdshell  CmdExc         --END          --BEGIN Merge Data File With Header File         SET  CmdExc    TYPE    Dir  Data    TABLE NAME   csv  gt  gt     Dir  File EXEC master  xp cmdshell  CmdExc         --END          --BEGIN Delete Data File         SET  CmdExc    DEL  q    Dir  Data    TABLE NAME   csv  EXEC master  xp cmdshell  CmdExc         --END     END --BEGIN Drop TEMP Table IF Exists SET  SQL  IF  EXISTS  SELECT   FROM    DB   INFORMATION SCHEMA TABLES WHERE TABLE NAME     TEMP    TABLE NAME       BEGIN EXEC   DROP TABLE     DB     dbo   TEMP    TABLE NAME       END  EXEC  SQL

User · Answer

The latest version of sqlcmd adds the -w option to remove extra space after the field value  however  it does not put quotes around strings  which can be a problem with CSV files when importing a field value that contains a comma

User · Answer

You should be able to solve this problem with one cte view and one batch file containing the bcp code  First create the view  Since  it s relatively straightforward  I did not create a temporary table  Normally I do  CREATE VIEW  dbo   vwxMySAMPLE EXTRACT COLUMNS    AS     WITH MYBCP CTE  COLUMN NM  ORD POS  TXT       AS         SELECT  COLUMN NAME                ORDINAL POSITION                CAST COLUMN NAME AS VARCHAR MAX           FROM  INFORMATION SCHEMA   COLUMNS          WHERE TABLE NAME    xMySAMPLE EXTRACT NEW          AND ORDINAL POSITION   1          UNION ALL          SELECT  V COLUMN NAME                 V ORDINAL POSITION                 CAST C TXT         V COLUMN NAME AS VARCHAR MAX           FROM  INFORMATION SCHEMA   COLUMNS   V INNER JOIN MYBCP CTE C         ON V ORDINAL POSITION   C ORD POS 1         AND V ORDINAL POSITION  gt  1         WHERE TABLE NAME    xMySAMPLE EXTRACT NEW                 SELECT CC TXT       FROM MYBCP CTE CC INNER JOIN   SELECT MAX ORD POS  AS MX CNT                                      FROM MYBCP CTE C                                       SC       ON CC ORD POS   SC MX CNT   Now  create the batch file  I created this in my Temp directory  but I m lazy     cd    CD  C  Program Files Microsoft SQL Server 110 Tools Binn     set buildhour  time   0    set buildDate  DATE  4 10    set backupfiledate  buildDate  6 4  buildDate  0 2  buildDate  3 2  time  0 2  time  3 2  time  6 2     echo  backupfiledate    pause   The above code just creates a date to append to the end of your file    Next  the first bcp statement with the view to the recursive cte to concatenate it all together     bcp  SELECT    FROM  dbo   vwxMYSAMPLE EXTRACT COLUMNS  OPTION  MAXRECURSION 300   queryout C  Temp Col NM backupfiledate  txt -c -t    -S MYSERVERTOLOGINTO -T -q   bcp  SELECT    FROM  myDBName   dbo   vwxMYSAMPLE EXTRACT NEW    queryout C  Temp 3316 PHYSDATA ALL backupfiledate  txt -c -t    -S MYSERVERTOLOGINTO -T -q   Now merge them together using the copy command     copy C  Temp Col NM backupfiledate  txt    C  Temp 3316 PHYSDATA ALL backupfiledate  txt  C  Temp 3316 PHYSDATA ALL backupfiledate  csv   All set

User · Answer

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

User · Answer

DECLARE  table name varchar max   tableName --which needs to be exported DECLARE  fileName varchar max   file Name --What would be file name  DECLARE  query varchar 8000  DECLARE  columnHeader VARCHAR max  SELECT  columnHeader   COALESCE  columnHeader                column name        FROM INFORMATION SCHEMA COLUMNS WHERE TABLE NAME    table name  DECLARE  ColumnList VARCHAR max  SELECT  ColumnList   COALESCE  ColumnList            CAST   column name    AS VARCHAR    column name FROM INFORMATION SCHEMA COLUMNS WHERE TABLE NAME    table name  DECLARE  tempRaw sql nvarchar max  SELECT  tempRaw sql    SELECT      ColumnList     into   temp11 FROM      table name  PRINT  tempRaw sql EXECUTE sp executesql   tempRaw sql   DECLARE  raw sql nvarchar max  SELECT  raw sql    SELECT      columnHeader    UNION ALL SELECT   FROM   temp11  PRINT  raw SQL SET  query  bcp     raw SQL    queryout  C  data    fileName   txt  -T -c -t   EXEC xp cmdshell  query   DROP TABLE   temp11

User · Answer

From all I know  BCP only exports the data - I don t think there s any way to make it export the header row with column names  too   One common technique seen to solve this is to use a view over your actual data for export  which basically does a UNION ALL over two statements    the first statement to give back one row with the column headers the actual data to be export   and then use bcp on that view  instead of your underlying data table directly   Marc

User · Answer

Please find below another way to make the same thing    This procedure also takes in a schema name as a parameter in case you need it to access your table   CREATE PROCEDURE Export Data NBA  TableName nchar 50    TableSchema nvarchar 50        AS DECLARE  TableToBeExported as nvarchar 50    DECLARE  OUTPUT TABLE  col1 nvarchar max    DECLARE  colnamestable VARCHAR max   select  colnamestable   COALESCE  colnamestable       COLUMN NAME      from INFORMATION SCHEMA COLUMNS where TABLE NAME    TableName order BY ORDINAL POSITION  SELECT  colnamestable   LEFT  colnamestable DATALENGTH  colnamestable -1   INSERT INTO  OUTPUT select  colnamestable  DECLARE  selectstatement VARCHAR max   select  selectstatement   COALESCE  selectstatement         Convert nvarchar 100    COLUMN NAME            from INFORMATION SCHEMA COLUMNS where TABLE NAME    TableName order BY ORDINAL POSITION  SELECT  selectstatement   LEFT  selectstatement DATALENGTH  selectstatement -1   DECLARE  sqlstatment as nvarchar max    SET  TableToBeExported    TableSchema      TableToBeExported  SELECT  sqlstatment   N Select    selectstatement N  from    TableToBeExported  INSERT INTO  OUTPUT exec sp executesql  stmt    sqlstatment  SELECT   from  OUTPUT

User · Answer

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 Question1355876 values  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 all select 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

[sql-server] Export table to file with column headers (column names) using the bcp utility and SQL Server 2008

Examples related to sql-server

Examples related to csv

Examples related to header

Examples related to bcp