Azure Synapse SQL pool not identifying csv headers using OPENROWSET

Question

I am trying to read a csv from an Azure Synapse Serverless SQL Pool, but it is not able to read the headers of the csv properly, and that in none of the following attempts:

The csv is:

test_col1,test_col2
A,B

Attempt 1:

SELECT TOP 100 *
FROM OPENROWSET(
    BULK 'https://xxxx.dfs.core.windows.net/xx/xx/Test.csv',
    FORMAT = 'CSV',
    PARSER_VERSION = '2.0'
) AS [result]

It yielded a table which erroneously generated C1 & C2 as field names:

C1,C2
test_col1,test_col2
A,B

Attempt 2:

SELECT TOP 100 *
FROM OPENROWSET(
    BULK 'https://xxxx.dfs.core.windows.net/xx/xx/Test.csv',
    FORMAT = 'CSV',
    PARSER_VERSION = '2.0',
    FIRSTROW = 2,
    FIELDQUOTE = '"',
    FIELDTERMINATOR = ','
) AS [result]

It yielded a table which erroneously generated C1 & C2 as field names, and removed the row of the csv containing the actual headers:

C1,C2
A,B

Attempt 3:

SELECT TOP 100 *
FROM OPENROWSET(
    BULK 'https://xxxx.dfs.core.windows.net/xx/xx/Test.csv',
    FORMAT = 'CSV',
    PARSER_VERSION = '2.0',
    FIRSTROW = 2
) AS [result]

Same result as attempt 2:

C1,C2
A,B

Any suggestion?

Answer 1

You need to declare that the file has a header row:

SELECT TOP 100 *
FROM OPENROWSET(
    BULK 'https://xxxx.dfs.core.windows.net/xx/xx/Test.csv',
    FORMAT = 'CSV',
    PARSER_VERSION = '2.0',
    HEADER_ROW = TRUE
) AS [result]