How to BCP IN to table with IDENTITY column

[JJA]

I have fixed length records (167 bytes) in a .txt file and want to load
this data exactly as is into a staging table where I hope to be able to
later get at selected columns using the SUBSTRING function. Here is my
target table:
CREATE TABLE JJA_BCP_NHO_DAT (
RecID int IDENTITY(1,1) NOT NULL
, Data VARCHAR(167) NOT NULL
)
Here is my bcp command:
bcp DevMDW.dbo.JJA_BCP_NHO_DAT in
N:\RawData_MDS\Purchase\UnzipArea\2005_08_Aug_0149 7407.txt -f bcp.fmt

Here is the .fmt file:
8.0
1
1 SQLCHAR 0 167 "" 2
Data SQL_Latin1_General_CP1_CI_AS
Here are the first 3 lines of data in the .txt file (note how 1st 2
bytes are blank; last 2 bytes of each record are 78, 79, 80):

02160168C013CMA20050819 328
UUU AGAWAM 36422005072901001 BERKSHIRE
BANK 25013 78
01940155C001 MA20050805 254
UUU AGAWAM 28072005071801001 WEBSTER
BANK 25013 79
02350188C014CMA20050729 067
UUU AGAWAM 24812005070701001 FIRST
PIONEER FARM CR 25013 80

The bcp command runs OK but the output is "shifted" in the column
called DATA in the table. The IDENTITY column looks good in all rows,
but only the first row is OK in the DATA column. Starting in the 2nd
row, all bytes are truncated or shifted by what appears to be 2
characters. This is hard to show with pasting results but I ran this
query:

SET ROWCOUNT 10
SELECT RecID
, SUBSTRING(Data,1,10) as FirstTen
, SUBSTRING(Data,158,10) as LastTen
, DATALENGTH(DATA) AS LEN_Data FROM JJA_BCP_NHO_DAT

1 02160168 25013 78 167
2 019401 25013 167
3 79 0235 CR 2501 167
4 3 80 01 CO 25 167
5 013 80 CORP 167
6 25013 80 REG SYS 167
7 25013 98 ANK 167
8 25013 VGS BK 167
9 2501 21 MTG CO 167
10 RP 25 RYWIDE HOM 167

Every line in the .txt file ends after column 167 with x'0d0a'. I've
tried a field terminator of \r\n and that produces this error:

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

Thanks in advance for some help on this.

[John Bell]

Hi

Try a format file such as
8.0
2
1 SQLINT 0 0 "" 1 RecID
""
2 SQLCHAR 0 167 "\r\n" 2 Data
Latin1_General_CI_AS

John

"JJA" <jo***@cbmiweb.com> wrote in message
news:11**********************@o13g2000cwo.googlegr oups.com...

I have fixed length records (167 bytes) in a .txt file and want to load
this data exactly as is into a staging table where I hope to be able to
later get at selected columns using the SUBSTRING function. Here is my
target table:
CREATE TABLE JJA_BCP_NHO_DAT (
RecID int IDENTITY(1,1) NOT NULL
, Data VARCHAR(167) NOT NULL
)
Here is my bcp command:
bcp DevMDW.dbo.JJA_BCP_NHO_DAT in
N:\RawData_MDS\Purchase\UnzipArea\2005_08_Aug_0149 7407.txt -f bcp.fmt

Here is the .fmt file:
8.0
1
1 SQLCHAR 0 167 "" 2
Data SQL_Latin1_General_CP1_CI_AS
Here are the first 3 lines of data in the .txt file (note how 1st 2
bytes are blank; last 2 bytes of each record are 78, 79, 80):

02160168C013CMA20050819 328
UUU AGAWAM 36422005072901001 BERKSHIRE
BANK 25013 78
01940155C001 MA20050805 254
UUU AGAWAM 28072005071801001 WEBSTER
BANK 25013 79
02350188C014CMA20050729 067
UUU AGAWAM 24812005070701001 FIRST
PIONEER FARM CR 25013 80

The bcp command runs OK but the output is "shifted" in the column
called DATA in the table. The IDENTITY column looks good in all rows,
but only the first row is OK in the DATA column. Starting in the 2nd
row, all bytes are truncated or shifted by what appears to be 2
characters. This is hard to show with pasting results but I ran this
query:

SET ROWCOUNT 10
SELECT RecID
, SUBSTRING(Data,1,10) as FirstTen
, SUBSTRING(Data,158,10) as LastTen
, DATALENGTH(DATA) AS LEN_Data FROM JJA_BCP_NHO_DAT

1 02160168 25013 78 167
2 019401 25013 167
3 79 0235 CR 2501 167
4 3 80 01 CO 25 167
5 013 80 CORP 167
6 25013 80 REG SYS 167
7 25013 98 ANK 167
8 25013 VGS BK 167
9 2501 21 MTG CO 167
10 RP 25 RYWIDE HOM 167

Every line in the .txt file ends after column 167 with x'0d0a'. I've
tried a field terminator of \r\n and that produces this error:

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

Thanks in advance for some help on this.

[JJA]

Thank you so much! I had not placed \r\n within double-quotes in my
previous attempts. Your suggestion worked great.