StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>I read the this link here <a href="http://luke.breuer.com/time/item/bcp_utility_gotchas/46.aspx" rel="noreferrer">http://luke.breuer.com/time/item/bcp_utility_gotchas/46.aspx</a></p> <p>It says:</p> <p>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:</p> <pre> #@ 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 </pre> <p>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}.</p> <p>Pleas also check if the date formats are properly written. <a href="http://support.microsoft.com/kb/883503" rel="noreferrer">http://support.microsoft.com/kb/883503</a></p> <p>EDIT 2 - 1241PST</p> <p>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:</p> <p>2011-01-21 12:30:10.917</p> <p>I think I ran into this problem once... </p> <p>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. </p> <p>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.</p>

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