471,075 Members | 1,273 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,075 software developers and data experts.

Rows not importing through BCP

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 ""
-----------------------------------------------------------
Jul 20 '05 #1
1 4417
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
Jul 20 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

5 posts views Thread by TonyB | last post: by
3 posts views Thread by Stephen Matthews | last post: by
10 posts views Thread by el__marcin | last post: by
reply views Thread by leo001 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.