CODEWITHSUNDEEP
azure-sql-databaseazure-blob-storageopenrowset
Sara
Sara

Reputation: 9

how to set bulk_options in openrowset to read file from Azure blob storage?

I have an Azure SQL database and would like to read a csv file from Azure blob storage (gen 2) with openrowset. However, the output lacks proper formatting, as rows and columns are not separated.

In the csv file, FIELD TERMINATOR is '|' and ROW TERMINATOR is '0x0a'. I've used openrowset to read the file, my code is as follows:

SELECT *
FROM 
OPENROWSET 
( 
    BULK '/myPath/myFile.csv'
    ,DATA_SOURCE = 'myDataSource'
    ,SINGLE_CLOB    
)  
AS fct

the problem is the result is in only one field and when I use

,FIELDTERMINATOR ='|'
,ROWTERMINATOR ='0x0a'

I get the error:

Incorrect syntax near 'FIELDTERMINATOR'.

How can I read the file in table format. I tried to create the format file based on a table that is similar to csv file in landing zone, when I run the following command in windows cmd in my client: bcp myAzureDB.mySchema.myTable format nul -f "https://myBlobStorage/myPath/filename.fmt" -S HPESTAR -c -T. I got this error" A valid table name is required for in, out, or format options. That I'm sure the table name is correct.

Upvotes: -1

Views: 1383

Answers (2)

Sara
Sara

Reputation: 9

The bcp command did not work for me due to security policies in our cloud environment. The login failed.

bcp <database_name>.<schema_name>.<table_name> format nul -f <output_file_name> -c -S <server_name>.database.windows.net -U <username> -P <password>

So, I copied the table into my local SQL server and generated a formatfile based on the local database. I could, however, make the formatfile manually.

Upvotes: 0

Pratik Lad
Pratik Lad

Reputation: 8402

Try following command in Your command prompt to get format file.

bcp <database_name>.<schema_name>.<table_name> format nul -f <output_file_name> -c -S <server_name>.database.windows.net -U <username> -P <password>

enter image description here

It will get you file downloaded locally from Azure SQL Database.

enter image description here

then upload this file to blob storage in similar container and update the terminators as your requirement.

enter image description here

Code to read CSV file:

SELECT * FROM OPENROWSET(
   BULK 'fsn2p/Book1.csv',
   DATA_SOURCE = 'MyAzureInvoices',
   FORMAT = 'CSV',
   FORMATFILE='fsn2p/format.fmt',
   FORMATFILE_DATA_SOURCE = 'MyAzureInvoices'
   ) AS DataFile;

Output

enter image description here

Upvotes: 0

Related Questions