how to convert varchar to numeric ?

[meh]

I have imported data from excel file. When data came to SQL table, the type
of AMOUNT column was varchar. I tried to convert and cast amount type of
amount column to number type but it does not allow me to convert.

What is the best way of importing data into SQL and type stays the same as
it was in excel file ?

Or anyone has any better solution, please let me.

Thanks.

[Andrew John]

Meh,

You need a 2nd column, temporarily at least:

Update MyTable
set fltAmount = cast( vchAmount as float )
where isnumeric( vchAmount ) = 1

Then check the ones that dont update for garbage.

Regards
AJ

"meh" <me**@hotmail.c om> wrote in message news:w1******** **********@nwrd dc01.gnilink.ne t...

I have imported data from excel file. When data came to SQL table, the type
of AMOUNT column was varchar. I tried to convert and cast amount type of
amount column to number type but it does not allow me to convert.

What is the best way of importing data into SQL and type stays the same as
it was in excel file ?

Or anyone has any better solution, please let me.

Thanks.

[Delbert Glass]

FYI, that will sometimes fail.

For example:

declare @s varchar(25)
set @s = '-'
select cast(@s as float) where isnumeric(@s) = 1

How try with integer instead of float.

Bye,
Delbert Glass

"Andrew John" <aj@DELETEmistr ose.com> wrote in message
news:3f******** @duster.adelaid e.on.net...

Meh,

You need a 2nd column, temporarily at least:

Update MyTable
set fltAmount = cast( vchAmount as float )
where isnumeric( vchAmount ) = 1

Then check the ones that dont update for garbage.

Regards
AJ

"meh" <me**@hotmail.c om> wrote in message

news:w1******** **********@nwrd dc01.gnilink.ne t...

I have imported data from excel file. When data came to SQL table, the type of AMOUNT column was varchar. I tried to convert and cast amount type of amount column to number type but it does not allow me to convert.

What is the best way of importing data into SQL and type stays the same as it was in excel file ?

Or anyone has any better solution, please let me.

Thanks.

[Tim Hinkel]

You could create a new table, with the correct datatypes. Then use a select
into query to add the data collected from the import. Use the convert()
functionality to make the varchars integers.

"meh" <me**@hotmail.c om> wrote in message
news:w1******** **********@nwrd dc01.gnilink.ne t...

I have imported data from excel file. When data came to SQL table, the type of AMOUNT column was varchar. I tried to convert and cast amount type of
amount column to number type but it does not allow me to convert.

What is the best way of importing data into SQL and type stays the same as
it was in excel file ?

Or anyone has any better solution, please let me.

Thanks.

[Bob Simms]

"Tim Hinkel" <th*****@orcapa ck.com> wrote in message
news:u40mb.1364 2$e01.26060@att bi_s02...

You could create a new table, with the correct datatypes. Then use a select into query to add the data collected from the import. Use the convert()
functionality to make the varchars integers.

"meh" <me**@hotmail.c om> wrote in message
news:w1******** **********@nwrd dc01.gnilink.ne t...

I have imported data from excel file. When data came to SQL table, the

type

of AMOUNT column was varchar. I tried to convert and cast amount type of amount column to number type but it does not allow me to convert.

What is the best way of importing data into SQL and type stays the same as it was in excel file ?

Or anyone has any better solution, please let me.

Thanks.

You could create a DTS package that coerced the data into the correct format
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.530 / Virus Database: 325 - Release Date: 22/10/2003