Script to export table data from SQL Server to CSV, Flat File or XLS Format.

Problem : 

How to Export SQL Server Query Results into CSV, Flat File or XLS Format.

Solution :

BCP, xp_cmdshell

This can be done using multiple ways. But I am going to use the fastest way to export the data

 The following is the BCP arguments

bcp {table|view|"query"}  {out|queryout|in|format}   {data_file|nul}    {[optional_argument]...}

Let’s see the meanings inside these key words we are going to use for this process.

• queryout: The command exports data retrieved through a query into a data file.
• File Name: desired export file name with file extension, when specifying the file location to save please make sure the folder already exists.
• -T parameter this command will send BCP utility to use trusted connection using windows authentication, to change it to a SQL Server Login then specify –U(username) and –P(password). Also if you have a named instance specify the name.
• -c parameter character data type for each field.
• -t parameter this command allows a delimiter in the export file. To use comma (“,”) as field delimiter for columns, use (-t,)

Please see the following screenshot to see the available options and the usages of the commands.

Pre-Requisites:

By default, SQL Server configurations are set to OFF for cmdshell we might need to configure this by enabling the feature and reconfigure using sp_configure(Server Configuration Options (SQL Server)).

-- To enable the XP_CmdShell feature. 

EXEC sp_configure 'xp_cmdshell', 1; 

GO 

-- To update the currently configured value for this feature. 

RECONFIGURE; 

GO 

Example:

I am going to create a procedure which extracts the number of rows from a table to specified file name and format.

Results from SQL server inside the table 

Script: 

CREATE PROCEDURE dbo.ExtractTableRowstoExcelFile (

            @dbname VARCHAR(100),

            @SchemaName VARCHAR(100) = NULL,

            @tablename VARCHAR(100),

            @Rows VARCHAR(10) = NULL,

            @filename VARCHAR(100)

            )

AS

BEGIN

            DECLARE @sqlcommand VARCHAR(8000)

            IF @Rows IS NULL

            BEGIN

                        SET @Rows = ''

            END

            ELSE

                        SET @Rows = ' TOP ' + @Rows

            IF ISNULL(@Schemaname, '') = ''

            BEGIN

                        SET @Schemaname = 'dbo'

            END

            --Generate sql command to execute with parameters.

            SET @sqlcommand = 'exec master..xp_cmdshell ''bcp " SELECT' + @Rows + ' * FROM ' + @dbname + '.' + @schemaname + '.' + @tablename + '" queryout "' + @filename + '"   -T                    -c                    -t,'''

            EXEC (@sqlcommand)

END

Execution: 

Note : Target file location to save please make sure the folder already exists.

TADA...the wait is over.!

We have the file exported in CSV format and after opening we see all the rows in the spread sheet.