Set Statistics TIME on

;
WITH cteFileData AS
(
SELECT BulkColumn FROM OPENROWSET (BULK 'C:\DataSample.txt', FORMATFILE = 'C:\DataSampleBulk.fmt') AS X
)
,
cteData AS
(
SELECT [1],[2],[3],[4],[5],[6],[7],[8] FROM cteFileData
CROSS APPLY
(
SELECT [1],[2],[3],[4],[5],[6],[7],[8] FROM 
(
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS ROW, 
SUBSTRING(BulkColumn + ',', N, CHARINDEX(',', BulkColumn + ',', N) - N) AS Value
FROM master.dbo.Tally
WHERE N < LEN(BulkColumn) + 2 AND SUBSTRING(',' + BulkColumn + ',', N, 1) = ','
) AS Z
PIVOT
(
MAX(Value) FOR ROW IN 
(
[1],[2],[3],[4],[5],[6],[7],[8]
)
)
AS pvt
)
AS Y
)
SELECT 
CONVERT(VARCHAR(20), [1]) AS COL1,
CONVERT(DATETIME, [2]) AS COL2,
CONVERT(DECIMAL(9,2), [3]) AS COL3,
CONVERT(DECIMAL(9,2), [4]) AS COL4,
CONVERT(DECIMAL(9,2), [5]) AS COL5,
CONVERT(DECIMAL(9,2), [6]) AS COL6,
CONVERT(BIGINT, [7]) AS COL7,
CONVERT(DECIMAL(9,2), [8]) AS COL8
FROM cteData