Azure Synapse reading csv file

[aggiedan97]

Referring to Youtube video (https://www.youtube.com/watch?v=vOSzw40TohM) and attempting to utilze a csv file uploaded to Azure Blob/Gen2 datalake, and execute a SQL Script against a CSV file. Attempted many combinations to query but only returns the filename.

base query

SELECT

    TOP 100 *

FROM

    OPENROWSET(

        BULK 'https://example.dfs.core.windows.net/exampledatalakeg2/.../filename_example.csv',

        FORMAT = 'CSV',

        FIELDTERMINATOR = ';',

        ROWTERMINATOR = '',

        PARSER_VERSION='2.0'

    ) AS [result]

Tried defining all the columns, data types, and data row start which should return 6 columns with 100k rows of data.

[gdarakji]

You can try to directly connect to this csv file from within Power BI. Open Power BI -> Get Data -> Azure -> Azure Data Lake Storage Gen 2 -> Put the URL of the CSV and then sign in. This should be the easiest and most straight forward way.

[v-lionel-msft]

Hi @aggiedan97 ,

OPENROWSET (Transact-SQL) - SQL Server | Microsoft Docs

Please refer to the link.

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.