Import from excel

[sbcglobal]

I have code like this to import from excel into access:

SELECT INDEX, ...DM
FROM [Excel 8.0; Extended
Properties="";IMEX=1;HDR=Yes;DATABASE=C:\Data\Invoice.xls].[BASE$]

problem is: field DM (as diameter) has both number and text (like N/A when
it doesn't exist)..under windows registry, the setup allow Access to look up
1st 20 cells of the col and then it decide the column is 'number'...which I
don't want. I want to import it as text.

I like some expert opinion on how to maneuver this in vba or sql query...NOT
from excel side (like to insert rows or reset excel sheet value..)

I have tried: str(DM) as DM, but it wont' work since function str only work
with well-formated values. And formatting DM column as text won't work
either. Access take it as number (double) anyway. I also tried more
complicated way, like IIF(Is numeric(....)), won't work either.

Generally speaking, is it possible to control Access to take value as Text
(or any other format), independend of what the 'True' format of data
resource????

Many thanks@@!!

 

[Andreas]

Top of my head, not verified:
Try using CStr, which should handle the region specific formatting of
numbers correctly when converting to text.

Regards,
Andreas

 

[sbcglobal]

Thank you Andreas, but Cstr won't work, because it convert number to string,
but if I have text in column, like "aaa", then it return error.

Top of my head, not verified:
Try using CStr, which should handle the region specific formatting of
numbers correctly when converting to text.

Regards,
Andreas

I have code like this to import from excel into access:

SELECT INDEX, ...DM
FROM [Excel 8.0; Extended
Properties="";IMEX=1;HDR=Yes;DATABASE=C:\Data\Invoice.xls].[BASE$]

problem is: field DM (as diameter) has both number and text (like N/A
when it doesn't exist)..under windows registry, the setup allow Access to
look up 1st 20 cells of the col and then it decide the column is
'number'...which I don't want. I want to import it as text.

I like some expert opinion on how to maneuver this in vba or sql
query...NOT from excel side (like to insert rows or reset excel sheet
value..)

I have tried: str(DM) as DM, but it wont' work since function str only
work with well-formated values. And formatting DM column as text won't
work either. Access take it as number (double) anyway. I also tried more
complicated way, like IIF(Is numeric(....)), won't work either.

Generally speaking, is it possible to control Access to take value as
Text (or any other format), independend of what the 'True' format of data
resource????

Many thanks@@!!

 

[Andreas]

Use an IIF function to test for datatype:

IIF(IsNumeric([DM]),CStr([DM]),[DM])

Regards,
Andreas

Thank you Andreas, but Cstr won't work, because it convert number to string,
but if I have text in column, like "aaa", then it return error.

Top of my head, not verified:
Try using CStr, which should handle the region specific formatting of
numbers correctly when converting to text.

Regards,
Andreas

I have code like this to import from excel into access:

SELECT INDEX, ...DM
FROM [Excel 8.0; Extended
Properties="";IMEX=1;HDR=Yes;DATABASE=C:\Data\Invoice.xls].[BASE$]

problem is: field DM (as diameter) has both number and text (like N/A
when it doesn't exist)..under windows registry, the setup allow Access to
look up 1st 20 cells of the col and then it decide the column is
'number'...which I don't want. I want to import it as text.

I like some expert opinion on how to maneuver this in vba or sql
query...NOT from excel side (like to insert rows or reset excel sheet
value..)

I have tried: str(DM) as DM, but it wont' work since function str only
work with well-formated values. And formatting DM column as text won't
work either. Access take it as number (double) anyway. I also tried more
complicated way, like IIF(Is numeric(....)), won't work either.

Generally speaking, is it possible to control Access to take value as
Text (or any other format), independend of what the 'True' format of data
resource????

Many thanks@@!!

 

[Jamie Collins]

I have code like this to import from excel into access:

SELECT INDEX, ...DM
FROM [Excel 8.0; Extended
Properties="";IMEX=1;HDR=Yes;DATABASE=C:\Data\Invoice.xls].[BASE$]

problem is: field DM (as diameter) has both number and text (like N/A when
it doesn't exist)..under windows registry, the setup allow Access to look up
1st 20 cells of the col and then it decide the column is 'number'...which I
don't want. I want to import it as text.

I like some expert opinion on how to maneuver this in vba or sql query...NOT
from excel side (like to insert rows or reset excel sheet value..)

In lieu of an expert...

You have a good understanding but you haven't quite grasped that it is
*Excel* that does the scanning of rows to determine a data type
(actually, it is Jet but Jet on the Excel side, rather than Jet on the
MS Access side, so it's easier to think in terms of Excel doing the
scanning). Once Excel has decided and coerced and/or nulled values as
required, it is a done deal.

So you seem to be asking, is there anything you can do in MS Access to
influence Excel's choice of data type? The straight answer is, no.

You are clued up about using IMEX=1 and the registry keys; you may even
have read this:

http://www.dicks-blog.com/archives/2004/06/03/external-data-mixed-data-types/

In case you haven't already, try changing TypeGuessRows from your
current setting of 20 to zero. This should force Excel to scan all rows
to determine whether a mixed types situation exists; if it does it will
determine the data type as Text (or the value of the ImportMixedTypes
key if different).

Jamie.

--