Exporting from SQL Server to CSV with column names

SQL Server can easily export to CSV file, but it exports just the data, without the column names included. In order to export the column names, you need to actually perform two exports – one with the column names, and one with the data – and then combine the two files into a single file. It populates

You could do this using any query you want – native SQL, a linked server, a stored procedure, or anything else – and the results will export the same way once they’re in the temp table. Since it builds the list of column name dynamically as well, you only need to change out the query being executed and set the export location – no other configuration is necessary.

-- Declare the variables
DECLARE @CMD VARCHAR(4000),
        @DelCMD VARCHAR(4000),
        @HEADERCMD VARCHAR(4000),
        @Combine VARCHAR(4000),
        @Path VARCHAR(4000),
        @COLUMNS VARCHAR(4000)

-- Set values as appropriate
    SET @COLUMNS = ''
    SET @Path = '\\servername\share\outputpath'

-- Set up the external commands and queries we'll use through xp_cmdshell
-- Note that they won't execute until we populate the temp tables they refer to
    SET @CMD = 'bcp "select * from ##OutputTable" queryout "' + @Path + '\Temp_RawData.csv" -S ' + @@SERVERNAME + ' -T -t , -c'
    SET @HEADERCMD = 'bcp "SELECT * from ##cols" queryout "' + @Path + '\Temp_Headers.csv" -S ' + @@SERVERNAME + ' -T -t , -c'
    SET @Combine = 'copy "' + @Path + '\Temp_Headers.csv" + "' + @Path + '\Temp_RawData.csv" "' + @Path + '\MyCombinedFile.csv"'
    SET @DelCMD = 'del "' + @Path + '\Temp_*.csv"'

-- Create and populate our temp table with the query results
SELECT *
  INTO ##OutputTable
  FROM YourSourceTable

-- Generate a list of columns	
 SELECT @COLUMNS = @COLUMNS + c.name + ','
   from tempdb..syscolumns c
   join tempdb..sysobjects t
     on c.id = t.id
  where t.name like '##OutputTable%'
  order by colid
  
  SELECT @COLUMNS as Cols INTO ##Cols
		
-- Run the two export queries - first for the header, then for the data
exec xp_cmdshell @HEADERCMD
exec xp_cmdshell @CMD

-- Combine the two files into a single file
exec xp_cmdshell @Combine

-- Clean up the two temp files we created
exec xp_cmdshell @DelCMD

-- Clean up our temp tables
drop table ##cols
drop table ##OutputTable

If you have any suggestions or run into any issues, please let me know!

16 thoughts on “Exporting from SQL Server to CSV with column names”

  1. Hi,

    Could you give an example of the above script with actual values please?

    Particularly for:
    SET @COLUMNS = ”
    SET @Path = ‘\\servername\share\outputpath’

    I am a little unclear, particularly on @COLUMNS what the actual input would look like.

    Many thanks,

    Q

    1. I can see the confusion in the way I’ve written it – you don’t need to set this value at all. It needs to be set to ” when it’s created so that the query later can set the list of columns (if it’s still NULL, this won’t work).

      You don’t need to change this value.

  2. I want to export the results of a stored procedure, i didn’t find where should I set my stored Procedure name

  3. I am a little confused with the @@SERVERNAME variable, it is not defined in some place, from which I assume that it is taken from the system. However, I am no t sure what is the value it is taking in my case. On the other hand I have my query into a .csv file, When it is populated the temp table should I import this file into the variable YourSourceTable, or should I run this script immediately my SQL query script, with the source table stored in the memory?

    1. The “@@SERVERNAME” is a system variable that holds the name of the current SQL Server – it’s in the script to ensure that the bcp command connects to the correct server. If you’re only running one instance on the server, connecting to LOCALHOST would work just fine, but it wouldn’t work if you’ve got multiple instances. In this case, if you’re running a named instance (like “DBSERVER\SQLINSTANCE7), it will connect correctly to the location you’re running the script.

      It is possible to override the value returned by @@SERVERNAME to something else – there are a few reasons you’d want to do this, but the most common is when you physically rename the host and you want it to stay accurate. This means @@SERVERNAME may not actually be correct, but it’s the best way to get the database server’s name.

  4. I have tried to execute this, but the Col returnrd is null? Could you pleae help me in this
    SELECT @COLUMNS = @COLUMNS + c.name + ‘,’
    from FIMU_POCTest.sys.syscolumns c
    join FIMU_POCTest.sys.sysobjects t
    on c.id = t.id
    where t.name like ‘##OutputTable%’
    order by colid

    SELECT @COLUMNS as Cols INTO ##Cols

    1. I’m not exactly sure what’s going on here, but my first guess is that you’re selecting from the table in your primary database (FIMU_POCTest) and the temp tables are always located in tempdb instead. Try to leave the original query pointing at tempdb and see if that resolves your problem.

      Because of the way it’s appending columns into the variable, a query with no rows would result in a NULL variable.

Leave a Reply

Your email address will not be published. Required fields are marked *

Why ask?