如何将含JSON列的CSV文件正确导入SQL Server？

当CSV中包含JSON格式的列（如示例行 1,{"testId": 2, "testName":"Hello!"},3,4,5），直接用BULK INSERT会把JSON内部的逗号误识别为字段分隔符，且无法修改原CSV文件时，可通过以下几种方案解决：

方案一：使用XML格式文件配合字段修复 #

SQL Server的BULK INSERT支持通过格式文件定义字段边界，针对JSON字段{...}包裹的特征，先提取JSON主体再补全闭合符号：

1. 创建XML格式文件（例如命名为FormatFile.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>
  <FIELD ID="1" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="10"/>
  <FIELD ID="2" xsi:type="CharTerm" TERMINATOR="},{" MAX_LENGTH="1000"/>
  <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="10"/>
  <FIELD ID="4" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="10"/>
  <FIELD ID="5" xsi:type="CharTerm" TERMINATOR="0x0A" MAX_LENGTH="10"/>
 </RECORD>
 <ROW>
  <COLUMN SOURCE="1" NAME="Col1" xsi:type="SQLINT"/>
  <COLUMN SOURCE="2" NAME="Col2" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="3" NAME="Col3" xsi:type="SQLINT"/>
  <COLUMN SOURCE="4" NAME="Col4" xsi:type="SQLINT"/>
  <COLUMN SOURCE="5" NAME="Col5" xsi:type="SQLINT"/>
 </ROW>
</BCPFORMAT>

2. 执行BULK INSERT并修复JSON字段：

BULK INSERT TableName
FROM '<PathToCSV>'
WITH
(
  FIRSTROW = 1,
  FORMATFILE = '<PathToFormatFile.xml>',
  TABLOCK
)

-- 补全JSON字段的闭合大括号
UPDATE TableName SET Col2 = Col2 + '}'

方案二：通过OPENROWSET读取整行后手动拆分 #

直接读取每行作为完整字符串，利用字符串函数定位JSON的边界，精准拆分字段，避免误识别内部逗号：

SELECT
  TRY_CAST(SUBSTRING(Line, 1, CHARINDEX(',', Line) - 1) AS INT) AS Col1,
  TRY_CAST(SUBSTRING(Line, CHARINDEX(',', Line) + 1, CHARINDEX('}', Line, CHARINDEX(',', Line)) - CHARINDEX(',', Line)) AS NVARCHAR(MAX)) AS Col2,
  TRY_CAST(SUBSTRING(Line, CHARINDEX('}', Line, CHARINDEX(',', Line)) + 2, CHARINDEX(',', Line, CHARINDEX('}', Line, CHARINDEX(',', Line)) + 2) - (CHARINDEX('}', Line, CHARINDEX(',', Line)) + 2)) AS INT) AS Col3,
  TRY_CAST(SUBSTRING(Line, CHARINDEX(',', Line, CHARINDEX(',', Line, CHARINDEX('}', Line, CHARINDEX(',', Line)) + 2) + 1), CHARINDEX(',', Line, CHARINDEX(',', Line, CHARINDEX(',', Line, CHARINDEX('}', Line, CHARINDEX(',', Line)) + 2) + 1)) - (CHARINDEX(',', Line, CHARINDEX(',', Line, CHARINDEX('}', Line, CHARINDEX(',', Line)) + 2) + 1))) AS INT) AS Col4,
  TRY_CAST(RIGHT(Line, LEN(Line) - CHARINDEX(',', Line, CHARINDEX(',', Line, CHARINDEX(',', Line, CHARINDEX('}', Line, CHARINDEX(',', Line)) + 2) + 1))) AS INT) AS Col5
INTO TableName
FROM OPENROWSET(BULK '<PathToCSV>', SINGLE_CLOB) AS Data(Content)
CROSS APPLY STRING_SPLIT(Data.Content, CHAR(10)) AS Rows(Line)
WHERE Line <> '' -- 过滤空行

方案三：使用SSIS脚本组件解析 #

如果有SQL Server Integration Services（SSIS）环境，可创建数据导入任务：

• 添加平面文件源，连接到目标CSV；
• 插入脚本组件，在脚本中编写逻辑，按行读取内容，通过匹配大括号位置拆分字段，确保JSON列完整提取；
• 将处理后的数据写入目标SQL Server表。

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