reading .csv from blobstorage container

arkiboys 9,686 Reputation points
2021-09-17T11:55:11.333+00:00

Hello,
There is a file, test.csv in the container.
In the synapse studio, I right click on this file which is in the blobstorage, and select the top 100 and it generates the automated sql to execute to read the data inside it.
When the automated openrowset query is executed, it does show the data but the csv headers are in the second row and in the first row I see C1, C2, C3, etc.
Do you know how to remove those unwanted C1, C2, C3, etc. headings?
Thank you

Azure Blob Storage
Azure Blob Storage
An Azure service that stores unstructured data in the cloud as blobs.
2,558 questions
0 comments No comments
{count} votes

Accepted answer
  1. Thomas Boersma 806 Reputation points
    2021-09-17T12:15:45.193+00:00

    Hi @arkiboys ,

    You need to specify that you want to use the csv headers as columns. Here is an example:

    SELECT  
        *  
    FROM  
        OPENROWSET(  
            BULK 'https://{your-storage-account-name}.dfs.core.windows.net/data/test.csv',  
            FORMAT = 'CSV',  
            PARSER_VERSION='2.0',  
            HEADER_ROW = TRUE -- This the setting you want  
        ) AS [result]  
    

    See the docs for more info.

    Hope this helps.

    0 comments No comments

0 additional answers

Sort by: Most helpful