importing from a named range in a spreadsheet

  • Thread starter Thread starter Mark
  • Start date Start date
M

Mark

i am trying to import a range from a spreadsheet but am
getting errors on a particular field as the default field
type assigned is double, but in fact the data in the
field contains alphanumeric data.

my problem is that the dialog box in the wizard has the
field type selection greyed out even though the help
module tells me i should be able to change it here.

any help much appreciated.

mark
 
Since Excel is not a database, using "mixed" data types in a column causes a
problem when Access has to "guess" which one to use. Access examines the
first 15 rows (or so) and makes a guess. If you want to "force" Access to
guess a text data type then make the first entry in that column a text
value. The reason you get errors is that the first 15 rows are all numeric
and then the text data shows up after Access has already guessed the data
type to be Numeric.

The way to make a text value in your data of numbers is to include a
character!
e.g. 123A

========================================================
Other good advice from John Nurick:
The Access routine that imports Excel data doesn't allow direct control
over the types of the fields it creates, and often runs into trouble
with Excel columns that contain a mix of numeric and text values.

You can work round this in any of the following ways:

1) create the table yourself with the field types you need, then import
the spreadsheet data. The field names in the table must exactly match
the column headings in Excel.

2) make sure that at least one row near the top of the Excel table
contains values that can only be interpreted as the data types you need
(e.g. text that cannot be interpreted as a number if you want the column
to become a text field). Sometimes the simplest way to do this is to
insert a first row of "dummy" data into Excel just for this, and then
delete it from the Access table once the data has been imported.

3) Access assigns field types on the basis of the data it finds in the
first dozen or so rows of the spreadsheet table. It pays no attention to
cell formats. Sometimes a useful trick is to put an apostrophe ' in
front of numeric values in the cells (e.g. '999): this forces Excel and
Access to treat them as text, but the apostrophe is not displayed in
Excel or imported into Access.

(Just to make things more confusing, the Access applies different rules
when you're linking Excel data rather than importing it. Simplifying
somewhat: when importing, any text value in the first few rows will
cause a field to be imported as text. When linking, any *numeric* value
in the first few rows will cause a field to be linked as numeric even if
all the other values are non-numeric.)

3) Write your own import code using Automation to get the values direct
from the worksheet cells and recordset operations or queries to append
them into your table.

===========================================================

How many rows does Access use to guess the data type?
Comments from Newsgroup:

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.
=================================================================
 
Joe Fallon said:
Other good advice <snip>
Click to expand...

Joe, I've changed my notes. In future please use the update version as
follows:

==============================
Check your registry settings. 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. 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.

ImportMixedTypes='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. There's a bias in favor
of numeric in the event of a tie. 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:

Jet (MS Access UI): 'Text' data type
DDL: VARCHAR(255)
ADO: adWChar ('a null-terminated Unicode character string')

Note that this is distinct from:

Jet (MS Access UI): 'Memo' data type
DDL: N/A
ADO: adLongVarWChar ('a long null-terminated Unicode string value')

ImportMixedTypes=Text will curtail text at 255 characters as 'Memo' is
cast as 'Text'. For a column to be recognized as 'Memo', majority type
must be detected, meaning the majority of rows detected must contain
255 or more characters.

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.

In summary, use TypeGuessRows to get Jet to detect whether a 'mixed
types' situation exists or use it to 'trick' Jet into detecting a
certaint data type as being the majority type. In the event of a
'mixed types' situation being detected, use ImportMixedTypes to tell
Jet to either use the majority type or coerce all values as 'Text'
(max 255 characters).

--
 
Back
Top