BCP到CSV文件的数据中带有逗号 [英] BCP to CSV file with commas in the data

问题描述

我有一个BCP进程正在调用存储过程.通常，我一直在执行此存储过程，并将数据复制到Excel工作表中，该工作表将所有列均指定为文本并将其另存为CSV.

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.

我需要使这项工作自动化，并且一直在使用BCP命令，但到目前为止还是有问题.我的数据中有逗号，将数据向左移动.这是我可以用格式文件克服的东西还是类似的东西?

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?

我不想在proc本身的输出中引用这些内容.

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

推荐答案

BCP命令具有-t开关，用于指定字段终止符.您的情况是逗号或CHR(44).

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

为防止MS Excel打开文件时出现问题，请在QUERY中的所有文本字段都用双引号"或CHR(34)括起来.

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

这是Adventure Works的示例查询.

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]

这应该允许您在MS Excel中打开文件而没有任何问题.

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

这篇关于BCP到CSV文件的数据中带有逗号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！