Search code examples
t-sqlazure-sql-databaseazure-data-lake

How to open txt file from openrowset Function in Synapse analytics

I am writing this SQL query to fetch data from Delimitedtext file. But getting an error Invalid or unknown format type 'txt'.

select * from OPENROWSET
    (
        bulk 'https://demoaccname.dfs.core.windows.net/demoadlscontainer/simplenotebook.txt',
        format= 'txt'
    
    )as result
Solution

  • We can access the data of .txt file in synapse dedicated pool using below process. click on the linked in data and open the storage account and right click on the table will get bulk load option. Image for reference:

    enter image description here

    we will get below page

    enter image description here

    enter image description here

    Fill the required fields then code will generate automatically in sql. when we click on sql script. we will get code as mentioned below:

    enter image description here

    retrieve the data of the table I got the below result.

    enter image description here

    In this way we can access the data of .txt file in synapse dedicated file.