SQL Server 2014中Bulk Insert处理带可选单引号CSV的格式文件问题

嘿，我来帮你搞定这个CSV加载的问题！先理清楚你的核心困境：CSV里的地址列有的用单引号包裹（用来规避内部逗号的干扰），有的却没有，这种可选的单引号直接让你原来的格式文件彻底懵了，没法正确识别字段边界，所以加载失败完全在情理之中。下面给你几个靠谱的解决方案：

先说说你原来格式文件的问题 #

你给地址列指定的终止符是\'\',，但单引号是可选的——有的值带，有的不带，而且带单引号的列内部还有逗号，这就让Bulk Insert根本搞不清哪里是字段的结束位置，不报错才怪。

方法1：用QUOTE参数配合调整后的格式文件（最推荐） #

SQL Server的Bulk Insert其实自带处理带包裹符字段的能力，就是QUOTE参数，刚好你的CSV用的是单引号'，完美适配这个场景。

第一步：修改格式文件 #

把所有字段的终止符改回普通逗号，同时补全格式文件的ROW部分（你原来的格式文件只有RECORD，缺少映射到表列的关键部分）：

<?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="255" />
<FIELD ID="2" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="255" />
<FIELD ID="3" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="100" />
<FIELD ID="4" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="100" />
<FIELD ID="5" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="50" />
<FIELD ID="6" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="150" /> <!-- 注意Job列长度，Football Player超过15，得改大一点 -->
</RECORD>
<ROW>
<COLUMN SOURCE="1" NAME="Name" xsi:type="SQLVARCHAR" />
<COLUMN SOURCE="2" NAME="Age" xsi:type="SQLINT" />
<COLUMN SOURCE="3" NAME="AddressLine1" xsi:type="SQLVARCHAR" />
<COLUMN SOURCE="4" NAME="AddressLine2" xsi:type="SQLVARCHAR" />
<COLUMN SOURCE="5" NAME="AddressLine3" xsi:type="SQLVARCHAR" />
<COLUMN SOURCE="6" NAME="Job" xsi:type="SQLVARCHAR" />
</ROW>
</BCPFORMAT>

第二步：执行带QUOTE参数的Bulk Insert #

SQL里单引号需要用两个单引号转义，所以QUOTE = ''''，执行语句如下：

BULK INSERT YourTableName -- 替换成你的目标表名
FROM 'C:\YourFilePath\yourdata.csv' -- 替换成你的CSV实际路径
WITH (
    FORMATFILE = 'C:\YourFilePath\yourformat.xml', -- 替换成你的格式文件路径
    FIRSTROW = 2, -- 跳过第一行表头
    QUOTE = '''', -- 指定单引号为字段包裹符，自动忽略内部逗号
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '\r\n',
    TABLOCK -- 可选，能提升大文件的加载速度
);

这个方法会让SQL Server自动识别：如果字段被单引号包裹，就跳过内部的逗号，直到找到闭合的单引号再去寻找下一个分隔符，完美解决可选单引号的问题。

方法2：用OPENROWSET手动拆分清理（适合格式不规范的情况） #

如果格式文件的方式还是有问题，你可以先用OPENROWSET把整个CSV读成原始字符串，再手动拆分字段并清理单引号：

SELECT
    -- 提取Name字段并去除前后空格
    LTRIM(RTRIM(SUBSTRING(raw_line, 1, CHARINDEX(',', raw_line)-1))) AS Name,
    -- 提取Age字段并转成INT类型
    CAST(LTRIM(RTRIM(SUBSTRING(raw_line, CHARINDEX(',', raw_line)+1, CHARINDEX(',', raw_line, CHARINDEX(',', raw_line)+1)-CHARINDEX(',', raw_line)-1))) AS INT) AS Age,
    -- 提取AddressLine1并清理单引号
    LTRIM(RTRIM(REPLACE(
        SUBSTRING(raw_line, 
            CHARINDEX(',', raw_line, CHARINDEX(',', raw_line)+1)+1, 
            CHARINDEX(',', raw_line, CHARINDEX(',', raw_line, CHARINDEX(',', raw_line)+1)+1)-CHARINDEX(',', raw_line, CHARINDEX(',', raw_line)+1)-1
        ), '''', ''))) AS AddressLine1,
    -- 提取AddressLine2并清理单引号
    LTRIM(RTRIM(REPLACE(
        SUBSTRING(raw_line, 
            CHARINDEX(',', raw_line, CHARINDEX(',', raw_line, CHARINDEX(',', raw_line)+1)+1)+1, 
            CHARINDEX(',', raw_line, CHARINDEX(',', raw_line, CHARINDEX(',', raw_line, CHARINDEX(',', raw_line)+1)+1)+1)-CHARINDEX(',', raw_line, CHARINDEX(',', raw_line, CHARINDEX(',', raw_line)+1)+1)-1
        ), '''', ''))) AS AddressLine2,
    -- 提取AddressLine3并清理单引号
    LTRIM(RTRIM(REPLACE(
        SUBSTRING(raw_line, 
            CHARINDEX(',', raw_line, CHARINDEX(',', raw_line, CHARINDEX(',', raw_line, CHARINDEX(',', raw_line)+1)+1)+1)+1, 
            CHARINDEX(',', raw_line, CHARINDEX(',', raw_line, CHARINDEX(',', raw_line, CHARINDEX(',', raw_line, CHARINDEX(',', raw_line)+1)+1)+1)+1)-CHARINDEX(',', raw_line, CHARINDEX(',', raw_line, CHARINDEX(',', raw_line, CHARINDEX(',', raw_line)+1)+1)+1)-1
        ), '''', ''))) AS AddressLine3,
    -- 提取最后一个逗号后的Job字段
    LTRIM(RTRIM(SUBSTRING(raw_line, CHARINDEX(',', raw_line, LEN(raw_line)-CHARINDEX(',', REVERSE(raw_line)))+1, LEN(raw_line)))) AS Job
FROM OPENROWSET(BULK 'C:\YourFilePath\yourdata.csv', SINGLE_CLOB) AS data(raw_line)
WHERE raw_line NOT LIKE 'Name, Age%' -- 跳过表头行

这个方法更灵活，适合CSV格式不太标准的场景，但需要手动处理字段拆分，稍微麻烦一点。

方法3：预处理CSV文件（最简单粗暴） #

如果上面的方法都不想用，你可以先把CSV里的单引号全部去掉，再用普通的Bulk Insert加载。比如用PowerShell脚本一键处理：

$csvPath = "C:\YourFilePath\yourdata.csv"
$outputPath = "C:\YourFilePath\cleaned_data.csv"

# 读取每一行，替换所有单引号后保存
Get-Content $csvPath | ForEach-Object { $_ -replace "'", "" } | Set-Content $outputPath

处理后的CSV就没有单引号了，直接用普通的Bulk Insert语句就能正常加载。

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