Rows not importing through BCP

[Thomas R. Hummel]

Hello,

I am importing a file using BCP, with a format file. It is a
fixed-width file, which I am importing into a table that has a column
for each field in the file. The columns in my import table are all
defined as CHAR. I use it as a staging area to check the data (are
dates formatted correctly, etc.) before moving it from there.

In one of my files I have six records that contain strange characters
(ascii codes of 03 and 02 if I am remembering correctly). When I
import the file, these six records do not get imported. Not only that,
but if I use the error file option with BCP, nothing is put in the
error file either. As far as I can tell, it is just skipping right
over the rows. If I import the file into a table with a single column
that holds the entire row of data, all rows get imported.

I've included a sample of the format file below (there are 106 fields
in the real one, so I've abbreviated it for space). I've also included
a table definition as well.

Has anyone seen this behavior before? Any ideas?

Thanks and Happy New Year!
-Tom.
Table Definition:
-----------------------------------------------------------
CREATE TABLE [dbo].[BIF_Pre_Staging] (
[dda_num] [char] (18) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[account_app_id] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[cust_num] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CUST_ACT_OWNER_TYPE] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[do_not_mail] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[employee_code] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[ACCOUNT_TAX_NUM] [char] (9) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[CUS_PFS_FLAG] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SUB_TIER_VALUE] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

-----------------------------------------------------------
Sample Format File:
-----------------------------------------------------------
8.0
9
1 SQLCHAR 0 18 "" 1 dda_num ""
2 SQLCHAR 0 4 "" 2 account_app_id ""
3 SQLCHAR 0 10 "" 3 cust_num ""
4 SQLCHAR 0 1 "" 4 CUST_ACT_OWNER_TYPE ""
5 SQLCHAR 0 6 "" 5 do_not_mail ""
6 SQLCHAR 0 1 "" 6 employee_code ""
7 SQLCHAR 0 9 "" 7 ACCOUNT_TAX_NUM ""
8 SQLCHAR 0 1 "" 8 CUS_PFS_FLAG ""
9 SQLCHAR 0 5 "\r\n" 9 SUB_TIER_VALUE ""
-----------------------------------------------------------

[Erland Sommarskog]

Thomas R. Hummel (to********@hotmail.com) writes:

In one of my files I have six records that contain strange characters
(ascii codes of 03 and 02 if I am remembering correctly). When I
import the file, these six records do not get imported. Not only that,
but if I use the error file option with BCP, nothing is put in the
error file either. As far as I can tell, it is just skipping right
over the rows. If I import the file into a table with a single column
that holds the entire row of data, all rows get imported.

I've included a sample of the format file below (there are 106 fields
in the real one, so I've abbreviated it for space). I've also included
a table definition as well.

I tried your table and format file, with this data:

123456789012345678ABCD1234567890|abcdefX123456789/12345
%%%456789012345678ABCD1234567890@abcdefX123456789/12345
¤¤¤456789012345678ABCD1234567890\abcdefX123456789/12345
"""456789012345678ABCD1234567890?abcdefX123456 789/12345

This loaded fine. I then replaced the "d" on the last two lines with
ASCII 2 & 3. The file still loaded fine.

Could you try to achieve a complete repro?
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp