Flat File Staging For Format Conversion:

Question

I have a code the converts data from one format to another by inserting the data manually. I need assistance on how to stage the flat/txt file and apply the same code to come up with same results.

DBFiddle https://dbfiddle.uk/cwfvntlt

-- 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;

Expected results