BCP 导入帮助
我在使用 BCP 将数据从制表符分隔的 *.txt 文件导入到 SQL 表时遇到问题。
我不断收到两个“强制转换规范的字符值无效”错误。
作为测试，我手动插入了几行。然后我将这些行 BCP 查询到 *.txt 文件。然后我尝试 BCP 导入相同的数据。
通过该测试，我收到“强制转换规范的字符值无效”错误和“BCP 数据文件中遇到意外的 EOF”
有没有办法告诉哪些行/列触发了错误？
对于调试 BCP 有什么提示/建议吗？
#@ Row 1, Column 2: Invalid character value for cast specification @#
1003 1/2/2011 23:59:00 Neeta Garg <NULL> 8888 <NULL> <NULL> WESTBURY NY 11594 US ambulance Yes Agency 92240099 000026 Neeta Garg <NULL> 8888 <NULL> <NULL> WESTBURY NY 11594 US 2222222222 [email protected] <NULL> UPS GRD 42.650 AXB479T;67.5900;2|842000YEL;4.2900;22|B956N;51.8900;1|XSPSPCL;67.0000;1890300;2999.9900;1|B956N;51.8900;1|XSPSPCL;210.0000;1|59047BKLG;9.9900;1 AXB479T;|842000YEL;|B956N;Badge Custom Details : Style = B956, Dimensions = 2 9/16\" x 2 9/16\", Enamel Type = regular, Font = block-black, Panel 2 = , Panel 3 = , Panel 5 = , Panel 6 = , Seal Style = , Attachment = NONE, Text Separator = NONE, Badge Comments = , badgeURL = http://www.blackinton.com/design-a-badge/data/pdf/vhbdab1291672350948.png, RESTXML = http://www.blackinton.com/design-a-badge/generate-xml.php?u=vhbdab1291672350948, Base Price = 59.00, Enamel = 0.00, Struck Solid = 0.00, Seal = 0.0 3271.8700 0.0000 0.0000 0.0000 3271.8700 1 Visa <NULL> 0
1004 1/3/2011 23:59:00 Neeta Garg <NULL> 8888 WESTBURY NY 11594 US ambulance Yes Agency 92240099 000026 Neeta Garg 8888 WESTBURY NY 11594 US 2222222222 [email protected] UPS GRD 0.000 AXB479T;67.5900;2|842000YEL;4.2900;22|B956N;51.8900;1|XSPSPCL;67.0000;1890300;2999.9900;1|B956N;51.8900;1|XSPSPCL;210.0000;1|59047BKLG;9.9900;1A161BK;115.9900;1 AXB479T;|842000YEL;|B956N;Badge Custom Details : Style = B956, Dimensions = 2 9/16\" x 2 9/16\", Enamel Type = regular, Font = block-black, Panel 2 = , Panel 3 = , Panel 5 = , Panel 6 = , Sea
#@ Row 2, Column 2: Invalid character value for cast specification @#
1005 1/4/2011 23:59:00 Neeta Garg <NULL> 8888 <NULL> <NULL> WESTBURY NY 11594 US ambulance Yes Agency 92240099 000026 Neeta Garg <NULL> 8888 <NULL> <NULL> WESTBURY NY 11594 US 2222222222 [email protected] <NULL> UPS GRD 0.000 AXB479T;67.5900;2|842000YEL;4.2900;22|B956N;51.8900;1|XSPSPCL;67.0000;1890300;2999.9900;1|B956N;51.8900;1|XSPSPCL;210.0000;1|59047BKLG;9.9900;1A161BK;115.9900;1A161BK;115.9900;1 AXB479T;|842000YEL;|B956N;Badge Custom Details : Style = B956, Dimensions = 2 9/16\" x 2 9/16\", Enamel Type = regular, Font = block-black, Panel 2 = , Panel 3 = , Panel 5 = , Panel 6 = , Seal Style = , Attachment = NONE, Text Separator = NONE, Badge Comments = , badgeURL = http://www.blackinton.com/design-a-badge/data/pdf/vhbdab1291672350948.png, RESTXML = http://www.blackinton.com/design-a-badge/generate-xml.php?u=vhbdab1291672350948, Base Price = 59.00, Enamel = 0.00, Struck Solid = 0.00, Seal = 0.0 115.9900 8.0000 0.0000 0.0000 123.9900 1 Visa <NULL> 0
1006 2011-01-05 11:27:50.617 Neeta Garg <NULL> 8888 WESTBURY NY 11594 US ambulance Yes Agency 92240099 000026 Neeta Garg 8888 WESTBURY NY 11594 US 2222222222 [email protected] UPS GRD 0.000 AXB479T;67.5900;2|842000YEL;4.2900;22|B956N;51.8900;1|XSPSPCL;67.0000;1890300;2999.9900;1|B956N;51.8900;1|XSPSPCL;210.0000;1|59047BKLG;9.9900;1A161BK;115.9900;1A161BK;115.9900;1A161BK;115.9900;1 AXB479T;|842000YEL;|B956N;Badge Custom Details : Style = B956, Dimensions = 2 9/16\" x 2 9/16\", Enamel Type = regular, Font = block-black, Panel 2 = , Panel
我发现，您不能 BCP 将日期导入为日期时间类型。因此，我将其导入到临时表中，其中类型为 varchar，然后我将 SELECT INTO 我想要的表。但是，即使目标表的列类型为 varchar，我也会收到无效字符错误。
#@ Row 1, Column 2: Invalid character value for cast specification @#
1003 2011-01-21 12:30:10.917 Neeta Garg <NULL> 8888 <NULL> <NULL> WESTBURY NY 11594 US ambulance Yes Agency 92240099 000026 Neeta Garg <NULL> 8888 <NULL> <NULL> WESTBURY NY 11594 US 2222222222 [email protected] <NULL> UPS GRD 42.650 AXB479T;67.5900;2|842000YEL;4.2900;22|B956N;51.8900;1|XSPSPCL;67.0000;1890300;2999.9900;1|B956N;51.8900;1|XSPSPCL;210.0000;1|59047BKLG;9.9900;1 AXB479T;|842000YEL;|B956N;Badge Custom Details : Style = B956, Dimensions = 2 9/16\" x 2 9/16\", Enamel Type = regular, Font = block-black, Panel 2 = , Panel 3 = , Panel 5 = , Panel 6 = , Seal Style = , Attachment = NONE, Text Separator = NONE, Badge Comments = , badgeURL = http://www.blackinton.com/design-a-badge/data/pdf/vhbdab1291672350948.png, RESTXML = http://www.blackinton.com/design-a-badge/generate-xml.php?u=vhbdab1291672350948, Base Price = 59.00, Enamel = 0.00, Struck Solid = 0.00, Seal = 0.0 3271.8700 0.0000 0.0000 0.0000 3271.8700 1 Visa <NULL> 0
1004 2011-01-21 12:30:10.917 Neeta Garg <NULL> 8888 WESTBURY NY 11594 US ambulance Yes Agency 92240099 000026 Neeta Garg 8888 WESTBURY NY 11594 US 2222222222 [email protected] UPS GRD 0.000 AXB479T;67.5900;2|842000YEL;4.2900;22|B956N;51.8900;1|XSPSPCL;67.0000;1890300;2999.9900;1|B956N;51.8900;1|XSPSPCL;210.0000;1|59047BKLG;9.9900;1A161BK;115.9900;1 AXB479T;|842000YEL;|B956N;Badge Custom Details : Style = B956, Dimensions = 2 9/16\" x 2 9/16\", Enamel Type = regular, Font = block-black, Panel 2 = , Panel 3 = , Panel 5 = , Panel 6 = , Sea
#@ Row 2, Column 2: Invalid character value for cast specification @#
1005 2011-01-21 12:30:10.917 Neeta Garg <NULL> 8888 <NULL> <NULL> WESTBURY NY 11594 US ambulance Yes Agency 92240099 000026 Neeta Garg <NULL> 8888 <NULL> <NULL> WESTBURY NY 11594 US 2222222222 [email protected] <NULL> UPS GRD 0.000 AXB479T;67.5900;2|842000YEL;4.2900;22|B956N;51.8900;1|XSPSPCL;67.0000;1890300;2999.9900;1|B956N;51.8900;1|XSPSPCL;210.0000;1|59047BKLG;9.9900;1A161BK;115.9900;1A161BK;115.9900;1 AXB479T;|842000YEL;|B956N;Badge Custom Details : Style = B956, Dimensions = 2 9/16\" x 2 9/16\", Enamel Type = regular, Font = block-black, Panel 2 = , Panel 3 = , Panel 5 = , Panel 6 = , Seal Style = , Attachment = NONE, Text Separator = NONE, Badge Comments = , badgeURL = http://www.blackinton.com/design-a-badge/data/pdf/vhbdab1291672350948.png, RESTXML = http://www.blackinton.com/design-a-badge/generate-xml.php?u=vhbdab1291672350948, Base Price = 59.00, Enamel = 0.00, Struck Solid = 0.00, Seal = 0.0 115.9900 8.0000 0.0000 0.0000 123.9900 1 Visa <NULL> 0
1006 2011-01-21 12:30:10.917 Neeta Garg <NULL> 8888 WESTBURY NY 11594 US ambulance Yes Agency 92240099 000026 Neeta Garg 8888 WESTBURY NY 11594 US 2222222222 [email protected] UPS GRD 0.000 AXB479T;67.5900;2|842000YEL;4.2900;22|B956N;51.8900;1|XSPSPCL;67.0000;1890300;2999.9900;1|B956N;51.8900;1|XSPSPCL;210.0000;1|59047BKLG;9.9900;1A161BK;115.9900;1A161BK;115.9900;1A161BK;115.9900;1 AXB479T;|842000YEL;|B956N;Badge Custom Details : Style = B956, Dimensions = 2 9/16\" x 2 9/16\", Enamel Type = regular, Font = block-black, Panel 2 = , Panel
I'm having trouble importing data from a tab-delimited *.txt file into my SQL table using BCP.
I keep getting two "Invalid character value for cast specification" errors.
As a test, I manually inserted a couple of rows. Then I BCP queryout those rows to a *.txt file. Then I tried to BCP import that same data.
With that test I got an "Invalid character value for cast specification" error and a "Unexpected EOF encountered in BCP data-file"
Is there any way to tell what rows/columns are triggering the errors?
Any tips/suggestions for debugging BCP?
#@ Row 1, Column 2: Invalid character value for cast specification @#
1003 1/2/2011 23:59:00 Neeta Garg <NULL> 8888 <NULL> <NULL> WESTBURY NY 11594 US ambulance Yes Agency 92240099 000026 Neeta Garg <NULL> 8888 <NULL> <NULL> WESTBURY NY 11594 US 2222222222 [email protected] <NULL> UPS GRD 42.650 AXB479T;67.5900;2|842000YEL;4.2900;22|B956N;51.8900;1|XSPSPCL;67.0000;1890300;2999.9900;1|B956N;51.8900;1|XSPSPCL;210.0000;1|59047BKLG;9.9900;1 AXB479T;|842000YEL;|B956N;Badge Custom Details : Style = B956, Dimensions = 2 9/16\" x 2 9/16\", Enamel Type = regular, Font = block-black, Panel 2 = , Panel 3 = , Panel 5 = , Panel 6 = , Seal Style = , Attachment = NONE, Text Separator = NONE, Badge Comments = , badgeURL = http://www.blackinton.com/design-a-badge/data/pdf/vhbdab1291672350948.png, RESTXML = http://www.blackinton.com/design-a-badge/generate-xml.php?u=vhbdab1291672350948, Base Price = 59.00, Enamel = 0.00, Struck Solid = 0.00, Seal = 0.0 3271.8700 0.0000 0.0000 0.0000 3271.8700 1 Visa <NULL> 0
1004 1/3/2011 23:59:00 Neeta Garg <NULL> 8888 WESTBURY NY 11594 US ambulance Yes Agency 92240099 000026 Neeta Garg 8888 WESTBURY NY 11594 US 2222222222 [email protected] UPS GRD 0.000 AXB479T;67.5900;2|842000YEL;4.2900;22|B956N;51.8900;1|XSPSPCL;67.0000;1890300;2999.9900;1|B956N;51.8900;1|XSPSPCL;210.0000;1|59047BKLG;9.9900;1A161BK;115.9900;1 AXB479T;|842000YEL;|B956N;Badge Custom Details : Style = B956, Dimensions = 2 9/16\" x 2 9/16\", Enamel Type = regular, Font = block-black, Panel 2 = , Panel 3 = , Panel 5 = , Panel 6 = , Sea
#@ Row 2, Column 2: Invalid character value for cast specification @#
1005 1/4/2011 23:59:00 Neeta Garg <NULL> 8888 <NULL> <NULL> WESTBURY NY 11594 US ambulance Yes Agency 92240099 000026 Neeta Garg <NULL> 8888 <NULL> <NULL> WESTBURY NY 11594 US 2222222222 [email protected] <NULL> UPS GRD 0.000 AXB479T;67.5900;2|842000YEL;4.2900;22|B956N;51.8900;1|XSPSPCL;67.0000;1890300;2999.9900;1|B956N;51.8900;1|XSPSPCL;210.0000;1|59047BKLG;9.9900;1A161BK;115.9900;1A161BK;115.9900;1 AXB479T;|842000YEL;|B956N;Badge Custom Details : Style = B956, Dimensions = 2 9/16\" x 2 9/16\", Enamel Type = regular, Font = block-black, Panel 2 = , Panel 3 = , Panel 5 = , Panel 6 = , Seal Style = , Attachment = NONE, Text Separator = NONE, Badge Comments = , badgeURL = http://www.blackinton.com/design-a-badge/data/pdf/vhbdab1291672350948.png, RESTXML = http://www.blackinton.com/design-a-badge/generate-xml.php?u=vhbdab1291672350948, Base Price = 59.00, Enamel = 0.00, Struck Solid = 0.00, Seal = 0.0 115.9900 8.0000 0.0000 0.0000 123.9900 1 Visa <NULL> 0
1006 2011-01-05 11:27:50.617 Neeta Garg <NULL> 8888 WESTBURY NY 11594 US ambulance Yes Agency 92240099 000026 Neeta Garg 8888 WESTBURY NY 11594 US 2222222222 [email protected] UPS GRD 0.000 AXB479T;67.5900;2|842000YEL;4.2900;22|B956N;51.8900;1|XSPSPCL;67.0000;1890300;2999.9900;1|B956N;51.8900;1|XSPSPCL;210.0000;1|59047BKLG;9.9900;1A161BK;115.9900;1A161BK;115.9900;1A161BK;115.9900;1 AXB479T;|842000YEL;|B956N;Badge Custom Details : Style = B956, Dimensions = 2 9/16\" x 2 9/16\", Enamel Type = regular, Font = block-black, Panel 2 = , Panel
I figured out, that you can't BCP import dates as datetime type. So, I'm importing it to a temp table, where the type is varchar, then I'm going to SELECT INTO the table I want. But, even with the target table's column type of varchar, I get an invalid character error.
#@ Row 1, Column 2: Invalid character value for cast specification @#
1003 2011-01-21 12:30:10.917 Neeta Garg <NULL> 8888 <NULL> <NULL> WESTBURY NY 11594 US ambulance Yes Agency 92240099 000026 Neeta Garg <NULL> 8888 <NULL> <NULL> WESTBURY NY 11594 US 2222222222 [email protected] <NULL> UPS GRD 42.650 AXB479T;67.5900;2|842000YEL;4.2900;22|B956N;51.8900;1|XSPSPCL;67.0000;1890300;2999.9900;1|B956N;51.8900;1|XSPSPCL;210.0000;1|59047BKLG;9.9900;1 AXB479T;|842000YEL;|B956N;Badge Custom Details : Style = B956, Dimensions = 2 9/16\" x 2 9/16\", Enamel Type = regular, Font = block-black, Panel 2 = , Panel 3 = , Panel 5 = , Panel 6 = , Seal Style = , Attachment = NONE, Text Separator = NONE, Badge Comments = , badgeURL = http://www.blackinton.com/design-a-badge/data/pdf/vhbdab1291672350948.png, RESTXML = http://www.blackinton.com/design-a-badge/generate-xml.php?u=vhbdab1291672350948, Base Price = 59.00, Enamel = 0.00, Struck Solid = 0.00, Seal = 0.0 3271.8700 0.0000 0.0000 0.0000 3271.8700 1 Visa <NULL> 0
1004 2011-01-21 12:30:10.917 Neeta Garg <NULL> 8888 WESTBURY NY 11594 US ambulance Yes Agency 92240099 000026 Neeta Garg 8888 WESTBURY NY 11594 US 2222222222 [email protected] UPS GRD 0.000 AXB479T;67.5900;2|842000YEL;4.2900;22|B956N;51.8900;1|XSPSPCL;67.0000;1890300;2999.9900;1|B956N;51.8900;1|XSPSPCL;210.0000;1|59047BKLG;9.9900;1A161BK;115.9900;1 AXB479T;|842000YEL;|B956N;Badge Custom Details : Style = B956, Dimensions = 2 9/16\" x 2 9/16\", Enamel Type = regular, Font = block-black, Panel 2 = , Panel 3 = , Panel 5 = , Panel 6 = , Sea
#@ Row 2, Column 2: Invalid character value for cast specification @#
1005 2011-01-21 12:30:10.917 Neeta Garg <NULL> 8888 <NULL> <NULL> WESTBURY NY 11594 US ambulance Yes Agency 92240099 000026 Neeta Garg <NULL> 8888 <NULL> <NULL> WESTBURY NY 11594 US 2222222222 [email protected] <NULL> UPS GRD 0.000 AXB479T;67.5900;2|842000YEL;4.2900;22|B956N;51.8900;1|XSPSPCL;67.0000;1890300;2999.9900;1|B956N;51.8900;1|XSPSPCL;210.0000;1|59047BKLG;9.9900;1A161BK;115.9900;1A161BK;115.9900;1 AXB479T;|842000YEL;|B956N;Badge Custom Details : Style = B956, Dimensions = 2 9/16\" x 2 9/16\", Enamel Type = regular, Font = block-black, Panel 2 = , Panel 3 = , Panel 5 = , Panel 6 = , Seal Style = , Attachment = NONE, Text Separator = NONE, Badge Comments = , badgeURL = http://www.blackinton.com/design-a-badge/data/pdf/vhbdab1291672350948.png, RESTXML = http://www.blackinton.com/design-a-badge/generate-xml.php?u=vhbdab1291672350948, Base Price = 59.00, Enamel = 0.00, Struck Solid = 0.00, Seal = 0.0 115.9900 8.0000 0.0000 0.0000 123.9900 1 Visa <NULL> 0
1006 2011-01-21 12:30:10.917 Neeta Garg <NULL> 8888 WESTBURY NY 11594 US ambulance Yes Agency 92240099 000026 Neeta Garg 8888 WESTBURY NY 11594 US 2222222222 [email protected] UPS GRD 0.000 AXB479T;67.5900;2|842000YEL;4.2900;22|B956N;51.8900;1|XSPSPCL;67.0000;1890300;2999.9900;1|B956N;51.8900;1|XSPSPCL;210.0000;1|59047BKLG;9.9900;1A161BK;115.9900;1A161BK;115.9900;1A161BK;115.9900;1 AXB479T;|842000YEL;|B956N;Badge Custom Details : Style = B956, Dimensions = 2 9/16\" x 2 9/16\", Enamel Type = regular, Font = block-black, Panel 2 = , Panel
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
我在这里阅读了此链接 http://luke.breuer.com/time/item /bcp_utility_gotchas/46.aspx
它说：
强制转换规范的字符值无效
bcp 尝试上传到列的值的数据类型错误。在最简单的情况下，这是由于将非整数值上传到整数列、错误的日期格式等造成的。但是，如果 {数据文件中的列数} != {目标表中的列数/格式化文件}。查看是否存在列 # 不匹配的一个好方法是，错误文件是否吐出（您确实使用了 -e error.txt，对吧？）如下所示：
注意 bcp 认为两行数据构成要上传的一行到数据库。这
是因为 bcp 知道列分隔符是 {tab}，最后一个分隔符除外，即 {newline} — 如果它需要另一列，它将直接读取 {newline}。
另请检查日期格式是否正确书写。 http://support.microsoft.com/kb/883503
编辑2 - 1241PST
假设您的数据库是 SQL Server，并且您尝试导入的列数据类型是 datetime，格式应如下所示：
2011-01-21 12:30:10.917
我想我曾经遇到过这个问题...
尝试打开您的平面文件（带有制表符分隔的 .txt）转换为 EXCEL，然后从那里选择列并更改格式。您可以通过转到 Excel 中的“数据”选项卡，选择列，然后点击“文本到列”，选择分隔符，然后完成来完成此操作。您的所有日期和时间都应该出现。
然后你必须再次选择该列，右键单击，设置单元格格式，并在类别列表中选择DATE，并将格式更改为SQL Server期望的标准。如果这没有帮助，不知道还有什么，只能尝试通过使用表/数据库测试简单的小型测试用例来缩小问题范围。
I read the this link here http://luke.breuer.com/time/item/bcp_utility_gotchas/46.aspx
It says:
Invalid character value for cast specification
The value bcp attempted to upload to a column was the wrong data type. In the simplest cases, this is due to uploading a non-integer value into an integer column, bad date formats, etc. However, it can also occur if {number of columns in data file} != {number of columns in destination table/format file}. A good way to see if there is a column # mismatch is if the error file spit out (you did use -e error.txt, right?) looks like this:
Notice that what bcp thinks two rows of data constitute one row to upload to the database. This
is because bcp knows that the column delimiters are {tab}, except for the last one, which is {newline} — if it is expecting another column, it'll read right past a {newline}.
Pleas also check if the date formats are properly written. http://support.microsoft.com/kb/883503
EDIT 2 - 1241PST
Assuming that your db is SQL Server, and the column data type you're trying to import to is datetime, the format should look like this:
2011-01-21 12:30:10.917
I think I ran into this problem once...
try opening your flat file (.txt with tab delimited) to EXCEL, and from there choose the column and change the format. You can do this by going to the DATA tab in excel, select the column, and hit "text to columns", select delimited, and go finished. All your date and times should appear.
Then you have to select the column again, right click, to format cells, and select DATE in the category list, and change the format to the SQL Server expected standard. If that doesn't help, not sure what else but to try narrowing down the issue by testing simple, small test cases with your tables/database.