如何暂存Flat/TXT文件并复用格式转换代码实现目标结果

我现有一段通过手动插入数据实现格式转换的SQL代码，现在需要实现从Flat/TXT文件读取数据暂存，再复用这段代码得到与手动操作完全一致的转换结果。

原手动插入数据的示例代码 #

-- DDL and sample data population, start
DECLARE @tbl TABLE (Token VARCHAR(1024));
INSERT @tbl (Token) VALUES
('{1:F01SBZAZAJJXXXX9999999999}{2:I940SBICMWMXXXXXN}{4:'),
(':20:D424A100110011E4'),
(':25:020083203'),
(':28C:49/1'),
(':60F:C140106ZAR1029873,62'),
(':61:1401060106DR5000,NTRF99999999//NONREF20140106-13175-016050001844421'),
(':86:/PREF/ZA000520CATS THIRD PARTY PAYMENT'),
(':62F:C140106ZAR0,00'),
('-}'),
('{1:F01SBZAZAJJXXXX9999999999}{2:I940SBICMWMXXXXXN}{4:'),
(':20:D3DE7040110011E4'),
(':25:020083204'),
(':28C:51/1'),
(':60F:C140106NAD1030073,'),
(':61:1401060106DR5000,NTRF20140106-13175-0//NONREF20140106-13175-016050001844421'),
(':86:/PREF/NA000520TRANSFER'),
(':62F:C140106NAD0,00'),
('-}');
-- DDL and sample data population, end

DECLARE @group INT = (SELECT COUNT(*) FROM @tbl) / 9

;WITH rs AS
(
SELECT * 
    , _token = PARSENAME(REPLACE(token,':','.'),1)
    , seq = (ROW_NUMBER() OVER (ORDER BY (SELECT NULL))) % 9
    , grp = NTILE(@group) OVER (ORDER BY (SELECT NULL))
FROM @tbl
)
SELECT DISTINCT [20] = MAX(IIF(seq = 2, _token, '')) OVER (PARTITION BY grp)
    , [25] = MAX(IIF(seq = 3, _token, '')) OVER (PARTITION BY grp)
    , [28C] = MAX(IIF(seq = 4, _token, '')) OVER (PARTITION BY grp)
    , [60F] = MAX(IIF(seq = 5, _token, '')) OVER (PARTITION BY grp)
    , [61] = MAX(IIF(seq = 6, _token, '')) OVER (PARTITION BY grp)
    , [86] = MAX(IIF(seq = 7, _token, '')) OVER (PARTITION BY grp)
    , [62F] = MAX(IIF(seq = 8, _token, '')) OVER (PARTITION BY grp)
FROM rs;

预期转换结果 #

解决方案：读取Flat/TXT文件并复用转换逻辑 #

以下两种方法可以替代手动插入，直接从Flat/TXT文件读取数据，再复用你的转换代码：

方法1：使用BULK INSERT导入文件 #

假设你的Flat/TXT文件每行对应一条Token数据，路径为C:\data\mt940.txt，可以用BULK INSERT将数据导入表变量，再执行后续转换：

DECLARE @tbl TABLE (Token VARCHAR(1024));

-- 从TXT文件导入数据
BULK INSERT @tbl
FROM 'C:\data\mt940.txt'
WITH (
    ROWTERMINATOR = '\n', -- 根据文件实际换行符调整，可能是'\r\n'
    CODEPAGE = '65001' -- 若文件是UTF-8编码，需指定；否则可省略
);

-- 复用原转换逻辑
DECLARE @group INT = (SELECT COUNT(*) FROM @tbl) / 9

;WITH rs AS
(
SELECT * 
    , _token = PARSENAME(REPLACE(token,':','.'),1)
    , seq = (ROW_NUMBER() OVER (ORDER BY (SELECT NULL))) % 9
    , grp = NTILE(@group) OVER (ORDER BY (SELECT NULL))
FROM @tbl
)
SELECT DISTINCT [20] = MAX(IIF(seq = 2, _token, '')) OVER (PARTITION BY grp)
    , [25] = MAX(IIF(seq = 3, _token, '')) OVER (PARTITION BY grp)
    , [28C] = MAX(IIF(seq = 4, _token, '')) OVER (PARTITION BY grp)
    , [60F] = MAX(IIF(seq = 5, _token, '')) OVER (PARTITION BY grp)
    , [61] = MAX(IIF(seq = 6, _token, '')) OVER (PARTITION BY grp)
    , [86] = MAX(IIF(seq = 7, _token, '')) OVER (PARTITION BY grp)
    , [62F] = MAX(IIF(seq = 8, _token, '')) OVER (PARTITION BY grp)
FROM rs;

方法2：使用OPENROWSET读取文件 #

如果服务器配置允许，也可以用OPENROWSET直接读取文件并插入表变量：

DECLARE @tbl TABLE (Token VARCHAR(1024));

INSERT INTO @tbl (Token)
SELECT BulkColumn
FROM OPENROWSET(
    BULK 'C:\data\mt940.txt',
    FORMATFILE = 'C:\data\format.xml' -- 需要提前创建格式文件
) AS t;

-- 复用原转换逻辑（同方法1的后续代码）
DECLARE @group INT = (SELECT COUNT(*) FROM @tbl) / 9

;WITH rs AS
(
SELECT * 
    , _token = PARSENAME(REPLACE(token,':','.'),1)
    , seq = (ROW_NUMBER() OVER (ORDER BY (SELECT NULL))) % 9
    , grp = NTILE(@group) OVER (ORDER BY (SELECT NULL))
FROM @tbl
)
SELECT DISTINCT [20] = MAX(IIF(seq = 2, _token, '')) OVER (PARTITION BY grp)
    , [25] = MAX(IIF(seq = 3, _token, '')) OVER (PARTITION BY grp)
    , [28C] = MAX(IIF(seq = 4, _token, '')) OVER (PARTITION BY grp)
    , [60F] = MAX(IIF(seq = 5, _token, '')) OVER (PARTITION BY grp)
    , [61] = MAX(IIF(seq = 6, _token, '')) OVER (PARTITION BY grp)
    , [86] = MAX(IIF(seq = 7, _token, '')) OVER (PARTITION BY grp)
    , [62F] = MAX(IIF(seq = 8, _token, '')) OVER (PARTITION BY grp)
FROM rs;

注意事项 #

• 文件路径需是SQL Server服务账号可访问的路径，若使用远程文件需配置共享权限。
• 换行符需与文件实际匹配，常见的有\n（Linux）、\r\n（Windows）。
• 若文件包含特殊字符或非ASCII编码，需指定正确的CODEPAGE参数。
• 使用OPENROWSET时，需确保服务器已启用Ad Hoc Distributed Queries配置。

内容的提问来源于stack exchange，提问作者Jimrosy P Madzokere