BCP Import Help

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  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

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  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, En开发者_StackOverflow社区amel = 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

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:

#@ Row 1, Column 9: Invalid character value for cast specification @#
2   0300    HC:36415    21  1   0       20070509    2
3   0300    HC:36415    21  1   0       20070608    2
#@ Row 2, Column 9: Invalid character value for cast specification @#
4   0301    HC:80076    366 1   0       20070509    2
5   0301    HC:80076    366 1   0       20070608    2

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.