VirtualObjectives

Saving SQL Query Results to Comma Delimited Files (CSV)

The following script allows you to save query results to a file. The comma delimited format is usually the most accepted format, however, I have customized the script to allow delimiters of different types (like a pipe |). With carefully structured queries, this script is flexible enough to handle most of your requirements. Rather than explain the script itself, I'll show you several examples of what it can do.

Download SaveDelimitedColumns (Size 6 KiB)

Installation:

The script creates a stored procedure. You can place it in a specific database, otherwise, you can place it in the master database.

Depending on what option you choose for the parameter @DBUltra, you may need to enable the following...

If @DBUltra = 0 then it will require the use of xp_cmdshell. SQL Server 2008 R2 and above, by default, have disabled this use of xp_cmdshell calls to BCP.
If using temporary tables as the source then please use global temporary tables.
To enable this on the server, run the following script in a sysadmin query session on the server:

If @DBUltra = 1 then it will require the use of sp_OACreate, sp_OAMethod and sp_OADestroy which may be restricted.
If so, run the following script in a sysadmin query session on the server:

General Usage:

The SaveDelimitedColumns stored procedure accepts the following parameters...

Parameter Optional/
Mandatory
Data Type Function
@DBFetch Mandatory varchar(4000) Specifies the source dataset. The parameter can be a table name (including a temporary table if using the XP_CMDSHELL \ BCP method), view name, user-defined function call or SELECT statement.
Use the three part dot notation. For example [database].[schema owner].[table]
The use of implicit functions like CAST, CONVERT etc. cannot be specified in the @DBFetch parameter.
@DBWhere Optional varchar(2000) Specifies a WHERE clause to filter the data specified by the @DBFetch parameter.
NOTE: You are required to return the column used in @DBWhere to the output results.
@DBThere Optional varchar(2000) Specifies an ORDER BY clause to sort the data specified by the @DBFetch parameter.
@PCWrite Optional varchar(1000) @PCWrite specifies the location for the data file. The parameter must provide a complete path, including file name, to a location where the SQL Server service account is allowed to create a file.
Example: @PCWrite='C:\Temp\test.csv' will write the file test.csv in the C:\Temp\ directory of the SQL Server.
UNC paths to other servers can also be used provided the SQL Server service account has access to write the file.
If no value is provided the results are returned as text.
When you use this parameter, you will normally set the parameter @DBUltra = 1
@DBUltra Optional

Default = 1
bit @DBUltra is optional and it specifies which data-file-creation method to use.
A value of zero (0) uses the XP_CMDSHELL \ BCP method.
A value of one (1) uses the OLE automation method. (Default, Preferred method)
NOTE: @DBUltra = 0 will not work on SQL Server 2005 due to bug with BCP. It will work in SQL 2008 R2 and above.
@Delimiter Optional

Default = comma CHAR(44)
varchar(100) Used to set the column delimiter to a specific character. It needs to be a string. The default is a comma CHAR(44).
You must specify the CHAR equivalent for your character and NOT the actually character otherwise you will get an error. Example is 'CHAR(44)'. You may also concatenate CHARs up to 100 characters long. Example 'CHAR(44) + CHAR(32)'
@TextQuote Optional

Default = " CHAR(34)
varchar(100) Used to set the character used to place quotes around text. Default is " CHAR(34). Hint, use SPACE(0) for none.
@Header Optional

Default = 0
bit Used to output the column names as the first line.
A value of zero (0) does not include header in the output. (Default)
A value of one (1) includes header in the first line of the output.
@NullQuoted Optional

Default = 0
bit Place quotes around nulls rather than just have blanks.
Uses the character defined in the parameter @TextQuote
@DateTimeStyle Optional

Default = 120
tinyint Date Time Style for the CONVERT function. Default is 120 ODBC canonical yyyy-mm-dd hh:mi:ss(24h)

It's awkward to embed single quotes in a string literal with T-SQL. For convenience when specifying string parameters, caret symbols (^) are converted to single quotes in @DBFetch, @DBWhere and @DBThere. For example:

The SaveDelimitedColumns stored procedure creates a file at the @PCWrite location (on the SQL Server) and places in it the columns from the @DBFetch data set (optionally filtered/sorted by @DBWhere/@DBThere). By default, the values for any column that are not numeric are enclosed in quotation marks (such as "String") in case the values contain the column delimiter character (a comma). The values for each column are delimited, or separated, with commas (such as "String1",0,"String2").
The use of @Delimiter and @TextQuote allow you to change the character used by quotation marks and the delimiter.

