Bigint stored as varchar has issues...

Hi,
I'm importing contact information into SQL Server from Excel using
OPENROWSET. The issue I'm having is with how the phone numbers get stored.
The phone numbers have no extra characters like dashes, so they appear like
9495551212, which is equivelant to 949-555-1212. The phone number is being
imported to a varchar field, which implicitly converts it to a format like
7.70947e+009. The final destination field is intended to hold the data as it
is originally, so it's a bigint datatype.
My first thought was to use CAST or CONVERT. But I get:
Server: Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to bigint.
Any ideas?Try,
select str(cast('7.70947e+009' as float), 10, 0)
AMB
"Eric" wrote:

> Hi,
> I'm importing contact information into SQL Server from Excel using
> OPENROWSET. The issue I'm having is with how the phone numbers get stored.
> The phone numbers have no extra characters like dashes, so they appear lik
e
> 9495551212, which is equivelant to 949-555-1212. The phone number is being
> imported to a varchar field, which implicitly converts it to a format like
> 7.70947e+009. The final destination field is intended to hold the data as
it
> is originally, so it's a bigint datatype.
> My first thought was to use CAST or CONVERT. But I get:
> Server: Msg 8114, Level 16, State 5, Line 1
> Error converting data type varchar to bigint.
> Any ideas?|||Problem #1 7.70947e+009 is not a representation of an integer. It is
expressed with exponentials, so it is a float. You first have to cast it
to a float:
select cast(cast ('7.70947e+009' as float) as bigint)
Problem #2,( and this is the kicker) The result will be:
7709470000
Which is probably not a valid phone number. You are losing some of the
significant digits. Try casting it ot a bigint first, then to a
varhchar(10).
SELECT cast(cast(phonenumber as bigint) as varchar(10))
FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=c:\test.xls;Extended Properties=Excel 8.0')...sheet1$
This worked for me.
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:6BCA27F2-B212-4B4A-9E47-FAAEA48B7A14@.microsoft.com...
> Hi,
> I'm importing contact information into SQL Server from Excel using
> OPENROWSET. The issue I'm having is with how the phone numbers get stored.
> The phone numbers have no extra characters like dashes, so they appear
> like
> 9495551212, which is equivelant to 949-555-1212. The phone number is being
> imported to a varchar field, which implicitly converts it to a format like
> 7.70947e+009. The final destination field is intended to hold the data as
> it
> is originally, so it's a bigint datatype.
> My first thought was to use CAST or CONVERT. But I get:
> Server: Msg 8114, Level 16, State 5, Line 1
> Error converting data type varchar to bigint.
> Any ideas?|||That did the trick! Thank you so much...
"Louis Davidson" wrote:

> Problem #1 7.70947e+009 is not a representation of an integer. It is
> expressed with exponentials, so it is a float. You first have to cast it
> to a float:
> select cast(cast ('7.70947e+009' as float) as bigint)
> Problem #2,( and this is the kicker) The result will be:
> 7709470000
> Which is probably not a valid phone number. You are losing some of the
> significant digits. Try casting it ot a bigint first, then to a
> varhchar(10).
> SELECT cast(cast(phonenumber as bigint) as varchar(10))
> FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
> 'Data Source=c:\test.xls;Extended Properties=Excel 8.0')...sheet1$
> This worked for me.
>
> --
> ----
--
> Louis Davidson - drsql@.hotmail.com
> SQL Server MVP
> Compass Technology Management - www.compass.net
> Pro SQL Server 2000 Database Design -
> http://www.apress.com/book/bookDisplay.html?bID=266
> Note: Please reply to the newsgroups only unless you are interested in
> consulting services. All other replies may be ignored :)
> "Eric" <Eric@.discussions.microsoft.com> wrote in message
> news:6BCA27F2-B212-4B4A-9E47-FAAEA48B7A14@.microsoft.com...
>
>|||You could try adding IMEX=1 to the connection properties to see if that
helps.
select *
from OpenRowSet(
'Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\filename.xls;HDR=YES;IMEX=1;'
,Sheet1$
)
"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:6BCA27F2-B212-4B4A-9E47-FAAEA48B7A14@.microsoft.com...
> Hi,
> I'm importing contact information into SQL Server from Excel using
> OPENROWSET. The issue I'm having is with how the phone numbers get stored.
> The phone numbers have no extra characters like dashes, so they appear
> like
> 9495551212, which is equivelant to 949-555-1212. The phone number is being
> imported to a varchar field, which implicitly converts it to a format like
> 7.70947e+009. The final destination field is intended to hold the data as
> it
> is originally, so it's a bigint datatype.
> My first thought was to use CAST or CONVERT. But I get:
> Server: Msg 8114, Level 16, State 5, Line 1
> Error converting data type varchar to bigint.
> Any ideas?|||Hi
Make sure that on the Excel spreadsheet, the column is not "general" but
rather a "text" type cell.
General cells are evaluated by the Excel driver and if they meet numeric
criteria, they are passed as numeric to SQL Server. This is an Excel issue.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:6BCA27F2-B212-4B4A-9E47-FAAEA48B7A14@.microsoft.com...
> Hi,
> I'm importing contact information into SQL Server from Excel using
> OPENROWSET. The issue I'm having is with how the phone numbers get stored.
> The phone numbers have no extra characters like dashes, so they appear
like
> 9495551212, which is equivelant to 949-555-1212. The phone number is being
> imported to a varchar field, which implicitly converts it to a format like
> 7.70947e+009. The final destination field is intended to hold the data as
it
> is originally, so it's a bigint datatype.
> My first thought was to use CAST or CONVERT. But I get:
> Server: Msg 8114, Level 16, State 5, Line 1
> Error converting data type varchar to bigint.
> Any ideas?