Yet another question about BCP failure

Question

I am sorry, but I have a BCP issue. Reading various forums, I have tried a large number of things, but still I cannot seem to be able to figure out why this is failing. I am sorry, but I appreciate if any of you might be able to see what I am missing. I am betting it's pretty simple. So, I have a simple table:
create table dbo.PRICE_ZONE_GROUP (Id INT NOT NULL PRIMARY KEY,
ZoneGroup INT NOT NULL,
PricingLevel nVarchar(10) NOT NULL,
Description nVarchar(200) NOT NULL)

I have called on bcp with a number of command lines such as: bcp dbo.PRICE_ZONE_GROUP IN in.csv -f joe.fmt -r "\r\n" -e error.log -S <server> -d <db> -U <userName> -P <Password>

The format file is:
14.0
3
1 SQLINT 0 4 "," 1 ZoneGroup ""
2 SQLNCHAR 2 20 "," 2 PricingLevel SQL_Latin1_General_CP1_CI_AS
3 SQLNCHAR 2 400 "\r\r\n" 3 Description SQL_Latin1_General_CP1_CI_AS.

(I am skipping the first (Id) field for the import. They should autonumber.

Here is a small sample of the input file:
9990,Z,FDS CORPORATE
4000,Z,H2 PRICING
4440,S,Store Pricing
7770,Z,ALL OTHER DAIRY
5801,Z,Dummy for training
5550,Z,AVAILABLE - 3 ZONES
7772,Z,BAKERY
8000,Z,MILK AND EGG PRICING

Also - I have used Notepad++ on each of the files. There were missing <CR><LF> at the end of each row (in all the files). But they have them now.

When I run the import I see:

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

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

I cannot, for the life of me, see what I am missing. But I'm sure it's pretty simple. Anyone have an idea what I've missed???

Thank you in advance.

Answer 1

Hi @Marty Sirkin , welcome to Microsoft Q&A forum.

I tried using the same scripts provided by you and was able to reproduce the issue. There are couple of issues that we noticed here:

1. The input file does not have the 'id' column. We know that it is something autogenerated identity but still we need to pass it in the file. It could be any static value.
2. The table script provided does not have primary key as identity column.

Please go through below scripts and try to run it:

Create Table Script

create table dbo.PRICE_ZONE_GROUP (Id int Primary key IDENTITY(1,1) NOT NULL,  
ZoneGroup INT NOT NULL,  
PricingLevel nVarchar(10) NOT NULL,  
Description nVarchar(200) NOT NULL)  

Format file I just made one change, added the column order as well correctly Please refer to below format file:

14.0  
3  
1 SQLINT 0 4 "," 2 ZoneGroup ""  
2 SQLNCHAR 2 20 "," 3 PricingLevel SQL_Latin1_General_CP1_CI_AS  
3 SQLNCHAR 2 400 "\r\r\n" 4 Description SQL_Latin1_General_CP1_CI_AS  

Source file should have all the columns as mentioned above, you can ignore the mapping in format file. Added column header as well:

col1,col2,col3,col4  
1,9990,Z,FDS CORPORATE  
1,4000,Z,H2 PRICING  
1,4440,S,Store Pricing  
1,7770,Z,ALL OTHER DAIRY  
1,5801,Z,Dummy for training  
1,5550,Z,AVAILABLE - 3 ZONES  
1,7772,Z,BAKERY  
1,8000,Z,MILK AND EGG PRICING  

Below is the bcp command used. F is added to ignore the password.

bcp dbo.PRICE_ZONE_GROUP IN in.csv -f joe.fmt -r "\r\n" -e error.log -S <server> -d <database> -U <userid> -P <password> -c -r \n -t , -F 2  

Note: As we are ignoring the 'id' column in format file, column will have auto incremented values in table.

Please let me know if this works or else we can discuss further.

----------

If answer helps, please mark it as 'Accept Answer'