Most of the following examples uses a NULL value for the destination file parameter @PCWrite. This will output the results as text and is done for demonstration purposes only. Please substitute a complete path, including file name, that is appropriate for your environment.

Example 1: Basic Calls 1

This example exports addresses from the AdventureWorks2014 database where the City column is Oxford...

Top five results...

Example 2: Basic Calls 2

This example exports the products from the Product table and sorts by ProductNumber...

Top five results...

A similar method to call the above is...

Example 3: Changing the Delimiter

By default the delimiter is a coma. This example will use a pipe | as the delimiter...

Top five results...

If you wanted to put a space after the delimiter then use...

Top five results...

Example 4: Changing the Text Quotation

The @TextQuote parameter is used to set the character used to place quotes around text. Default is " CHAR(34).
If you wanted to change this to a single quote then use...

Top five results...

If you do not want to place any quotes around text then use...

Top five results...

Example 5: Header

The @Header parameter is used to control whether or not to output the column names as the first line. By default it does not include header in the output.
This example shows how to display the header...

Top five results...

Example 6: Fixed Width

In this example we'll try to output the data as fixed width. It is important to note that the use of implicit functions like CAST, CONVERT etc. cannot be specified in the @DBFetch parameter. The solution is to output the results to a temporary table and then run the SaveDelimitedColumns stored procedure on the temporary table.
In order to display the data in fixed width, we need to CONVERT each column to its maximum size and then concatenate the fields and output the result into a temporary table.
If any of the columns contain nulls, then ISNULL is used to force the nulls to be an empty char. The parameters @Delimiter and @TextQuote are set to SPACE(0) is used to remove the quotes.
Naturally, the use of the parameter @Header cannot be used as this will not produce columns names in a fixed width.

Top five results...

Example 7: Output to a File

This example is similar to the one above, but we'll output the data to a CSV file.
Just remember that the output file specified in @PCWrite below will be written to C:\Temp\ of the SQL Server.
You could always specify a UNC path like \\ServerName\e$\path1\path2\filename.csv provided that the SQL Server has access to write the file at the intended destination.

When using @DBUltra = 0 (BCP) and a temporary table, make sure it is a global temporary table.

Top five results...

Example 8: Using @NullQuoted and @DateTimeStyle

Use @NullQuoted to place quotes around nulls rather than just have blanks as is the default. Use the @TextQuote parameter to define another character for the quotes.
The @DateTimeStyle parameter allows you to define a Date Time Style for the conversion of date\time columns.

Top five results...

Caveats/Recommendations:

  1. Column titles must not contain spaces. You will get an error if any column contains spaces. A suggestion is to use underscores.
  2. Avoid using column names that are T-SQL reserved words. Also avoid using ID as a column name. If you need to, then bound the name with braces [ID]
  3. Do not use complex queries in @DBFetch, instead, output your query to a temporary table and then reference the temporary table in @DBFetch.
  4. Sometimes SaveDelimitedColumns can't handle some data types so CONVERT or CAST the results to a varchar.
  5. The @Delimiter parameter needs to be a string up to 100 character long and must be in the format of CHAR(nn). You can concatenate other characters with multiple CHARs like 'CHAR(44) + CHAR(32)' to represent a comma with a space after it.
  6. @DBUltra = 0 (BCP) will not work on SQL Server 2005 due to bug with BCP, however, it will work on SQL Server 2008 R2 and above.
  7. If you are using @DBUltra = 0 and a temporary table, make sure it is a global temporary table.
  8. When using the @PCWrite parameter, please make sure that the path used actually exists as the path will not be created if incorrect. The file is saved on the SQL Server so the path and file needs to be appropriate. I recommend setting aside a path like C:\TEMP\ where the old files can be regularly removed.
  9. When you use the parameter @PCWrite and you get the error "Windows error [1]", then check that you specify the full path and filename of the output file and try changing the parameter @DBUltra.
  10. When using @DBWhere, you are required to return the column used in @DBWhere to the output results.

Acknowledgment:

The SQL script was originally provided by Brian Walker (http://searchsqlserver.techtarget.com)
Thanks Brian, you saved me from many many hours of frustration and stress.