BCP和BULK INSERT无法对固定宽度文件中的不完整记录抛出错误。
BCP和BULK INSERT无法对固定宽度文件中的不完整记录抛出错误。
提问于 2014-02-12 13:54:44
我试图使用大容量插入或bcp插入文件。该文件是一个固定宽度的文件，其列(位置)比格式文件指定的列(位置)少得多。在执行任何一条语句时，都会排除这些行，但进程不会出错。
该文件包含42条记录。三个记录是不完整的(在可能的6000个位置中，字段长度仅为几百个)。我期待所有42条记录都会被尝试，最终，由于3条不完整的记录，进程会出错。任何帮助或洞察力都将不胜感激。谢谢。
以下查询以下列消息执行：“(受影响的39行)”
批量插入：
BULK INSERT ##tmp_table
FROM '\\cf\files\file_1_20131223.txt'
WITH (FIRSTROW = 1, MAXERRORS = 0, FORMATFILE = '\\cf\bcpFiles\file_1.fmt')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格式文件如下所示：
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回答 2
Stack Overflow用户
回答已采纳
发布于 2014-02-19 15:44:41
BCP和BULK INSERT似乎只是将不完整记录上的CRLF看作值，而不承认它是行的结束，这导致了下一行作为不完整记录的一部分被使用。要获得更完整的解释，请参见MSDN上的答案
Stack Overflow用户
发布于 2017-10-19 07:06:56
这可以通过指定一个错误文件来解决。这将导致bcp创建一个带有错误行的小报告。之后，程序可以检查此错误文件是否包含任何字符。
对于Windows批处理，代码如下：
SET "ERRORFILE=C:\mytable.err"
TYPE NUL>%ERRORFILE%
bcp %DATABASENAME%.dbo.%DESTTABLE% IN %INPUTFILE% -f %FORMATFILE% -e "%ERRORFILE%" -S "%DBSERVER%" -U "%USERNAME%" -P "%PASSWORD%"
SET "SIZE=0"
FOR /f %%i IN ("%ERROR_FILE%") DO SET SIZE=%%~zi
IF %SIZE% GTR 0 (
echo "import file contains error(s)."
GOTO ERROR_HANDLER
)页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/21740582
复制
相似问题
相机预览尺寸太大
墙纸应用尺寸太大？
pgfplots错误:尺寸太大
Silverlight打印尺寸太大
角5束尺寸太大
添加站长 进交流群
领取专属 10元无门槛券
AI混元助手 在线答疑
关注 腾讯云开发者公众号
洞察 腾讯核心技术
剖析业界实践案例
腾讯云开发者
