question

MartySirkin-8064 avatar image
0 Votes"
MartySirkin-8064 asked ·

Yet another question about BCP failure

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.

azure-sql-databaseazure-batch
· 3
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thank you very much for the effort. I had tried this so many times I must have left the IDENTITY(1, 1) out of the create table statement. I have fixed that. Also, I haven't used BCP in about a decade. I didn't remember that the fields should be numbered 2, 3, 4 (since we are skipping the identity column.

I changed all of those things, and still have an issue (that I've seen on/off as I looked at this).

In fact, I used your files (for the bcp command - changing the UID, server...), the format file, the input file and the create table command.

I am seeing now (which I've seen many times today on/off as I was trying to debug) the following:


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


Any ideas? I REALLY appreciate the help. And I will search (again) this error...

0 Votes 0 ·

Hi @MartySirkin-8064, thanks for replying back.

This looks strange as I was trying similar command again and it worked. Only thing I noticed is extra spaces in the code I pasted.

I am attaching the file as it is. Could you please try by using these files directly once? If there are spaces at the start, please remove them.

bcp dbo.PRICE_ZONE_GROUP IN in.csv -f joe.fmt -r "\r\n" -e error.log -S <servername> -d <dbname> -U <username> -P <password> -c -r \n -t , -F 2 - This is the bcp command. Please replace the placeholders
[1][77353-in.txt][1] - Copy and please change the format to .csv
77287-joe.txt - Copy and please change the format to .fmt

Below is the result I got:
77324-image.png


[1]: /answers/storage/temp/77353-in.txt


0 Votes 0 ·
image.png (32.7 KiB)
in.txt (230 B)
joe.txt (180 B)

Thank you. That did the trick!! I copied all of the files so I can do a byte-by-byte comparison. Now, I should be able to see what the delta is/what was wrong.

Thank you SO very much!! I really appreciate it.

Thank you,
Marty

0 Votes 0 ·

1 Answer

AnuragSharma-MSFT avatar image
0 Votes"
AnuragSharma-MSFT answered ·

Hi @MartySirkin-8064, 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'





·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.