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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy