Import db from Excel, conversion (text) error????

[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@@!!

 

[Guest]

Sbcglobal,

I think you'll find, in general, that you're a lot better off to create the
table first using Access, where you define the data type for each field. Then
use an append query to add records to the table. This is usually more
successful than relying upon an import wizard to make the correct guesses.
So, for a table named "tblBase", with the proper fields, something like this
should work:

INSERT INTO tblBase ( [Index], DM )
SELECT [BASE$].Index, [BASE$].DM
FROM [Excel 8.0; Extended
Properties="";IMEX=1;HDR=Yes;DATABASE=C:\Data\Invoice.xls].[BASE$];

Note, however, that one of your field names, Index, is considered a reserved
word in both Access and JET. You will avoid potential headaches in the future
if you make an effort to avoid using reserved words:

Reserved Words in Microsoft Access
http://support.microsoft.com/?id=286335

List of reserved words in Jet 4.0
http://support.microsoft.com/?id=321266

Okay, I'll bite. What does "The Spoon Does Not Exist" mean?

Tom
__________________________________________

:

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@@!!

 

[sbcglobal]

Thank you Tom, very informative!
You are abs right that it's better way to append, however, due to my working
environment, I am usually passed along database in Excel format (which more
people are comfortable with)..If I append Excel to existing table, more
offen Access will drop some data due to 'conversion error'. Is there in any
way that we can control Access to read EVERYTHING, no matter the format in
Excel?

Rgds,

 

[Guest]

Not that I am aware of. I think you just need to take it on a case-by-case
basis, and create tables with fields that match the datatypes in your Excel
data. Usually, you don't have too many different formats that you need to
worry about. Perhaps your time would be better spent sending out a memo to
the other people in your office, who create these spreadsheets, to try to
stick to a pre-defined format.

Tom
___________________________________________

:

Thank you Tom, very informative!
You are abs right that it's better way to append, however, due to my working
environment, I am usually passed along database in Excel format (which more
people are comfortable with)..If I append Excel to existing table, more
offen Access will drop some data due to 'conversion error'. Is there in any
way that we can control Access to read EVERYTHING, no matter the format in
Excel?