StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POBCP Import Help
text
Body
copied!<p>I'm having trouble importing data from a tab-delimited *.txt file into my SQL table using BCP.</p> <p>I keep getting two "Invalid character value for cast specification" errors.</p> <p>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.</p> <p>With that test I got an "Invalid character value for cast specification" error and a "Unexpected EOF encountered in BCP data-file"</p> <p>Is there any way to tell what rows/columns are triggering the errors?</p> <p>Any tips/suggestions for debugging BCP?</p> <pre><code> #@ 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 neeta@orderdynamics.com <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 neeta@orderdynamics.com 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 neeta@orderdynamics.com <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 neeta@orderdynamics.com 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 </code></pre> <p>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.</p> <pre><code>#@ 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 neeta@orderdynamics.com <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 neeta@orderdynamics.com 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 neeta@orderdynamics.com <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 neeta@orderdynamics.com 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 </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload