Convert/Cast from Varchar to decimal

guys, i've got a quick question on uploading a .txt or .csv file to a table
in sql server 2000.
i had been using a Bulk Insert to a dummy table where all columns are varcha
r.
then selecting that table and running it thru a for each loop in an asp.net
application and attempting the conversion there just using Cdbl(datarow.Item
(0))
or Cdec(datarow.Item(0)).
is there a better way to do this right on the database itself?
because it seems like somewhere an implicit rounding is occuring so i'll get
55.00 where 55.50 should be.
the problem i think is the inconsistant values, but, i thought i'd ask the r
eal experts.
as some of the values in the .csv (formerly .xls) file are 33.02, some are w
hole numbers 234 and even others are 55.5.
ive tried using Cast(Amount as Decimal(5,2)) in an insert statement and the
consistant error i get is "Arithmentic overflow converting numeric to data t
ype numeric"
if anyone has any suggestions, i'd really appreciate it.
thanks again
rik
****************************************
******************************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET
resources...Did you try altering that column in the dummy table to numeric(5, 2) instead
varchar?
AMB
"rik butcher" wrote:

> guys, i've got a quick question on uploading a .txt or .csv file to a tabl
e in sql server 2000.
> i had been using a Bulk Insert to a dummy table where all columns are varc
har.
> then selecting that table and running it thru a for each loop in an asp.ne
t
> application and attempting the conversion there just using Cdbl(datarow.It
em(0))
> or Cdec(datarow.Item(0)).
> is there a better way to do this right on the database itself?
> because it seems like somewhere an implicit rounding is occuring so i'll g
et 55.00 where 55.50 should be.
> the problem i think is the inconsistant values, but, i thought i'd ask the
real experts.
> as some of the values in the .csv (formerly .xls) file are 33.02, some ar
e whole numbers 234 and even others are 55.5.
> ive tried using Cast(Amount as Decimal(5,2)) in an insert statement and th
e consistant error i get is "Arithmentic overflow converting numeric to data
type numeric"
> if anyone has any suggestions, i'd really appreciate it.
> thanks again
> rik
> ****************************************
******************************
> Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
> Comprehensive, categorised, searchable collection of links to ASP & ASP.NE
T resources...
>|||Why are all the columns of your dummy table all varchar? Assuming that
the data is clean, you can create the target table with a column of the
desired decimal type and have nothing else to do about conversion.
As far as the error you're seeing with CAST, it indicates that there is
a value in the [Amount] column that represents a value too big for
decimal(5,2), in other words, greater than 999.99 or less than -999.99.
Again assuming that the data is clean, CAST should work if the target
type can hold the values. You'll get a different error if you have non-
numeric strings, like 'abc':
Server: Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.
I don't know the specifications of the Cdbl and Cdec functions, so I won't
comment on why some values seem to be changed more than rounding
should cause.
Steve Kass
Drew University
rbutch@.coair.com wrote:

>guys, i've got a quick question on uploading a .txt or .csv file to a table
in sql server 2000.
>i had been using a Bulk Insert to a dummy table where all columns are varch
ar.
>then selecting that table and running it thru a for each loop in an asp.net
>application and attempting the conversion there just using Cdbl(datarow.Ite
m(0))
>or Cdec(datarow.Item(0)).
>is there a better way to do this right on the database itself?
>because it seems like somewhere an implicit rounding is occuring so i'll ge
t 55.00 where 55.50 should be.
>the problem i think is the inconsistant values, but, i thought i'd ask the
real experts.
> as some of the values in the .csv (formerly .xls) file are 33.02, some are
whole numbers 234 and even others are 55.5.
>ive tried using Cast(Amount as Decimal(5,2)) in an insert statement and the
consistant error i get is "Arithmentic overflow converting numeric to data
type numeric"
>if anyone has any suggestions, i'd really appreciate it.
>thanks again
>rik
> ****************************************
******************************
>Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
>Comprehensive, categorised, searchable collection of links to ASP & ASP.NET
resources...
>|||thanks guys. you both were right. using numeric(5,2) worked but i still had
to widen that column again [that's what the overflow message was referring t
o] - so, i dont have to use a dummy table. and bcp bulk insert is working li
ke a charm.
i just had to look thru the actual values and there it was.
sometimes its the simplest things - and i appreciate you guys setting me str
aight on this.
thanks again
rik
****************************************
******************************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET
resources...