Issues regarding headers from CSV

Question

I am attempting to read a CSV file from an Azure Synapse Serverless SQL Pool, but I am encountering issues with correctly reading the headers from the CSV. This problem persists across multiple attempts. The structure of the CSV is as follows:

cssCopy codetest_col1,test_col2A,B

Attempt 1:

sqlCopy codeSELECT TOP 100 *FROM OPENROWSET(

    BULK 'https://xxxx.dfs.core.windows.net/xx/xx/Test.csv',

    FORMAT = 'CSV',

    PARSER_VERSION = '2.0'

) AS [result]

Result: The table erroneously generated C1 & C2 as field names.

Attempt 2:

sqlCopy codeSELECT 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]

Result: The table again erroneously generated C1 & C2 as field names and removed the row containing the actual headers.

Attempt 3:

sqlCopy codeSELECT 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]

Result: Similar to attempt 2, the table produced C1 & C2 as field names.

Do you have any suggestions for resolving this issue?

Answer 1

Hi @ClarissaJacquline ,

Welcome to Microsoft Q&A Platform. Thank you for posting your query.

Based on the information you provided, it seems that the issue you are encountering is related to reading the headers from a CSV file in an Azure Synapse Serverless SQL Pool.

To resolve this issue, I would suggest modifying your SQL query to include the correct delimiter and quote character for the CSV file. You can also use the HEADER option in the FORMAT parameter to specify that the first row of the CSV file contains the headers. Additionally, you can use the FIELDQUOTE option in the FORMAT parameter to specify the quote character used in the CSV file, and the FIELDTERMINATOR option to specify the delimiter used in the CSV file.

You need to declare that the file has a header row, as shown below, and this query should correctly read the headers from the CSV file.

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

Hope this helps. Do let us know if you any further queries.

---

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.