Solved

BCP and BULK INSERT ignoring incomplete records instead of throwing an error

Posted on 2014-02-13
3
572 Views
Last Modified: 2014-02-19
I am attempting to insert a file utilizing either bulk insert or bcp. The file is a fixed width file that has rows with far fewer columns (positions) than the format file specifies. When executing either statement, these rows are excluded, but the process does not error out.

The file contains 42 records. Three records are incomplete (Field lengths are only several hundred positions out of a possible 6000 positions). I am expecting all 42 records to be attempted, and ultimately, expect the process to error out due to the 3 incomplete records. Any help or insight would be greatly appreciated. Thanks.

The following queries execute with the following message: '(39 row(s) affected)'

BULK INSERT:

BULK INSERT ##tmp_table
FROM '\\cf\files\file_1_20131223.txt' 
WITH (FIRSTROW = 1, MAXERRORS = 0, FORMATFILE = '\\cf\bcpFiles\file_1.fmt')

Open in new window


BCP:

DECLARE @cmdLoad VARCHAR(2000)

SET @cmdLoad = 'bcp ##tmp_table IN "\\cf\files\file_1_20131223.txt" -F 1' 
+ ' -f "\\cf\bcpFiles\file_1.fmt -m 0 -T -S cf_db'

EXEC master..xp_cmdshell @cmdLoad

Open in new window


The format file looks like:

