Bulk insert CSV file from Azure blob storage to SQL managed instance

Question

I have CSV file on Azure blob storage. It has 4 columns in it without headers and one blank row at starting. I am inserting CSV file into SQL managed instance by bulkinsert and I have 5 columns in the database table. I don't have 5th column in CSV file.

Therefore it is throwing this error:

Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 5 (uId2)

As I want to insert that 4 columns from CSV file to table in database and I want that 5th column in table as NULL.

I am using this code:

BULK INSERT testing
FROM 'test.csv'
WITH (DATA_SOURCE = 'BULKTEST',
  FIELDTERMINATOR = ',',
  FIRSTROW = 0,
  CODEPAGE = '65001',
  ROWTERMINATOR = '0x0a'
   );

Want that 5th row as NULL in database table, if there are 4 columns in CSV file.

Hi Dipesh Pawar, If my answer is helpful for you, please mark it as answer( click on the check mark beside the answer to toggle it from greyed out to filled in.). This can be beneficial to other community members. Thank you. — Leon Yue
– Leon Yue, Commented Mar 9, 2020 at 2:34

Leon Yue · Accepted Answer · 2020-03-06 06:02:39Z

0

Sorry, we achieve that in bulk insert. None of other ways according my experience.

Azure SQL managed instance is also not supported as dataset in Data Factory Data flow. Otherwise we can using Data Flow derived column to create a new column to mapping to the Azure SQL database.

The best way is that you editor your csv file: just add new column as header in you csv files.

Hope this helps.

answered Mar 6, 2020 at 6:02

Leon Yue

16.5k1 gold badge17 silver badges26 bronze badges

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

Collectives™ on Stack Overflow

Bulk insert CSV file from Azure blob storage to SQL managed instance

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related