Bulk Inserting CSV into Azure SQL with formatting file error

Question

I'm trying to bulk insert data from a csv stored in a blob storage account in an Azure SQL database using a format file.

Here is what I am attempting to run:

BULK INSERT dbo.MyTable
FROM 'MyCSV.csv'
WITH(DATA_SOURCE = 'MyBlobStorageAccount'
, FIRSTROW = 2
, CODEPAGE = '65001'
 , FORMATFILE = 'MyFormatFile.xml'
);

I get the following error:

Cannot bulk load because the file "MyFormatFile.xml" could not be opened. Operating system error code (null).

Now, I can successfully load other files from the same storage account that do not use a format file and I can successfully load the contents of MyFormatFile.xml using the following, so its not a permissions / credentials issue:

IF(OBJECT_ID('tempdb..#data') IS NOT NULL)
    DROP TABLE #data

CREATE TABLE #data
(
    data VARCHAR (MAX)
)

BULK INSERT #data
FROM 'MyFormatFile.xml'
WITH (DATA_SOURCE = 'MyBlobStorageAccount',
      FIRSTROW = 1)

What am I doing wrong here? The Microsoft Documentation says that this is supported: Bulk Insert

Pratik Lad · Accepted Answer · 2023-02-03 12:32:56Z

1

As suggested in Microsoft Document there is no need to give file format when trading CSV file jus specify FORMAT = CSV

The issue is with your Format file you can simply insert data from csv fil without Format File.

If you still want to use Format File then please check the length of columns and column name that might causing the error.

Code Example:

BULK INSERT rest
FROM 'demo/mysamplecsv.csv'
WITH (DATA_SOURCE = 'MyAzurefile',
      FIRSTROW = 2,
      FORMAT = 'CSV');

Execution and Output: enter image description here

Reference: Examples of bulk access to data in Azure Blob Storage

also see this Answer by Joseph Xu.

answered Feb 3, 2023 at 12:32

Pratik Lad

8,7282 gold badges5 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Woody Over a year ago

Thanks for your response. I need the format file due to quirks in the CSV I'm loading. I also tested inserting the csv locally with the format file and it works successfully. The correct answer is actually in the second link you posted. Where it shows an additional parameter called FORMATFILE_DATA_SOURCE. This worked successfully.

Collectives™ on Stack Overflow

Bulk Inserting CSV into Azure SQL with formatting file error

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related