Missed the Logic on Excel Importing

[Earl Anderson]

I guess I missed the boat on the logic for this one. Immediately upon
hitting "Import" in an
attempt to import an Excel file containing 7 columns of 'txt' formatted data
into AXP, I got a "Type mismatch" error. A search of previous posts
indicated that Access did not 'like' most data types being imported except
for 'txt' files, so I saved the 'xls' as 'txt' and that didn't work either,
receiving the same "mismatch" error message. (Crudely, I can always 'cut &
paste' my data into a table I guess).

My question is if there is a "Type mismatch" on a truly text
data type, converted 'xls' file, what is Access 'matching' the data type
against since
no table (and therefore no data & structure) was ever presented as the
import destination? How can something be 'mismatched' if there is nothing
(presented) to 'match' it against?

Thx...
Earl

 

[Ken Snell \(MVP\)]

I assume that you are importing the data to a new table; I also assume that
the text data in EXCEL contain some alphanumeric strings, and also have some
strings with just numbers in them, and that the "just numbers" strings are
within the first 25 rows or so of the EXCEL file.

If my assumptions are correct, what ACCESS and Jet are doing is assuming
that the "text" data actually are numeric data, and thus all your
non-numeric text strings are "not matching" to a numeric data type.

Is this what you have in your setup?

 

[louisjohnphillips]

I assume that you are importing the data to a new table; I also assume that
the text data in EXCEL contain some alphanumeric strings, and also have some
strings with just numbers in them, and that the "just numbers" strings are
within the first 25 rows or so of the EXCEL file.

If my assumptions are correct, what ACCESS and Jet are doing is assuming
that the "text" data actually are numeric data, and thus all your
non-numeric text strings are "not matching" to a numeric data type.

Is this what you have in your setup?
--

Ken Snell
<MS ACCESS MVP>








- Show quoted text -

The problem here may be that Access is assuming too much about your
data.

It may be looking at a column that has in its first row the value of
"07/29/2007". It assumes that column should be formatted as datetime
data type. When it encounters a value of "N/A" in the same column, it
reports a data type mismatch.

The way around this is to specify the data type of each column in the
Import Spreadsheet Wizard. Look for where you can define data type in
that Wizard.

 

[Ken Snell \(MVP\)]

The way around this is to specify the data type of each column in the
Import Spreadsheet Wizard. Look for where you can define data type in
that Wizard.

Only ACCESS 2007 allows you to specify the data types when importing data
from a spreadsheet. The posted indicated that ACCESS 2002 (poster called it
AXP) is being used in this situation.

 

[Earl Anderson]

Ken,

Your assessment and assumptions are correct. The Excel table contains a
list of Federal, State and local regulations that govern my business. The
columns (fields) deal with the domain (Fed or state or local), the title or
'numbered' designation of the regulation', the part of the business affected
by the regulation, a summary of the regulation and a hyperlink field to view
the regulation (on the web). So I gather that your assessment is if one of
the columns has a number in it (i.e. "PATRIOT ACT 2002"), Access considers
the "PATRIOT ACT" as not matching the 'number data type "2002" (or vice
versa).

How do I overcome that...by removing all numeric-looking data since there's
a lot less of those than actual text and attempting another import? How can
I just tell Access to ignore data types and I will fix it later?

Thx...
Earl

 

[Ken Snell \(MVP\)]

Actually, "PATRIOT ACT 2002" will be viewed by ACCESS as text data type, not
numeric -- this is because it contains nonumeric characters. When I refer to
numeric, I mean a text string that contains only numbers or that looks like
a number:

"1003"
"1.788"
"4.56E3"
"-67"

and so on.

To assist us in debugging the problem that you're seeing, post here the
first 30 rows of data that are in the EXCEL file so that we better
understand what the data are in your file.

--

Ken Snell
<MS ACCESS MVP>