使用SQL Server Bulk Insert去除文本文件双引号问题

遇到这种带双引号包裹字段的导入问题，用BULK INSERT完全能实现自动化处理，不用依赖手动导入导出工具，我给你几个针对性的解决方案，你可以根据自己的SQL Server版本和文件情况选择：

方案一：直接使用FIELDQUOTE参数（推荐，SQL Server 2008及以上支持） #

如果你的SQL Server版本在2008或更高，直接在BULK INSERT语句里加上FIELDQUOTE参数，就能让数据库自动识别双引号作为字段的包裹符，同时自动去除引号，正确解析包含逗号的字段内容。

示例代码：

BULK INSERT YourTargetTable
FROM 'C:\YourDataFile.txt'
WITH (
    FIELDTERMINATOR = ',',       -- 字段分隔符是逗号
    ROWTERMINATOR = '\n',        -- 行分隔符根据实际文件调整，可能是'\r\n'
    FIELDQUOTE = '"',            -- 指定双引号为字段包裹符
    FIRSTROW = 2,                -- 如果文件第一行是表头，跳过第一行
    CODEPAGE = '65001',          -- 如果是UTF-8编码的文件，加上这个参数
    TABLOCK                      -- 提升导入性能
);

这个方案最简单，大部分场景下都能解决问题，核心就是FIELDQUOTE = '"'告诉BULK INSERT：被双引号包起来的内容是一个完整字段，哪怕里面有逗号也不会拆分。

方案二：使用XML格式化文件（兼容旧版本SQL Server） #

如果你的SQL Server版本比较旧（比如2005及更早），不支持FIELDQUOTE参数，可以创建一个XML格式化文件来定义字段的解析规则，精准处理带引号的字段。

首先创建一个format.xml文件，内容根据你的表结构调整：

<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
 <RECORD>
  <!-- 假设你的表有3个字段，字段格式为"值1","值2","值3" -->
  <FIELD ID="1" xsi:type="CharTerm" TERMINATOR='","' MAX_LENGTH="100"/>
  <FIELD ID="2" xsi:type="CharTerm" TERMINATOR='","' MAX_LENGTH="200"/>
  <FIELD ID="3" xsi:type="CharTerm" TERMINATOR='"\r\n' MAX_LENGTH="50"/>
 </RECORD>
 <ROW>
  <COLUMN SOURCE="1" NAME="Column1" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="2" NAME="Column2" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="3" NAME="Column3" xsi:type="SQLNVARCHAR"/>
 </ROW>
</BCPFORMAT>

然后在BULK INSERT中调用这个格式化文件：

BULK INSERT YourTargetTable
FROM 'C:\YourDataFile.txt'
WITH (
    FORMATFILE = 'C:\format.xml',
    FIRSTROW = 2,                -- 跳过表头（如果有）
    TABLOCK
);

这个格式化文件通过定义字段的终止符为","和"\r\n"，让BULK INSERT能正确识别每个被双引号包裹的字段，自动剥离引号。

方案三：预处理文本为JSON（复杂场景兜底） #

如果你的文件格式特别复杂（比如字段里有嵌套的引号或特殊字符），可以先用OPENROWSET读取整个文件，然后把每行文本转换成JSON格式，再解析插入：

INSERT INTO YourTargetTable (Column1, Column2, Column3)
SELECT 
    JSON_VALUE(json_row, '$.Column1'),
    JSON_VALUE(json_row, '$.Column2'),
    JSON_VALUE(json_row, '$.Column3')
FROM (
    SELECT 
        -- 把每行CSV转换成JSON格式，处理引号和逗号
        '{"Column1":"' + 
        REPLACE(REPLACE(REPLACE(BulkColumn, '"', '\"'), ',', '","'), '\r\n', '"}') + 
        '"' AS json_row
    FROM OPENROWSET(BULK 'C:\YourDataFile.txt', SINGLE_CLOB) AS data
) AS transformed
WHERE json_row NOT LIKE '{"Column1":"Header%' -- 跳过表头行

这个方法灵活性高，但只适合字段结构不复杂的场景，否则容易出现转义错误。

注意事项 #

• 行分隔符要和实际文件匹配：Windows生成的文件通常是\r\n，Linux/Mac生成的是\n，可以用文本编辑器查看文件的换行符类型。
• 如果文件是UTF-8编码，一定要加上CODEPAGE = '65001'参数，否则中文等非ASCII字符会乱码。

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