Blob Storage multiplle files

Question

question

RohitKulkarni-3496 asked · Mar 10 at 05:18 PM

Blob Storage multiplle files

Hello Team,

In a container there are 5 files are present.With the same column name.But the order of the column name are different among all the 5 files.

FolderName :

File1,
File2,
File3,
File4,
File5

When i run query in MS Sql server i am getting the error :
Msg 15813, Level 16, State 1, Line 1
Error handling external file: 'Max errors count reached.'. File/External table name: 'https://storagename.blob.core.windows.net/staging-zone/FolderName/Filename*.csv'.

Please advise how i can solve this issue

Regards
RK

sql-server-general azure-blob-storage

· 1

10 |1000 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog · Mar 10 at 10:43 PM

In which environment are you running this? You say SQL Server, but the format of OPENROWSET you use does not seem familiar to me. It looks as if you could be on Synapse.

Could you post the output of "SELECT @@version" so that we know where you are?

0 ·

Answer 1 · 2021-03-11T06:31:08.33Z

AmeliaGu-msft answered · Mar 11 at 06:31 AM

Hi @RohitKulkarni-3496,

Have you created a database scoped credential created for a shared access signature and created an external data source pointing to an Azure storage account before accessing data in a CSV file?
Please refer to bulk access to data in Azure Blob storage and Configure PolyBase to access external data in Azure Blob Storage which might help.
In addition, it seems there is something wrong with the OPENROWSET statement in your query. Please refer to OPENROWSET Syntax for more details.

Best Regards,
Amelia

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread

·

10 |1000 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Answer 2 · 2021-03-10T18:02:06.027Z

RohitKulkarni-3496 answered · Mar 10 at 06:02 PM

Just for example I am copying the query which i used in this format:

SELECT FROM
OPENROWSET(BULK 'https://storagename.blob.core.windows.net/staging-zone/FolderName/Filename.csv'
, FORMAT = 'csv' , Parser_Version= '2.0' , FIELDTERMINATOR = '|' , FIELDQUOTE='"' , ESCAPECHAR = '\') WITH
(
fieldName1 nvarchar(50)
fieldName2 nvarchar(20)
fieldName3 tinyint,
fieldName4 nvarchar(50),
fieldName5 int
) as r

·

10 |1000 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Answer 3 · 2021-03-10T18:39:35.31Z

RohitKulkarni-3496 answered · Mar 10 at 06:39 PM

I wanted to write the same like :

SELECT FROM
OPENROWSET(BULK 'https://storagename.blob.core.windows.net/staging-zone/FolderName/Filename.csv'
, FORMAT = 'csv' , Parser_Version= '2.0' , FIELDTERMINATOR = '|' , FIELDQUOTE='"' , ESCAPECHAR = '\') with
select * from tableName
) as r

But not able to find the correct syntax.Please advise.

·

10 |1000 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Answer 4 · 2021-03-11T11:05:22.173Z

RohitKulkarni-3496 answered · Mar 11 at 11:05 AM

The solution is got fixed after changing the data types for few of the columns.

Thanks

RK

·

10 |1000 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

question