Overcome BCP limitations for output to CSV file
This is an example to overcome short comings when using BCP to create a 'proper' CSV file. The view vw_Customers_RAW is used in order to provide a method of creating the column heading row and to enclose columns with embedded comma with double quotes, which are not options in BCP.
There are more generic methods of creating the Header row using system tables, I elected to use text instead. This example uses the Northwind database's Customers table, so 'should' be available on most SQL Server 7/2000 systems.
Many thanks to members of this forum for the ideas that are shown here.
Enjoy,
Andy
USE Northwind
GO
IF EXISTS (SELECT * FROM sysobjects WHERE sysstat & 0xf = 2
AND id = OBJECT_ID('dbo.vw_Customers_RAW'))
DROP VIEW dbo.vw_Customers_RAW
GO
CREATE VIEW dbo.vw_Customers_RAW
AS
-- Create Header row
SELECT 'CustomerID'AS CustomerID, 'CompanyName' AS CompanyName
, 'ContactName' AS ContactName, 'ContactTitle' AS ContactTitle
, 'Address' AS Address, 'City' AS City, 'Region' AS Region
, 'PostalCode' AS PostalCode, 'Country' AS Country
, 'Phone' AS Phone, 'Fax' AS Fax
UNION ALL
SELECT RTRIM(CustomerID) -- remove trailing spaces from NCHAR
, CompanyName, ContactName, ContactTitle
-- enclose column with embedded comma with double quotes
, CASE WHEN CHARINDEX(',',Address)>0 THEN
QUOTENAME(Address,CHAR(34)) -- CHAR(34) = , (comma)
ELSE Address END
, City, Region, PostalCode, Country, Phone, Fax
FROM Customers
GO
-- Test the view
SELECT * FROM vw_Customers_RAW
GO
-- Use BCP to output the view
DECLARE @Path varchar(255), @Cmd varchar(255)
-- Define output path\file parameter
SET @Path='C:\Customers.csv'
-- Output "proper" CSV file with BCP
-- -T use SSPI for Integrated login
-- -w output to Unicode text w/o need for format file
-- -t change the column delimiter from TAB character to , (comma)
SET @Cmd='BCP Northwind.dbo.vw_Customers_RAW out '+@Path+' -T -w -t,'
EXEC master.dbo.xp_cmdshell @Cmd, NO_OUTPUT
GO 
