MS SQL Server - bulk copy program utility (bcp)

About

bcp is a command-line bulk load utility that allows you to copy data between SQL Server, data files, and SQL Data Warehouse

bcp.exe does not support the UTF-8 file encoding.
Use ASCII files or UTF-16 encoded files.

Example

Installation

CSV

Import

https://docs.microsoft.com/en-us/azure/sql-database/sql-database-load-from-csv-with-bcp

  • the file is in UTF-16 or ASCII
1,AFA,Afghani
2,DZD,Algerian Dinar
3,ARS,Argentine Peso
4,AMD,Armenian Dram
5,AWG,Aruban Guilder
6,AUD,Australian Dollar
7,AZM,Azerbaijanian Manat
8,BSD,Bahamian Dollar
9,BHD,Bahraini Dinar
10,THB,Baht

The command:

bcp schema.Table in "/Samples/AdventureWorksDW/DimCurrency.csv" \
    -S db.database.windows.net \
    -d databaseName \ 
    -U userName \
    -P pwd  \
    -t , `# The field separator ` \
    -c  `# Insert as character and doesn't ask the data type`  \ 
    -q `# With quote -- seems to be mandatory` 

Result

Starting copy...

105 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total     : 813    Average : (129.15 rows per sec.)

Export

bcp "SELECT * FROM myTable" queryout MyTable.csv -t , -c -S serverName -d databaseName -T
bcp schema.myTable out -t , -c -S serverName -d databaseName -T

# With code page
bcp.exe MyTable out "D:\data.csv" -T -c -C 65001 -t , ... 

# Unicode
bcp TestDatabase.dbo.myWidechar OUT D:\BCP\myWidechar.bcp -T -w

# Don't ask all into character
bcp "SELECT * FROM table" queryout C:\tmp\file.csv -c

Support

Unexpected EOF encountered in BCP data-file

You may got this error:

Starting copy...
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]Unexpected EOF encountered in BCP data-file

0 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total     : 1

Two possible resolutions, open your file and:

  • save it as ASCII or UTF-16 <note important>Niet UTF-8</note>
  • verify that you have an empty line at the end

Documentation / Reference


Powered by ComboStrap