BCP shift values in SQL Server with ID primary key

Question

I'm using bcp to import a .csv file into a table in SQL Server like this:

bcp test1 in "./file.csv" -S server_name -U login_id -P password -d database_name -c -t

I get this message/warning:

Unexpected EOF encountered in BCP data-file.
bcp copy in failed

file.csv data:

A, B, C
A, B, C
A, B, C
A, B, C

My tables:

CREATE TABLE test2
(
    a VARCHAR(8) PRIMARY KEY,
    b VARCHAR(8),
    c VARCHAR(8)
);

CREATE TABLE test1 
(
    ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
    a VARCHAR(8),
    b VARCHAR(8),
    c VARCHAR(8)

    FOREIGN KEY (a) REFERENCES test2(a)
);

Here is what I get in my SELECT * FROM test1;:

ID | a | b |   c   |
1    B   CA B    C
A    B   CA B    C

Here is what I expected:

ID|a |b |c
1  A  B  C
2  A  B  C
3  A  B  C
3  A  B  C

I'm having no issues with test2 which is nearly like test but without ID. So the csv files are well formatted. So why do I have a shift like this?

EDIT 1

If I add header to csv fileI have this in my SELECT * FROM test1:

ID|a|b|c                        |
1  b cA B,C,A,B,C,A,B,C,A,B,C

EDIT 2

I generated a File Format to guide my data.

13.0
3
1    SQLCHQR    0    40    "\t"    1    a    SQL_Latin1_General_CP1_CI_AS
2    SQLCHQR    0    40    "\t"    2    a    SQL_Latin1_General_CP1_CI_AS
3    SQLCHQR    0    40    "\t"    3    a    SQL_Latin1_General_CP1_CI_AS

The modified one to try to "jump" over ID

13.0
4
1    SQLCHQR    0    40    "\t"    2    a    SQL_Latin1_General_CP1_CI_AS
2    SQLCHQR    0    40    "\t"    3    a    SQL_Latin1_General_CP1_CI_AS
3    SQLCHQR    0    40    "\t"    4    a    SQL_Latin1_General_CP1_CI_AS

But I can't manage to make it work.

bcp test1 in "./file.csv" -S server_name -U login_id -P password -d database_name -f file_name -t

SQLState = S1002, NativeError = 0 Error = [Microsoft][ODBC SQL Server Driver]Invalid Descriptor Index

EDIT 3

I found a way around the problem, but it's still not the good solution. What I did is: Change my test1 table, putting ID column at the end. Then adding with sed double commas at the end of each lines in my CSV to create a new empty column. Then I made a simple bcp in. I still want keep ID in first column, I just don't want to create an extra View for putting ID in front.

Answer 1

First Edit due to inverse meaning for the -E switch

You are missing missing the -E for the BCP to know that you are trying to use Identity column.

If you use this switch then the values are taken from the file and the SQL Server Identity value is ignored. You should preform DBCC CHECKIDENT('table_name', RESEED) afterwards.

Second Edit due to the more specific user whishes.

After edit @BeGreen specified that he wants to use format file. I'll post then the example how to do it. No need for the view in front which I saw in other post on Stack overflow: What will be BCP format for inserting a identity column.

The examples are being inconsistent in that CSV is mixing "," (in the example) and "\t" (in the format table). I'll be using only "," in the examples.

That is possible but you have to use correct approach.

First you have to generate the format file by this command:

bcp <db_name>.dbo.test1 format nul -c -f "generated_format_file.fmt" -t "," -S "<server>\<instance>" -T

Then you take generated .fmt file in this case (using SQL Server 2005 - as I have quick testing instance there - you can change the first number to fit your needs e.g. 13.0):

8.0
4
1       SQLCHAR       0       12      ","                       1     ID     ""
2       SQLCHAR       0       8       ","                       2     a      SQL_Latin1_General_CP1_CI_AS
3       SQLCHAR       0       8       ","                       3     b      SQL_Latin1_General_CP1_CI_AS
4       SQLCHAR       0       8       "\r\n"                    4     c      SQL_Latin1_General_CP1_CI_AS

The *.csv file looks (I have added the ID for tests and different from the Identity values that should be inserted into the table):

10,A1,B1,C1
20,A2,B2,C2
30,A3,B3,C3
40,A4,B4,C4

Then the correct command for importing (note: using trusted connection -T instead of -U -P to make the line shorter):

bcp <db_name>.<schema>.test1 IN "import_data.bcp" -f "generated_format_file.fmt" -t "," -S "<server>\<instance>" -T

Starting copy...

4 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total     : 218    Average : (18.35 rows per sec.)

Data is imported as (test1):

ID  a   b   c
1   A1  B1  C1
2   A2  B2  C2
3   A3  B3  C3
4   A4  B4  C4

To check identity value that is currently at test1:

select IDENT_CURRENT('[database_name].[dbo].[test1]')

Result:

(No column name)
4