10.0
123
1         SQLCHAR       0    1        ""     1       Field1              SQL_Latin1_General_CP1_CI_AS
2         SQLCHAR       0    20       ""     2       Field2              SQL_Latin1_General_CP1_CI_AS
3         SQLCHAR       0    20       ""     3       Field3              SQL_Latin1_General_CP1_CI_AS
4         SQLCHAR       0    2        ""     4       Field4              SQL_Latin1_General_CP1_CI_AS
5         SQLCHAR       0    8        ""     5       Field5              SQL_Latin1_General_CP1_CI_AS
6         SQLCHAR       0    8        ""     6       Field6              SQL_Latin1_General_CP1_CI_AS
7         SQLCHAR       0    15       ""     7       Field7              SQL_Latin1_General_CP1_CI_AS
8         SQLCHAR       0    15       ""     8       Field8              SQL_Latin1_General_CP1_CI_AS
9         SQLCHAR       0    1        ""     9       Field9              SQL_Latin1_General_CP1_CI_AS
10        SQLCHAR       0    1        ""     10      Field10             SQL_Latin1_General_CP1_CI_AS
11        SQLCHAR       0    8        ""     11      Field11             SQL_Latin1_General_CP1_CI_AS
12        SQLCHAR       0    9        ""     12      Field12             SQL_Latin1_General_CP1_CI_AS
13        SQLCHAR       0    4        ""     13      Field13             SQL_Latin1_General_CP1_CI_AS
14        SQLCHAR       0    48       ""     14      Field14             SQL_Latin1_General_CP1_CI_AS
15        SQLCHAR       0    320      ""     15      Field15             SQL_Latin1_General_CP1_CI_AS
16        SQLCHAR       0    8        ""     16      Field16             SQL_Latin1_General_CP1_CI_AS
17        SQLCHAR       0    1        ""     17      Field17             SQL_Latin1_General_CP1_CI_AS
18        SQLCHAR       0    1        ""     18      Field18             SQL_Latin1_General_CP1_CI_AS
19        SQLCHAR       0    1        ""     19      Field19             SQL_Latin1_General_CP1_CI_AS
20        SQLCHAR       0    1        ""     20      Field20             SQL_Latin1_General_CP1_CI_AS
21        SQLCHAR       0    1        ""     21      Field21             SQL_Latin1_General_CP1_CI_AS
22        SQLCHAR       0    8        ""     22      Field22             SQL_Latin1_General_CP1_CI_AS
23        SQLCHAR       0    8        ""     23      Field23             SQL_Latin1_General_CP1_CI_AS
24        SQLCHAR       0    24       ""     24      Field24             SQL_Latin1_General_CP1_CI_AS
25        SQLCHAR       0    24       ""     25      Field25             SQL_Latin1_General_CP1_CI_AS
26        SQLCHAR       0    18       ""     26      Field26             SQL_Latin1_General_CP1_CI_AS
27        SQLCHAR       0    24       ""     27      Field27             SQL_Latin1_General_CP1_CI_AS
28        SQLCHAR       0    14       ""     28      Field28             SQL_Latin1_General_CP1_CI_AS
29        SQLCHAR       0    8        ""     29      Field29             SQL_Latin1_General_CP1_CI_AS
30        SQLCHAR       0    20       ""     30      Field30             SQL_Latin1_General_CP1_CI_AS
31        SQLCHAR       0    3        ""     31      Field31             SQL_Latin1_General_CP1_CI_AS
32        SQLCHAR       0    8        ""     32      Field32             SQL_Latin1_General_CP1_CI_AS
33        SQLCHAR       0    20       ""     33      Field33             SQL_Latin1_General_CP1_CI_AS
34        SQLCHAR       0    3        ""     34      Field34             SQL_Latin1_General_CP1_CI_AS
35        SQLCHAR       0    1        ""     35      Field35             SQL_Latin1_General_CP1_CI_AS
36        SQLCHAR       0    8        ""     36      Field36             SQL_Latin1_General_CP1_CI_AS
37        SQLCHAR       0    25       ""     37      Field37             SQL_Latin1_General_CP1_CI_AS
38        SQLCHAR       0    8        ""     38      Field38             SQL_Latin1_General_CP1_CI_AS
39        SQLCHAR       0    25       ""     39      Field39             SQL_Latin1_General_CP1_CI_AS
40        SQLCHAR       0    1        ""     40      Field40             SQL_Latin1_General_CP1_CI_AS
41        SQLCHAR       0    8        ""     41      Field41             SQL_Latin1_General_CP1_CI_AS
42        SQLCHAR       0    3        ""     42      Field42             SQL_Latin1_General_CP1_CI_AS
43        SQLCHAR       0    12       ""     43      Field43             SQL_Latin1_General_CP1_CI_AS
44        SQLCHAR       0    8        ""     44      Field44             SQL_Latin1_General_CP1_CI_AS
45        SQLCHAR       0    12       ""     45      Field45             SQL_Latin1_General_CP1_CI_AS
46        SQLCHAR       0    1        ""     46      Field46             SQL_Latin1_General_CP1_CI_AS
47        SQLCHAR       0    1        ""     47      Field47             SQL_Latin1_General_CP1_CI_AS
48        SQLCHAR       0    1        ""     48      Field48             SQL_Latin1_General_CP1_CI_AS
49        SQLCHAR       0    1        ""     49      Field49             SQL_Latin1_General_CP1_CI_AS
50        SQLCHAR       0    8        ""     50      Field50             SQL_Latin1_General_CP1_CI_AS
51        SQLCHAR       0    10       ""     51      Field51             SQL_Latin1_General_CP1_CI_AS
52        SQLCHAR       0    3        ""     52      Field52             SQL_Latin1_General_CP1_CI_AS
53        SQLCHAR       0    2        ""     53      Field53             SQL_Latin1_General_CP1_CI_AS
54        SQLCHAR       0    8        ""     54      Field54             SQL_Latin1_General_CP1_CI_AS
55        SQLCHAR       0    1        ""     55      Field55             SQL_Latin1_General_CP1_CI_AS
56        SQLCHAR       0    8        ""     56      Field56             SQL_Latin1_General_CP1_CI_AS
57        SQLCHAR       0    14       ""     57      Field57             SQL_Latin1_General_CP1_CI_AS
58        SQLCHAR       0    1        ""     58      Field58             SQL_Latin1_General_CP1_CI_AS
59        SQLCHAR       0    1        ""     59      Field59             SQL_Latin1_General_CP1_CI_AS
60        SQLCHAR       0    1        ""     60      Field60             SQL_Latin1_General_CP1_CI_AS
61        SQLCHAR       0    4        ""     61      Field61             SQL_Latin1_General_CP1_CI_AS
62        SQLCHAR       0    1        ""     62      Field62             SQL_Latin1_General_CP1_CI_AS
63        SQLCHAR       0    8        ""     63      Field63             SQL_Latin1_General_CP1_CI_AS
64        SQLCHAR       0    8        ""     64      Field64             SQL_Latin1_General_CP1_CI_AS
65        SQLCHAR       0    1        ""     65      Field65             SQL_Latin1_General_CP1_CI_AS
66        SQLCHAR       0    3        ""     66      Field66             SQL_Latin1_General_CP1_CI_AS
67        SQLCHAR       0    1        ""     67      Field67             SQL_Latin1_General_CP1_CI_AS
68        SQLCHAR       0    3        ""     68      Field68             SQL_Latin1_General_CP1_CI_AS
69        SQLCHAR       0    35       ""     69      Field69             SQL_Latin1_General_CP1_CI_AS
70        SQLCHAR       0    30       ""     70      Field70             SQL_Latin1_General_CP1_CI_AS
71        SQLCHAR       0    25       ""     71      Field71             SQL_Latin1_General_CP1_CI_AS
72        SQLCHAR       0    20       ""     72      Field72             SQL_Latin1_General_CP1_CI_AS
73        SQLCHAR       0    2        ""     73      Field73             SQL_Latin1_General_CP1_CI_AS
74        SQLCHAR       0    9        ""     74      Field74             SQL_Latin1_General_CP1_CI_AS
75        SQLCHAR       0    8        ""     75      Field75             SQL_Latin1_General_CP1_CI_AS
76        SQLCHAR       0    20       ""     76      Field76             SQL_Latin1_General_CP1_CI_AS
77        SQLCHAR       0    8        ""     77      Field77             SQL_Latin1_General_CP1_CI_AS
78        SQLCHAR       0    20       ""     78      Field78             SQL_Latin1_General_CP1_CI_AS
79        SQLCHAR       0    6        ""     79      Field79             SQL_Latin1_General_CP1_CI_AS
80        SQLCHAR       0    9        ""     80      Field80             SQL_Latin1_General_CP1_CI_AS
81        SQLCHAR       0    12       ""     81      Field81             SQL_Latin1_General_CP1_CI_AS
82        SQLCHAR       0    10       ""     82      Field82             SQL_Latin1_General_CP1_CI_AS
83        SQLCHAR       0    10       ""     83      Field83             SQL_Latin1_General_CP1_CI_AS
84        SQLCHAR       0    50       ""     84      Field84             SQL_Latin1_General_CP1_CI_AS
85        SQLCHAR       0    25       ""     85      Field85             SQL_Latin1_General_CP1_CI_AS
86        SQLCHAR       0    25       ""     86      Field86             SQL_Latin1_General_CP1_CI_AS
87        SQLCHAR       0    40       ""     87      Field87             SQL_Latin1_General_CP1_CI_AS
88        SQLCHAR       0    40       ""     88      Field88             SQL_Latin1_General_CP1_CI_AS
89        SQLCHAR       0    40       ""     89      Field89             SQL_Latin1_General_CP1_CI_AS
90        SQLCHAR       0    30       ""     90      Field90             SQL_Latin1_General_CP1_CI_AS
91        SQLCHAR       0    20       ""     91      Field91             SQL_Latin1_General_CP1_CI_AS
92        SQLCHAR       0    15       ""     92      Field92             SQL_Latin1_General_CP1_CI_AS
93        SQLCHAR       0    8        ""     93      Field93             SQL_Latin1_General_CP1_CI_AS
94        SQLCHAR       0    8        ""     94      Field94             SQL_Latin1_General_CP1_CI_AS
95        SQLCHAR       0    20       ""     95      Field95             SQL_Latin1_General_CP1_CI_AS
96        SQLCHAR       0    10       ""     96      Field96             SQL_Latin1_General_CP1_CI_AS
97        SQLCHAR       0    20       ""     97      Field97             SQL_Latin1_General_CP1_CI_AS
98        SQLCHAR       0    20       ""     98      Field98             SQL_Latin1_General_CP1_CI_AS
99        SQLCHAR       0    2        ""     99      Field99             SQL_Latin1_General_CP1_CI_AS
100       SQLCHAR       0    60       ""     100     Field100            SQL_Latin1_General_CP1_CI_AS
101       SQLCHAR       0    9        ""     101     Field101            SQL_Latin1_General_CP1_CI_AS
102       SQLCHAR       0    1        ""     102     Field102            SQL_Latin1_General_CP1_CI_AS
103       SQLCHAR       0    200      ""     103     Field103            SQL_Latin1_General_CP1_CI_AS
104       SQLCHAR       0    20       ""     104     Field104            SQL_Latin1_General_CP1_CI_AS
105       SQLCHAR       0    2        ""     105     Field105            SQL_Latin1_General_CP1_CI_AS
106       SQLCHAR       0    2        ""     106     Field106            SQL_Latin1_General_CP1_CI_AS
107       SQLCHAR       0    1        ""     107     Field107            SQL_Latin1_General_CP1_CI_AS
108       SQLCHAR       0    2        ""     108     Field108            SQL_Latin1_General_CP1_CI_AS
109       SQLCHAR       0    8        ""     109     Field109            SQL_Latin1_General_CP1_CI_AS
110       SQLCHAR       0    2        ""     110     Field110            SQL_Latin1_General_CP1_CI_AS
111       SQLCHAR       0    2        ""     111     Field111            SQL_Latin1_General_CP1_CI_AS
112       SQLCHAR       0    2        ""     112     Field112            SQL_Latin1_General_CP1_CI_AS
113       SQLCHAR       0    2        ""     113     Field113            SQL_Latin1_General_CP1_CI_AS
114       SQLCHAR       0    1        ""     114     Field114            SQL_Latin1_General_CP1_CI_AS
115       SQLCHAR       0    1        ""     115     Field115            SQL_Latin1_General_CP1_CI_AS
116       SQLCHAR       0    1        ""     116     Field116            SQL_Latin1_General_CP1_CI_AS
117       SQLCHAR       0    1        ""     117     Field117            SQL_Latin1_General_CP1_CI_AS
118       SQLCHAR       0    1        ""     118     Field118            SQL_Latin1_General_CP1_CI_AS
119       SQLCHAR       0    20       ""     119     Field119            SQL_Latin1_General_CP1_CI_AS
120       SQLCHAR       0    4        ""     120     Field120            SQL_Latin1_General_CP1_CI_AS
121       SQLCHAR       0    4        ""     121     Field121            SQL_Latin1_General_CP1_CI_AS
122       SQLCHAR       0    2        ""     122     Field122            SQL_Latin1_General_CP1_CI_AS
123       SQLCHAR       0    4179     "\r\n" 0       ExtraFiller         SQL_Latin1_General_CP1_CI_AS

