如何使用OPENROWSET将含JSON列的逗号分隔文本导入SQL Server？

前提准备 #

• 确保SQL Server服务账户拥有目标文本文件所在目录的读写权限
• 若使用OLEDB驱动，需安装对应位数（32/64位）的Microsoft.ACE.OLEDB.12.0驱动，且与SQL Server位数匹配

方法一：使用OLEDB驱动直接读取 #

适合快速处理带表头的单引号分隔文件，无需额外格式文件。

1. 创建目标数据表（若尚未存在）：

CREATE TABLE TargetTable (
    Name VARCHAR(50),
    Jobs NVARCHAR(MAX), -- 存储JSON内容，用NVARCHAR(MAX)适配长文本
    Dob DATE
);

2. 执行导入语句（替换实际文件路径和文件名）：

INSERT INTO TargetTable (Name, Jobs, Dob)
SELECT 
    [Name],
    Jobs,
    CONVERT(DATE, Dob)
FROM OPENROWSET(
    'Microsoft.ACE.OLEDB.12.0',
    'Text;Database=C:\YourFileFolder\;HDR=YES;FMT=Delimited('' '')', -- 指定单引号为分隔符
    'SELECT * FROM YourFileName.txt'
);

• HDR=YES表示第一行是表头，自动匹配目标表列名
• FMT=Delimited('' '')中双单引号是SQL中转义单个单引号的写法，实际指定分隔符为单引号

方法二：使用BULK导入+格式文件 #

更灵活，适配复杂格式的文本文件。

1. 创建目标数据表（同方法一）。

2. 创建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>
  <FIELD ID="1" xsi:type="CharTerm" TERMINATOR="','" MAX_LENGTH="50"/>
  <FIELD ID="2" xsi:type="CharTerm" TERMINATOR="','" MAX_LENGTH="MAX"/>
  <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="10"/>
 </RECORD>
 <ROW>
  <COLUMN SOURCE="1" NAME="Name" xsi:type="SQLVARCHAR"/>
  <COLUMN SOURCE="2" NAME="Jobs" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="3" NAME="Dob" xsi:type="SQLDATE"/>
 </ROW>
</BCPFORMAT>

• 格式文件定义了字段分隔符（','即单引号+逗号+单引号）和对应的数据表列

3. 执行导入语句（替换实际文件路径）：

INSERT INTO TargetTable (Name, Jobs, Dob)
SELECT 
    SUBSTRING(Name, 2, LEN(Name)-1), -- 去掉字段首尾的单引号
    SUBSTRING(Jobs, 2, LEN(Jobs)-1),
    SUBSTRING(Dob, 2, LEN(Dob)-1)
FROM OPENROWSET(
    BULK 'C:\YourFileFolder\YourFileName.txt',
    FORMATFILE = 'C:\YourFileFolder\format.xml',
    FIRSTROW = 2, -- 跳过表头行
    CODEPAGE = '65001' -- 若文件为UTF-8编码，需添加此参数
) AS ImportData;

验证导入结果 #

执行以下语句检查数据正确性，同时验证JSON字段有效性：

SELECT 
    Name,
    Jobs,
    Dob,
    ISJSON(Jobs) AS IsValidJson -- 返回1表示JSON格式有效
FROM TargetTable;

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