Base64编码CSV文件数据迁移至SQL Server表的解析问题求助

前置说明 #

你解码得到的连续字符串本质是完整的CSV内容，展示时的空格实际是原始CSV的换行符，我们只需要先按换行符拆分出每一行记录，再按逗号拆分每行的字段即可映射为结构化表。

方案1：SQL Server 2022及以上版本（推荐） #

SQL Server 2022开始支持STRING_SPLIT的ordinal参数，可以保证字段拆分顺序，实现更简单：

WITH DecodedCSV AS (
    -- 解码Base64得到原始CSV文本，UTF8编码可加第三个参数65001适配
    SELECT 
        CONVERT(VARCHAR(MAX),
        CAST('' AS XML).value('xs:base64Binary(sql:column("FILE"))', 'VARBINARY(MAX)')) AS RawCSV
    FROM ABC
),
-- 按换行符拆分所有行，过滤空行
SplitRows AS (
    SELECT 
        value AS RowContent,
        ROW_NUMBER() OVER(ORDER BY (SELECT 1)) AS RowNum
    FROM DecodedCSV
    CROSS APPLY STRING_SPLIT(REPLACE(RawCSV, CHAR(13), ''), CHAR(10))
    WHERE value <> ''
)
-- 按列位置提取对应字段，跳过表头行
SELECT
    TRIM(MAX(CASE WHEN ordinal = 1 THEN d.value END)) AS Agentid,
    TRIM(MAX(CASE WHEN ordinal = 2 THEN d.value END)) AS agentname,
    TRIM(MAX(CASE WHEN ordinal = 3 THEN d.value END)) AS phone,
    TRIM(MAX(CASE WHEN ordinal = 4 THEN d.value END)) AS email,
    TRIM(MAX(CASE WHEN ordinal = 5 THEN d.value END)) AS address,
    TRIM(MAX(CASE WHEN ordinal = 6 THEN d.value END)) AS state,
    TRIM(MAX(CASE WHEN ordinal = 7 THEN d.value END)) AS country,
    TRIM(MAX(CASE WHEN ordinal = 8 THEN d.value END)) AS zip,
    TRIM(MAX(CASE WHEN ordinal = 9 THEN d.value END)) AS ssn
FROM SplitRows r
CROSS APPLY STRING_SPLIT(REPLACE(r.RowContent, ' ', ''), ',', 1) d
WHERE r.RowNum > 1
GROUP BY r.RowNum

方案2：兼容SQL Server 2016-2019版本 #

低版本SQL Server不支持ordinal参数，可以用XML拆分保证字段顺序：

WITH DecodedCSV AS (
    SELECT 
        CONVERT(VARCHAR(MAX),
        CAST('' AS XML).value('xs:base64Binary(sql:column("FILE"))', 'VARBINARY(MAX)')) AS RawCSV
    FROM ABC
),
-- 将CSV行转换为XML节点
SplitRowsXML AS (
    SELECT 
        CAST('<row>' + REPLACE(REPLACE(RawCSV, CHAR(13), ''), CHAR(10), '</row><row>') + '</row>' AS XML) AS RowsXML
    FROM DecodedCSV
),
-- 提取每一行内容
SplitRows AS (
    SELECT 
        n.value('.', 'VARCHAR(MAX)') AS RowContent,
        ROW_NUMBER() OVER(ORDER BY (SELECT 1)) AS RowNum
    FROM SplitRowsXML
    CROSS APPLY RowsXML.nodes('/row') AS t(n)
    WHERE n.value('.', 'VARCHAR(MAX)') <> ''
)
-- 拆分每行字段得到最终结果
SELECT
    LTRIM(RTRIM(ColXML.value('(/e)[1]', 'VARCHAR(100)'))) AS Agentid,
    LTRIM(RTRIM(ColXML.value('(/e)[2]', 'VARCHAR(100)'))) AS agentname,
    LTRIM(RTRIM(ColXML.value('(/e)[3]', 'VARCHAR(100)'))) AS phone,
    LTRIM(RTRIM(ColXML.value('(/e)[4]', 'VARCHAR(100)'))) AS email,
    LTRIM(RTRIM(ColXML.value('(/e)[5]', 'VARCHAR(100)'))) AS address,
    LTRIM(RTRIM(ColXML.value('(/e)[6]', 'VARCHAR(100)'))) AS state,
    LTRIM(RTRIM(ColXML.value('(/e)[7]', 'VARCHAR(100)'))) AS country,
    LTRIM(RTRIM(ColXML.value('(/e)[8]', 'VARCHAR(100)'))) AS zip,
    LTRIM(RTRIM(ColXML.value('(/e)[9]', 'VARCHAR(100)'))) AS ssn
FROM SplitRows r
CROSS APPLY (
    SELECT CAST('<e>' + REPLACE(REPLACE(RowContent, ' ', ''), ',', '</e><e>') + '</e>' AS XML) AS ColXML
) t
WHERE r.RowNum > 1

注意事项 #

• 如果CSV字段本身包含逗号，上述简单拆分方式会出现字段错位，这种情况更推荐直接在SSIS层完成Base64解码和CSV解析，SSIS自带的平面文件解析组件可以处理带引号包裹的含逗号字段，比SQL层处理更稳定。
• 若解码后出现乱码，可调整CONVERT函数的编码参数，比如CSV是UTF-8编码，SQL Server 2019及以上版本可以在CONVERT中加第三个参数65001指定UTF8编码。

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