BCP to CSV file with commas in the data

I have a BCP process that is calling a stored procedure. Typically I have been executing this stored procedure and copying the data to an Excel sheet that I specifed all columns as text and saved this as a CSV.

I need to automate this work and have been playing with the BCP command but so far have an issues. The data I have has commas in it which shifts data to the left. Is this something I can overcome with a format file or something of the sort?

I would rather not quote these in the output of the proc itself.

Solution

The BCP command has the -t switch which specifies the field terminator. In your case, it is a comma or CHR(44).

http://technet.microsoft.com/en-us/library/ms162802.aspx

To prevent MS Excel from having issues opening the file, enclose all text fields in the QUERY with double quotes "" or CHR(34).

Here is a sample query from Adventure Works.

-- Enclose text w/ possible commas in quotes
select 
  char(34) + AddressLine1 + char(34) as fmt_address_line1,
  char(34) + City + char(34) as fmt_city,
  PostalCode as postal_code
from 
  [AdventureWorks2012].[Person].[Address]

This should allow you to open the file in MS Excel w/o any issues.