BCP unexpected eof encountered in bcp data-file

Question

Trying to import csv file for below table. However getting the error unexpected eof encountered in bcp data-file.

below is the table

create table temp.test
( id int ,
createid int,
source varchar(250))

bcp datbasename.temp.test IN C:\Users\testfile.csv -S servername -U username@servername -P XXXXX -c -r \n -t \t

Below is the data in the csv file.

id createid source
1 2010 A
2 2011 B

Answer 1

To be picky, that is not a CSV file as it is tab-separated. Or at least you say so in the BCP command.

Since the file has a header, you need the option -F 2 to skip the header row. However, that is your not problem for the moment, because in that case you would have gotten a different error message. (A conversion error.)

It Tom Phillips's suggestion about line-endings does not help you, we need to see a sample the which demonstrates the problem. BCP is a binary tool, so we need to see a file, so that we see the exact bytes.

By the way, speaking of line-endings, you have specified -r\n, but to confuse that actually means \r\n. If the line separator is \n only, you need to say 0x0a.

Answer 2

Please try the following approach.
It is based on BULK INSERT statement.

Second statement below is designed by Microsoft for *.csv files.

SQL

BULK INSERT temp.test  
FROM 'C:\Users\testfile.csv'  
WITH (  
   DATAFILETYPE = 'char' -- { 'char' | 'native' | 'widechar' | 'widenative' }   
   , FIELDTERMINATOR = ','  
   , ROWTERMINATOR = '\n'  
   , FIRSTROW = 2   
   , LASTROW = 3 -- Specifies the number of the last row to load. The default is 0, which indicates the last row in the specified data file.  
   , CODEPAGE = '65001');  
  
BULK INSERT temp.test  
FROM 'C:\Users\testfile.csv'  
WITH (FORMAT='CSV' -- starting from SQL Server 2017 onwards  
   , FIRSTROW = 2  
   --, FIELDQUOTE = '"'  
   , FIELDTERMINATOR = ','  
   , ROWTERMINATOR = '\n');  

EDIT
Back to the bcp.exe approach.
Load your file in the notepad++ editor.
Turn on View => Show Symbol => Show All Characters
and make sure that you see its layout like below:

Answer 3

Bulk insert does not work for Azure PAAS

Answer 4

What you describe is almost always due to an invalid end of row. Likely your end of row is \r, not \n.

Answer 5

view

Not able to upload csv file so shared through link

https://drive.google.com/file/d/1z95hTEHcrZ0IoTsBbJDddKTOXVSGUDHT/view?usp=sharing