使用OPENROWSET批量导入CSV时遇FIELDQUOTE属性不支持错误

使用T-SQL的OPENROWSET BULK查询从.csv文件批量导入记录时，触发错误：Attribute "FIELDQUOTE" could not be specified for this type，已排查权限、行尾符等问题，仍无法解决。

执行的SQL语句 #

CREATE TABLE #TempMines (
MINE_ID NVARCHAR(10),
COAL_METAL_IND int,
CURRENT_MINE_TYPE DEC(5,2),
CURRENT_MINE_STATUS NVARCHAR(5),
CURRENT_STATUS_DT DATE);

INSERT INTO #TempMines (MINE_ID, COAL_METAL_IND, CURRENT_MINE_TYPE, CURRENT_MINE_STATUS, CURRENT_STATUS_DT)
SELECT MINE_ID, COAL_METAL_IND, CURRENT_MINE_TYPE, CURRENT_MINE_STATUS, CURRENT_STATUS_DT
FROM OPENROWSET(
    BULK 'C:\sqlStuff\sampleData.csv',
    FORMATFILE = 'C:\sqlStuff\sampleFormat.xml'
) AS TempMinesData;

对应的XML格式文件 #

<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="12" COLLATION="SQL_Latin1_General_CP1_CI_AS" FIELDQUOTE='"'/>
    <FIELD ID="2" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="12" FIELDQUOTE='"'/>
    <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="12" FIELDQUOTE='"'/>
    <FIELD ID="4" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="7" COLLATION="SQL_Latin1_General_CP1_CI_AS" FIELDQUOTE='"'/>
    <FIELD ID="5" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="12" FIELDQUOTE='"'/>
  </RECORD>
  <ROW>
    <COLUMN SOURCE="1" NAME="MINE_ID" xsi:type="SQLNVARCHAR"/>
    <COLUMN SOURCE="2" NAME="COAL_METAL_IND" xsi:type="SQLINT"/>
    <COLUMN SOURCE="3" NAME="CURRENT_MINE_TYPE" xsi:type="SQLDECIMAL" PRECISION="5" SCALE="2"/>
    <COLUMN SOURCE="4" NAME="CURRENT_MINE_STATUS" xsi:type="SQLNVARCHAR"/>
    <COLUMN SOURCE="5" NAME="CURRENT_STATUS_DT" xsi:type="SQLDATE"/>
  </ROW>
</BCPFORMAT>

已排查的内容 #

• 数据库为本地主机，确认目录及文件权限无问题
• 以管理员身份运行SSMS
• 核对行尾符与XML设置一致

解决方案 #

错误原因是XML格式文件中，FIELD元素使用了CharTerm类型，但该类型不支持FIELDQUOTE属性。需要将xsi:type改为CharDelimited，该类型专门用于处理带引号分隔的字段。

修改后的XML格式文件：

<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="CharDelimited" TERMINATOR="," MAX_LENGTH="12" COLLATION="SQL_Latin1_General_CP1_CI_AS" FIELDQUOTE='"'/>
    <FIELD ID="2" xsi:type="CharDelimited" TERMINATOR="," MAX_LENGTH="12" FIELDQUOTE='"'/>
    <FIELD ID="3" xsi:type="CharDelimited" TERMINATOR="," MAX_LENGTH="12" FIELDQUOTE='"'/>
    <FIELD ID="4" xsi:type="CharDelimited" TERMINATOR="," MAX_LENGTH="7" COLLATION="SQL_Latin1_General_CP1_CI_AS" FIELDQUOTE='"'/>
    <FIELD ID="5" xsi:type="CharDelimited" TERMINATOR="\r\n" MAX_LENGTH="12" FIELDQUOTE='"'/>
  </RECORD>
  <ROW>
    <COLUMN SOURCE="1" NAME="MINE_ID" xsi:type="SQLNVARCHAR"/>
    <COLUMN SOURCE="2" NAME="COAL_METAL_IND" xsi:type="SQLINT"/>
    <COLUMN SOURCE="3" NAME="CURRENT_MINE_TYPE" xsi:type="SQLDECIMAL" PRECISION="5" SCALE="2"/>
    <COLUMN SOURCE="4" NAME="CURRENT_MINE_STATUS" xsi:type="SQLNVARCHAR"/>
    <COLUMN SOURCE="5" NAME="CURRENT_STATUS_DT" xsi:type="SQLDATE"/>
  </ROW>
</BCPFORMAT>

替换格式文件后重新执行导入语句即可。

内容的提问来源于stack exchange，提问作者osm0sis