BULK INSERT制表符分隔的文件 - 用逗号描述

Question

早上好

我想用BULK INSERT函数导入CSV文件。我通过SQL来完成它，因为它可以是一个自动化过程。我遇到的问题是，我从客户端获得的CSV文件在项目描述中有逗号，例如Rosetta 10,5x25x4 - 当我尝试导入它时，描述中的逗号导致出现问题：以下是我正在运行的脚本：

BULK INSERT ORDERS_DATA 
FROM 'C:\back_orders_2013.csv' 
WITH 
(
    FIRSTROW = 2, 
    FIELDTERMINATOR = ',', 
    ROWTERMINATOR = '\n', 
    TABLOCK 
) 

有没有办法忽略像在excel中把连续分隔符当作一个对象的方式？

感谢

Answer 1

这里是解决方案：

<?xml version="1.0"?> 
    <BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" 
     xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> 
    <RECORD> 
     <FIELD ID="1" xsi:type="CharTerm" TERMINATOR=',' MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/> 
     <FIELD ID="2" xsi:type="CharTerm" TERMINATOR=',' MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/> 
     <FIELD ID="3" xsi:type="CharTerm" TERMINATOR=',' MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/> 
     <FIELD ID="4" xsi:type="CharTerm" TERMINATOR=',' MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/> 
     <FIELD ID="5" xsi:type="CharTerm" TERMINATOR=',' MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/> 
     <FIELD ID="6" xsi:type="CharTerm" TERMINATOR=',' MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/> 
     <FIELD ID="7" xsi:type="CharTerm" TERMINATOR=',' MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/> 
     <FIELD ID="8" xsi:type="CharTerm" TERMINATOR=',' MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/> 
     <FIELD ID="9" xsi:type="CharTerm" TERMINATOR=',' MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/> 
     <FIELD ID="10" xsi:type="CharTerm" TERMINATOR=',' MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/> 
     <FIELD ID="11" xsi:type="CharTerm" TERMINATOR=',' MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/> 
     <FIELD ID="12" xsi:type="CharTerm" TERMINATOR=',' MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/> 
     <FIELD ID="13" xsi:type="CharTerm" TERMINATOR=',' MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/> 
     <FIELD ID="14" xsi:type="CharTerm" TERMINATOR=',' MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/> 
     <FIELD ID="15" xsi:type="CharTerm" TERMINATOR=',' MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/> 
     <FIELD ID="16" xsi:type="CharTerm" TERMINATOR=',' MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/> 
     <FIELD ID="17" xsi:type="CharTerm" TERMINATOR=',' MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/> 
     <FIELD ID="18" xsi:type="CharTerm" TERMINATOR=',' MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/> 
     <FIELD ID="19" xsi:type="CharTerm" TERMINATOR='\n' MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/> 
    </RECORD> 
    <ROW> 
     <COLUMN SOURCE="1" NAME="RKID" xsi:type="SQLVARYCHAR" /> 
     <COLUMN SOURCE="2" NAME="ORNO" xsi:type="SQLVARYCHAR" /> 
     <COLUMN SOURCE="3" NAME="PONO" xsi:type="SQLVARYCHAR" /> 
     <COLUMN SOURCE="4" NAME="ITEM_CODE" xsi:type="SQLVARYCHAR" /> 
    <COLUMN SOURCE="5" NAME="ITEM_DESCRIPTION" xsi:type="SQLVARYCHAR" /> 
    <COLUMN SOURCE="6" NAME="ORDER_QTY" xsi:type="SQLVARYCHAR" /> 
    <COLUMN SOURCE="7" NAME="DELIVER_QTY" xsi:type="SQLVARYCHAR" /> 
    <COLUMN SOURCE="8" NAME="B_QTY" xsi:type="SQLVARYCHAR" /> 
    <COLUMN SOURCE="9" NAME="RTS_DATE" xsi:type="SQLVARYCHAR" /> 
    <COLUMN SOURCE="10" NAME="PRICE" xsi:type="SQLVARYCHAR" /> 
    <COLUMN SOURCE="11" NAME="PLEV" xsi:type="SQLVARYCHAR" /> 
    <COLUMN SOURCE="12" NAME="DISCOUNT1" xsi:type="SQLVARYCHAR" /> 
    <COLUMN SOURCE="13" NAME="DISCOUNT2" xsi:type="SQLVARYCHAR" /> 
    <COLUMN SOURCE="14" NAME="DISCOUNT3" xsi:type="SQLVARYCHAR" /> 
    <COLUMN SOURCE="15" NAME="TOTAL" xsi:type="SQLVARYCHAR" /> 
    <COLUMN SOURCE="16" NAME="CURRENCY" xsi:type="SQLVARYCHAR" /> 
    <COLUMN SOURCE="17" NAME="ORDER_DATE" xsi:type="SQLVARYCHAR" /> 
    <COLUMN SOURCE="18" NAME="BKYN" xsi:type="SQLVARYCHAR" /> 
    <COLUMN SOURCE="19" NAME="PENTA_REF" xsi:type="SQLVARYCHAR" /> 
    </ROW> 
    </BCPFORMAT> 

这工作100％和错误我收到：

String or binary data would be truncated. The statement has been terminated

。是因为我的一个数据字段的长度。

谢谢