如何导入含空行的分号分隔TXT文件至SQL表并拆分列？

一、直接导入时忽略空行的方法 #

方法1：通过OPENROWSET(BULK)过滤空行 #

利用OPENROWSET读取文件内容，同时直接筛选掉空行，再拆分插入目标临时表：

CREATE TABLE #temp (
    Dato1 VARCHAR(100),
    Dato2 VARCHAR(100),
    -- 按需添加其他列
)

INSERT INTO #temp (Dato1, Dato2)
SELECT 
    split.Dato1,
    split.Dato2
FROM OPENROWSET(BULK 'C:\你的文件路径\data.txt', SINGLE_CLOB) AS f
CROSS APPLY STRING_SPLIT(f.BulkColumn, CHAR(10)) AS s
CROSS APPLY (
    SELECT 
        MAX(CASE WHEN ordinal = 1 THEN value END) AS Dato1,
        MAX(CASE WHEN ordinal = 2 THEN value END) AS Dato2
    FROM STRING_SPLIT(s.value, ';', 1) -- ordinal参数需SQL Server 2022+或Azure SQL支持
) AS split
WHERE LTRIM(RTRIM(s.value)) <> '' -- 过滤空行

方法2：先导入临时表再过滤空行 #

先用BULK INSERT导入所有行到临时表，再过滤空行后拆分数据：

-- 存储原始整行数据的临时表
CREATE TABLE #temp_raw (
    RawData VARCHAR(MAX)
)

-- 导入所有行（含空行）
BULK INSERT #temp_raw
FROM 'C:\你的文件路径\data.txt'
WITH (
    FIELDTERMINATOR = '\n',
    ROWTERMINATOR = '\n'
)

-- 创建目标临时表
CREATE TABLE #temp (
    Dato1 VARCHAR(100),
    Dato2 VARCHAR(100),
    -- 按需添加其他列
)

-- 过滤空行并拆分插入目标表
INSERT INTO #temp (Dato1, Dato2)
SELECT 
    MAX(CASE WHEN ordinal = 1 THEN value END) AS Dato1,
    MAX(CASE WHEN ordinal = 2 THEN value END) AS Dato2
FROM #temp_raw
CROSS APPLY STRING_SPLIT(RawData, ';', 1)
WHERE LTRIM(RTRIM(RawData)) <> ''
GROUP BY RawData

二、将单列数据拆分为多列的方法 #

方式1：使用带ordinal参数的STRING_SPLIT（SQL Server 2022+/Azure SQL） #

利用STRING_SPLIT的ordinal参数按顺序拆分列：

CREATE TABLE #temp_split (
    Dato1 VARCHAR(100),
    Dato2 VARCHAR(100),
    Dato3 VARCHAR(100)
)

INSERT INTO #temp_split
SELECT 
    MAX(CASE WHEN ordinal = 1 THEN value END) AS Dato1,
    MAX(CASE WHEN ordinal = 2 THEN value END) AS Dato2,
    MAX(CASE WHEN ordinal = 3 THEN value END) AS Dato3
FROM #temp_raw -- 已删除空行的单列临时表
CROSS APPLY STRING_SPLIT(RawData, ';', 1)
GROUP BY RawData

方式2：使用CHARINDEX和SUBSTRING（兼容旧版本SQL Server） #

针对SQL Server 2019及更早版本，用字符串截取方式拆分：

CREATE TABLE #temp_split (
    Dato1 VARCHAR(100),
    Dato2 VARCHAR(100),
    Dato3 VARCHAR(100)
)

INSERT INTO #temp_split
SELECT
    -- 截取第一列（第一个分号前的内容）
    LEFT(RawData, CHARINDEX(';', RawData) - 1) AS Dato1,
    -- 截取第二列（两个分号之间的内容）
    SUBSTRING(
        RawData,
        CHARINDEX(';', RawData) + 1,
        CHARINDEX(';', RawData, CHARINDEX(';', RawData) + 1) - CHARINDEX(';', RawData) - 1
    ) AS Dato2,
    -- 截取第三列（第二个分号后的内容）
    RIGHT(RawData, LEN(RawData) - CHARINDEX(';', RawData, CHARINDEX(';', RawData) + 1)) AS Dato3
FROM #temp_raw
WHERE LTRIM(RTRIM(RawData)) <> ''

注：若列数更多，需依次嵌套CHARINDEX定位每个分号位置，或编写自定义拆分函数。

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