Open in new window

0
Comment
Question by:10DayWu
  • 2
3 Comments
 
LVL 15

Accepted Solution

by:
dbbishop earned 500 total points
Comment Utility
The file is read, as you indicated, as a fixed-length file. Have you fully examined the data that was imported. As a CRLF is expected as a row terminator, if the row is shorter than expected, the CRLF will get read in as part of the field that was in that position. From that point forward, data starts 'wrapping'.

I would closely examine the imported data and make sure you got what was expected.

Are you wanting the process to fail completely (not import any data) if the file format is not correct.
0
 

Author Comment

by:10DayWu
Comment Utility
Hi.  Thank you for the feedback.

Yes, I did review the imported data, and have assured that records didn't wrap.  For the 39 successfully imported records, all data seems to be in the appropriate destination columns.

Yes, in this case, since max errors is set to zero, my expectation is that the whole process would fail.  (I was using this to test my error handling.)  I expected it to fail when it sees the CRLF before the expected position (say position 250) instead of position 6000.  

What is really odd to me is that it seems to just ignore the 3 incomplete records.  They don't appear in the table at all.  For some reason, both BULK INSERT and BCP seem to just be glossing over these records...
0
 

Author Comment

by:10DayWu
Comment Utility
After further examination of the data, the posted solution is correct.  The record following the incomplete row is read as part of the incomplete row...
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now