Using OPENROWSET to import CSV files

  • steve-893342

    SSCarpal Tunnel

    Points: 4439

    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