Reading data from Excel...

[piero]

Hi,
I have a Excel sheet that have a column with mixed data:
column can contain data like "892-234-32A" or like "892298343233432"

I need to get all data column such as "text" to avoid numeric field show as
exponential number.

I'm trying with:
SELECT CAST (CODICE as bigint(25)) FROM
OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel
8.0;IMEX=1;HDR=YES;Database=C:\tempxadp\dist1.xls' , Foglio1$)

But I get error conversion type "from nvarchar to bigint" when query meet
the alphanumeric field.

Any tips ?

Thanks in advance

Piero

Italy

[Simon Hayes]

"piero" <g.*******@pesaroservice.com> wrote in message news:<bq**********@serv1.iunet.it>...

Hi,
I have a Excel sheet that have a column with mixed data:
column can contain data like "892-234-32A" or like "892298343233432"

I need to get all data column such as "text" to avoid numeric field show as
exponential number.

I'm trying with:
SELECT CAST (CODICE as bigint(25)) FROM
OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel
8.0;IMEX=1;HDR=YES;Database=C:\tempxadp\dist1.xls' , Foglio1$)

But I get error conversion type "from nvarchar to bigint" when query meet
the alphanumeric field.

Any tips ?

Thanks in advance

Piero

Italy

Here is one possibility, which will return only integers:

select cast(CODICE as bigint)
from ...
where CODICE not like '%[^0-9]%'

bigint(25) is incorrect, by the way.

Simon