Importing Data into an Azure SQL Database with BCP

Importing data from a flat file into a Azure SQL Database is definitely not as easy as I thought it would be. After a bit of Googling, BCP seemed to be the easiest solution but the tool itself is very difficult to work with and the documentation, while plentiful, is not at all user friendly.

After a lot of frustration and failed attempts, I came to the conclusion that it's far easier to use the tool's defaults rather than trying to get BCP to understand your specific file format (even though the format I was trying to get it to understand was a bog standard CSV file).

And that format is Tab Separated columns with CRLF line endings.

Once the data is in the right format, run BCP from the command line with the following arguments:

bcp "[<my_database>].[dbo].[<my table>]" IN "my_data.tsv" -S"<server_name>" -U"<username>" -P"<password>" -c  

The "-c" above runs BCP in character mode rather than the default native (binary).

Be careful to leave the quotes and spacing as is as they can make the command fail without providing a meaningful reason why.

While the solution is simple, the journey certainly wasn't. Hopefully this post saves you (and future me) a little bit of time.

Author image
About Jon Leigh
London Website

Hi, I'm Jon. I'm part of the Engineering team at Moneybox, a London based Fintech startup helping people to save and invest.

In my spare time, I've created CompareVino, a UK supermarket wine comparison website. If you like wine and buy it from a supermarket, you should definitely check it out.

If you want to get in touch, send me a message via Twitter