Saving SQL Query Results to Comma Delimited Files (CSV)

The following scripts allow you to save query results to a file without the need to use DTS packages or BCP. The comma delimited format is usually the most accepted format, however, I have customized the script to allow delimiters of different types (like |). 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 for you.

There are two scripts, one that places quotes around data which contains the delimiter (for example "Citizen, John"), while the other script which places quotes around all columns even though it contains no data (for example "ColumnA", "", "ColumnC")

SaveDelimitedColumns and SaveDelimitedColumnsNullQuoted (Size 12 KB)

Installation:

These scripts create stored procedures. You can place them in specific databases, otherwise, if they will be commonly used by various processes, then you can place them in the master database.
The stored procedures are independent of each other - so you only install the one you require (or both if you desire it)

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 2005, by default, has disabled this use of xp_cmdshell. To enable this on the server, run the following script in a Query Analyzer sysadmin session on the server:

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'xp_cmdshell', 1;
RECONFIGURE;

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 Query Analyzer sysadmin session on the server:

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'Ole Automation Procedures',1;
RECONFIGURE;

General Usage:

The SaveDelimitedColumns stored procedure accepts eight parameters, but only @DBFetch is required.

Parameter	Optional/ Mandatory	Data Type	Function
@DBFetch	Mandatory	varchar(4000)	Specifies the set of data. The parameter can be a table name (including a temporary table if using the bcp method), view name, user-defined function call or SELECT statement.
@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. If no value is provided the results are returned as text. When you use this parameter, you will normally set the parameter @DBUltra = 1
@DBWhere	Optional	varchar(2000)	Specifies a WHERE clause to filter the data specified by the @DBFetch parameter.
@DBThere	Optional	varchar(2000)	Specifies an ORDER BY clause to sort the data specified by the @DBFetch parameter.
@DBUltra	Optional	bit	@DBUltra is optional and it specifies which data-file-creation method to use. A value of zero (0) uses the bcp method. (Default) A value of one (1) uses the OLE automation method. (Preferred method)
@Delimiter	Optional	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	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	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.

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:

EXECUTE SaveDelimitedColumns @DBFetch='select * from Northwind..Customers', @DBWhere='Country = ^USA^'

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 to Query Analyzer and is done for demonstration purposes only. Please substitute a complete path, including file name, that's appropriate for your environment.

Example 1: Basic Calls 1

This example exports customers from the Northwind database where the Country column is Italy..

EXECUTE SaveDelimitedColumns @DBFetch='select * from Northwind..Customers', @DBWhere='Country = ^Italy^'

"FRANS","Franchi S.p.A.","Paolo Accorti","Sales Representative","Via Monte Bianco 34","Torino",,"10100","Italy","011-4988260","011-4988261" "MAGAA","Magazzini Alimentari Riuniti","Giovanni Rovelli","Marketing Manager","Via Ludovico il Moro 22","Bergamo",,"24100","Italy","035-640230","035-640231" "REGGC","Reggiani Caseifici","Maurizio Moroni","Sales Associate","Strada Provinciale 124","Reggio Emilia",,"42100","Italy","0522-556721","0522-556722"

Example 2: Basic Calls 2

This example exports a product list sorted by ProductID from the Northwind database.

USE Northwind 
EXECUTE SaveDelimitedColumns NULL, 'Products', NULL, 'ProductID'

1,"Chai",1,1,"10 boxes x 20 bags",18.00,39,0,10,0 2,"Chang",1,1,"24 - 12 oz bottles",19.00,17,40,25,0 3,"Aniseed Syrup",1,2,"12 - 550 ml bottles",10.00,13,70,25,0 4,"Chef Anton's Cajun Seasoning",2,2,"48 - 6 oz jars",22.00,53,0,0,0

Another method to call the above is..

USE Northwind 
EXECUTE SaveDelimitedColumns @DBFetch = 'Products', @DBThere = 'ProductID'

Example 3: Changing the Delimiter

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

USE Northwind 
EXECUTE SaveDelimitedColumns @DBFetch = 'Products', @DBThere = 'ProductID', @Delimiter = 'CHAR(124)'

1|"Chai"|1|1|"10 boxes x 20 bags"|18.00|39|0|10|0 2|"Chang"|1|1|"24 - 12 oz bottles"|19.00|17|40|25|0 3|"Aniseed Syrup"|1|2|"12 - 550 ml bottles"|10.00|13|70|25|0 4|"Chef Anton's Cajun Seasoning"|2|2|"48 - 6 oz jars"|22.00|53|0|0|0

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

USE Northwind 
EXECUTE SaveDelimitedColumns @DBFetch='Products', @DBThere='ProductID', @Delimiter='CHAR(124)+CHAR(32)'

1| "Chai"| 1| 1| "10 boxes x 20 bags"| 18.00| 39| 0| 10| 0 2| "Chang"| 1| 1| "24 - 12 oz bottles"| 19.00| 17| 40| 25| 0 3| "Aniseed Syrup"| 1| 2| "12 - 550 ml bottles"| 10.00| 13| 70| 25| 0 4| "Chef Anton's Cajun Seasoning"| 2| 2| "48 - 6 oz jars"| 22.00| 53| 0| 0| 0

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..

