Import Excel Error

  • Thread starter Thread starter Allison
  • Start date Start date
A

Allison

I linked up to a Microsoft Excel spreadsheet and when I go
into Datasheet view and open of the table I'm receiving an
NUM! error in some of the fields. I went back to excel
and checked to see how the data looked but I did not find
any errors. How would one correct an NUM! error in
Access???
 
Linking to Excel is a pill.
I never do it.

I import the data and it is hard enough to do it that way.

If you *must* link then add a dummy row of data to the spreadsheet and be
sure itis the first row.
(Access checks the first 8 or so when guessing the datatype.)
Be srue the first row is representative of the datatype.
e.g. You may have lots of numeric data in a column but way down there is
some text.
If the first 8 rows are numeric Access will guess it is a number. Then when
you get to the text it will give you the error. So in this case your dummy
row of data would have text in that column, not numbers.
 
This is one of the ideosyncracies of how ACCESS links to EXCEL data. When
you link to the spreadsheet, ACCESS decides what the format of the file is
based on the first 10 rows or so. If you have a mixture of data formats in
the column, ACCESS may choose the wrong data format for all the data in the
column. Hence, the cells with the "offending" data format will show with an
error in the linked table's datasheet view (#NUM!, #ERR!, etc.).

Short of making the data in the first 10 rows or so exactly match what is in
all the columns, the other approach is to import the data from the
spreadsheet and then use the data from a table. ACCESS uses a different
algorithm to decide data formats when it imports data than it does when it
links to data.
 
Joe Fallon said:
Linking to Excel is a pill.
I import the data and it is hard enough to do it that way.
(Access checks the first 8 or so when guessing the datatype.)
Click to expand...

As you are a MVP, can I please request you expand on your 'checks the
first 8 or so' comment and give some more details. The question is
often asked in the Excel newsgroups and I would be interested to know
exactly how this works in MS Access. When using ADO, I know which
registry settings affect the import/export and how to specify in the
connection string to ensure the registry settings are honored.
However, this isn't as explicit (to me) in MS Access and would be
grateful to learn.

Many thanks.

--
 
I received the answer from Frank Kabel on the Excel side.
All I had to do prior to linking was set up a custom
format to my range example 0000 and it worked!!!
 
Just checking back to this thread to see if Joe Fallon MVP has replied
to my request to expand on his 'checks the first 8 or so' comment (he
hasn't) and noticed that Ken Snell MVP made a similar comment, bring
'based on the first 10 rows or so'.

Can any of the above MVPs, or anyone else here, confirm how exactly
how MS Access (*specifically* MS Access, as opposed to MS Jet)
determines the number of rows to use?

Many thanks.

--
 
I believe it is a registry setting in the Jet engine.
I also believe the default value is 8.
(I used to think it was 16 but I have heard 8 more recently so....)
 
Joe Fallon said:
I believe it is a registry setting in the Jet engine.
I also believe the default value is 8.
(I used to think it was 16 but I have heard 8 more recently so....)
Click to expand...

Thanks, Joe.

I'll share what I know:

The relevant registry keys (for Jet 4.0) are in:

Hkey_Local_Machine/Software/Microsoft/Jet/4.0/Engines/Excel/

The ImportMixedTypes registry key is always read (whether it is
honored is discussed later). You can test this by changing the key to
ImportMixedTypes=OneDayWhen and trying to use the ISAM: you get the
error, 'Invalid setting in Excel key of the Engines section of the
Windows Registry.' The only valid values are:

ImportMixedTypes=Text
ImportMixedTypes=Majority Type

Data type is determined column by column. 'Majority Type' means a
certain number of rows (more on this later) in each column are scanned
and the data types are counted. Both a cell's value and format are
used to determine data type. The majority data type (i.e. the one with
the most rows) decides the overall data type for the entire column.
Rows from any minority data types found that can't be cast as the
majority data type will be returned with a null value.

For ImportMixedTypes=Text, the data type for the whole column will be
adWChar ('a null-terminated Unicode character string' i.e. Windows'
REG_SZ).

But how many rows are scanned for each column before is decided that
mixed types exist? There is a second registry Key, TypeGuessRows. This
can be a value from 0-16 (decimal). A value from 1 to 16 inclusive is
the number of rows to scan. A value of zero means all rows will be
scanned.

OK this is where Excel and MS Access part company, because I'm using
ADO in Excel and I have an explicit connection string e.g.

Provider=Microsoft.Jet.OLEDB.4.0;
Data Source=C:\ db.xls;
Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'

A setting of IMEX=1 in the connection string's extended property
determines whether the ImportMixedTypes value is honored. IMEX refers
to IMport EXport mode. There are three possible values. IMEX=0 and
IMEX=2 result in ImportMixedTypes being ignored and the default value
of 'Majority Types' is used. IMEX=1 is the only way to ensure
ImportMixedTypes=Text is honored.

I was wondering how MS Access deals with the IMEX=1 issue in the
absence of an explicit connection string. I guess that because in the
MS Access GUI you will always be in import mode so the registry key is
always honored. Can you (or anyone) confirm whether my guess is
correct?

Many thanks.

--
 
Back
Top