asked on

BCP.exe SQL Import - Invalid character value for cast specification

Good afternoon,

I have a couple of flat files, with roughly 10K records that I need to import to my MS SQL DB once of twice a week.
I don't have access to the actual server, just access via Management Studio.

So... I am using the BCP command to run this import. For now, I am testing this out on my local machine before using it in production.
The syntax is (sanitised):

bcp db.dbo.tableOld in E:\datafile.txt -c -r "0x0a" -S myserver-x

It gets through about 50+ records, then gives me the following error:
SQLState = 22005, NativeError = 0
Error = [Microsoft][ODBC Driver 11 for SQL Server]Invalid character value for cast specification
(10 of them, before failing)

I suspect this may have to do with the date field?

Anyway... not sure how to address?

Any assistance would be greatly appreciated.

Thanks,

E.D.

SOLUTION

funwithdotnet

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

Zberteoc

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

cuadmin

ASKER

Thanks for the advise guys... I found the issue, it wasn't date at all. There are 4 fields that were FLOAT. I changed them to varchar and the import worked perfectly. The only issue is... after I import, I try to change back to FLOAT and I get the following error:

- Unable to modify table.
Error converting data type nvarchar to float.

Any thoughts? Thanks again guys!

cuadmin

ASKER

I think I found it... the some of the FLOAT values - 10-20, were letters. I assume that's why it wouldn't convert?! :-)

Zberteoc

Varchar and Nvarchar datatypes "swallow" anything so there is no guarantee that the values imported in them are numerics that can be converted to FLOAT. In other words the input data could contain non numeric values either by mistake or it is just that.

Zberteoc

It is possible to have letters in FLOAT values:

6.02E+12

this is a valid FLOAT value.

Zberteoc

You can identify the non numeric values with:

select * from YourTable where isnumeric(nvarchar_colum)=0

cuadmin

ASKER

I think I've got it, thanks guys!