USE Northwind 
EXECUTE SaveDelimitedColumns @DBFetch = 'Suppliers', @TextQuote = 'CHAR(39)'

1,'Exotic Liquids','Charlotte Cooper','Purchasing Manager','49 Gilbert St.','London',,'EC1 4SD','UK','(171) 555-2222', 2,'New Orleans Cajun Delights','Shelley Burke','Order Administrator','P.O. Box 78934','New Orleans','LA','70117','USA','(100) 555-4822', 3,'Grandma Kelly''s Homestead','Regina Murphy','Sales Representative','707 Oxford Rd.','Ann Arbor','MI','48104','USA','(313) 555-5735','(313) 555-3349' 4,'Tokyo Traders','Yoshi Nagase','Marketing Manager','9-8 Sekimai Musashino-shi','Tokyo',,'100','Japan','(03) 3555-5011',

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

USE Northwind 
EXECUTE SaveDelimitedColumns @DBFetch = 'Suppliers', @TextQuote = 'SPACE(0)'

1,Exotic Liquids,Charlotte Cooper,Purchasing Manager,49 Gilbert St.,London,,EC1 4SD,UK,(171) 555-2222, 2,New Orleans Cajun Delights,Shelley Burke,Order Administrator,P.O. Box 78934,New Orleans,LA,70117,USA,(100) 555-4822, 3,Grandma Kelly's Homestead,Regina Murphy,Sales Representative,707 Oxford Rd.,Ann Arbor,MI,48104,USA,(313) 555-5735,(313) 555-3349 4,Tokyo Traders,Yoshi Nagase,Marketing Manager,9-8 Sekimai Musashino-shi,Tokyo,,100,Japan,(03) 3555-5011,

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..

USE Northwind 
EXECUTE SaveDelimitedColumns @DBFetch = 'select CompanyName, ContactName, Country from Suppliers', 
	@DBWhere = 'Country = ^France^', @Header = 1

"CompanyName","ContactName","Country" "Aux joyeux ecclésiastiques","Guylène Nodier","France" "Escargots Nouveaux","Marie Delamare","France" "Gai pâturage","Eliane Noz","France"

Please note that the following query will result in an error because the label for CompanyName has a space which SaveDelimitedColumns cannot handle..

USE Northwind 
EXECUTE SaveDelimitedColumns 
	@DBFetch = 'select CompanyName as "Company Name", ContactName, Country from Suppliers', 
	@DBWhere = 'Country = ^France^', @Header = 1

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 CONVERT 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.

select 
convert(char(20),IsNull(LastName,'')) + 
convert(char(10),IsNull(FirstName,'')) + 
convert(char(15),IsNull(City,'')) + 
convert(char(15),IsNull(Region,'')) + 
convert(char(15),IsNull(Country,'')) as FixedWidth
into #tmp
from Employees
USE Northwind 
EXECUTE SaveDelimitedColumns @DBFetch = '#tmp', @Delimiter = 'SPACE(0)', @TextQuote = 'SPACE(0)'

Davolio Nancy Seattle WA USA Fuller Andrew Tacoma WA USA Leverling Janet Kirkland WA USA Peacock Margaret Redmond WA USA Buchanan Steven London UK Suyama Michael London UK King Robert London UK Callahan Laura Seattle WA USA Dodsworth Anne London UK

Example 7: SaveDelimitedColumnsNullQuoted

The SaveDelimitedColumns stored procedure will not place quotes around nulls. The SaveDelimitedColumnsNullQuoted stored procedure will do this..

USE Northwind 
EXECUTE SaveDelimitedColumnsNullQuoted 
	@DBFetch = 'select LastName, FirstName, City, Region, Country from Employees'

"Davolio","Nancy","Seattle","WA","USA" "Fuller","Andrew","Tacoma","WA","USA" "Leverling","Janet","Kirkland","WA","USA" "Peacock","Margaret","Redmond","WA","USA"

SQL Formatting Errors:

Occasionally, the query you are using and/or the column data type or name will produce an error similar to the one below:

Server: Msg 16916, Level 16, State 1, Procedure SaveDelimitedColumns, Line 368 A cursor with the name 'Records' does not exist. Server: Msg 16916, Level 16, State 1, Procedure SaveDelimitedColumns, Line 372 A cursor with the name 'Records' does not exist. Server: Msg 16916, Level 16, State 1, Procedure SaveDelimitedColumns, Line 418 A cursor with the name 'Records' does not exist. Server: Msg 16916, Level 16, State 1, Procedure SaveDelimitedColumns, Line 418 A cursor with the name 'Records' does not exist.

Usually this error is a result of the stored procedure SaveDelimitedColumns not being able to interpret the results so it can convert the data to your requirements. Please check the query you are using in @DBFetch and refer to the Caveats/Recommendations section below for more information.

Caveats/Recommendations:

Column titles must not contain spaces. You will get an error if any column contains spaces. A suggestion is to use underscores.
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]
Do not use complex queries in @DBFetch, instead, output your query to a temporary table and then reference the temporary table in @DBFetch.
Sometimes SaveDelimitedColumns can't handle some data types so CONVERT or CAST the results to a varchar.
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.
When you use the parameter @PCWrite to specify the full path and filename of the output file, you will normally need set the parameter @DBUltra = 1 as well. If not, you may get an error "Windows error [1]".
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.

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.