Unexpected EOF encountered in BCP data-file

Question

I was trying copy data from a table to another table in another database using bcp.

First a format file was created using

!! bcp dbName1.dbo.tableName1 format nul  -S serverName1 -T -f D:\tableName1_fmt.txt -n

Then the data file was created using

!! bcp dbName1.dbo.tableName1 out D:\tableName1.txt  -S  serverName1 -T -c

Now I tried to import the data-file to another table in another database present in another server using the format file

!! bcp dbName2.dbo.tableName2 in D:\tableName1.txt -f D:\tableName1_fmt.txt  -S ServerName2 -T -E 

Then the following error was generated

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    

I figured out the problem. But it was a different case from the existing ones in Stackoverflow.

So I am writing my solution in case someone encounter with the same may benefit.

Answer 1

The answer to this puzzle is insidious. I spent time that I can never get back...

If you're in Windows, use NotePad++ and under "Encoding" on the menu, change it to [UCS-2 LE BOM] and give it a try. LE = Little Endian...

Such a hateful error! And I just installed SQL Server 2019 and the latest SQLCMD/BCP tools. Seems this error has been around for a while.

This guy saved my life: https://shades-of-orange.com/post/Unexpected-EOF-encountered-in-BCP-data-file

This was the line I used in the end

 bcp my_schema.my_table IN /home/user/data/data.csv -S "127.0.0.1" -d "my-database" -U "sa" -P "myPassWord" -t "," -r "\n" -c -F 2

Answer 2

The problem was with format specifiers (-c, -n) while creating Format-file and Data-file.

The format specifier for Format File (-n) and Data File (-c) was different.

When I changed format specifier of both to either -c or -n, the import statement worked.

Since retaining datatype of columns was important for my purpose, -n was used for format file and data file

Answer 3

Under my experience, this problem is caused by an empty line at the end of the file. It is recommended to use

tail your_file | od -c

with that, you'll see the last line and the format of line termination, sometimes dos2unix is needed

dos2unix your_file

Cheers!