topic Re: Azure Synapse reading csv file in Developer

Azure Synapse reading csv file

Anonymous — Tue, 11 May 2021 20:08:03 GMT

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.

Re: Azure Synapse reading csv file

v-lionel-msft — Fri, 14 May 2021 08:00:07 GMT

Hi @Anonymous ,

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.

Re: Azure Synapse reading csv file

Anonymous — Sun, 16 May 2021 15:58:10 GMT

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.