April 27, 2010 at 4:54 pm
Out of curiosity I have come up with another variation using OPENROWSET BULK as the starting point
DECLARE @File VARCHAR(100)
SET @File = 'C:\Sample2.csv'
DECLARE @ROWSET NVARCHAR(255)
DECLARE @HeadersRAW VARCHAR(8000)
DECLARE @RowList VARCHAR(MAX)
DECLARE @ConvertHeaderList VARCHAR(MAX)
--First we need to extract the Headers into the variable @HeadersRAW
--We do this using OPENROWSET BULK to extract the first row using the unstructured format file BulkFile.fmt which has a single field called BulkColumn defined as SQLCHAR 8000
SET @ROWSET = N'SELECT @HeadersRAW = BulkColumn FROM OPENROWSET (BULK ''' + @File + ''', FORMATFILE = ''C:\BulkFile.fmt'', LASTROW = 1) AS X'
EXEC SP_EXECUTESQL @ROWSET, N'@HeadersRAW VARCHAR(8000) OUTPUT', @HeadersRAW OUTPUT
--This CTE Block factors the headers and the row list for the final query to work
--Here we split and flip the @HeadersRAW string into vertical data and then concatenate in the [1] AS [first name] syntax
--Then we use FOR XML PATH to concatenate into comma-separated horizontal row @ConvertHeaderList
--also check for blank headers in which case use ROW instead as column header
--eg [1] AS [first name],[2] AS [last name],[3] AS [class],[4] AS [tel1],[5] AS [5],[6] AS [6]
--At the same time we evaluate the row list eg [1],[2],[3],[4],[5],[6]
;
WITH cteHeadersConvert1 AS
(
SELECT @HeadersRAW AS HeadersRAW
)
,
cteHeadersConvert2 AS
(
SELECT * FROM cteHeadersConvert1
CROSS APPLY
(
SELECT
ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS ROW,
NULLIF(SUBSTRING(HeadersRAW + ',', N, CHARINDEX(',', HeadersRAW + ',', N) - N), '') AS Value
FROM master.dbo.Tally
WHERE N < LEN(HeadersRAW) + 2 AND SUBSTRING(',' + HeadersRAW + ',', N, 1) = ','
) AS Z
)
,
cteHeadersConvert3 AS
(
SELECT 0 AS Block, QUOTENAME(CONVERT(VARCHAR, ROW), ']') + ' AS ' + QUOTENAME(COALESCE(Value, CONVERT(VARCHAR, ROW)), ']') AS ConvertValue FROM cteHeadersConvert2
UNION ALL SELECT 1 AS Block, QUOTENAME(CONVERT(VARCHAR, ROW), ']') AS ConvertValue FROM cteHeadersConvert2
)
,
XMLHeadersConvert AS
(
SELECT DISTINCT Block, STUFF((SELECT ',' + ConvertValue FROM cteHeadersConvert3 AS t2 WHERE t2.Block = t1.Block FOR XML PATH('')), 1, 1, '') AS ConvertList
FROM cteHeadersConvert3 AS t1
)
,
cteCombined AS
(
SELECT (SELECT ConvertList FROM XMLHeadersConvert WHERE Block = 0) AS ConvertHeaderList, (SELECT ConvertList FROM XMLHeadersConvert WHERE Block = 1) AS RowList
)
SELECT @ConvertHeaderList = ConvertHeaderList, @RowList = RowList FROM cteCombined
--Now we have the variables we need to feed in to the dynamic SQL
--The data starting from row 2 is first imported using OPENROWSET BULK into an unstructured raw column and split vertically using CROSS APPLY/Tally split method
--Finally it is reassembled horizontally using PIVOT
EXEC(
'
;
WITH
cteFileData AS
(
SELECT
BulkColumn FROM OPENROWSET (BULK ''' + @File + ''', FORMATFILE = ''C:\BulkFile.fmt'', FIRSTROW = 2) AS X
)
,
cteData AS
(
SELECT ' + @ConvertHeaderList + ' FROM cteFileData
CROSS APPLY
(
SELECT ' + @RowList + ' FROM
(
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS ROW,
NULLIF(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
(
' + @RowList + '
)
)
AS pvt
)
AS Y
)
SELECT * FROM cteData
'
)
Viewing post 31 (of 31 total)
You must be logged in to reply to this topic. Login to reply