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.
Using the "From Text" feature in Excel Click the Data tab, then From Text. Select the CSV file that has the data clustered into one column. Select Delimited, then make sure the File Origin is Unicode UTF-8. Select Comma (this is Affinity's default list separator).
Some people's name use commas, for example Joe Blow, CFA. This comma breaks the CSV format, since it's interpreted as a new column. I've read up and the most common prescription seems to be replacing that character, or replacing the delimiter, with a new value (e.g. this|that|the, other ).
If you pre-format your column to not Use 1000 Separator and you save as CSV or TXT (tab delimited) file, the values will not show up with commas within the data cell.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With