Importing from Excel to Access

  • Thread starter Thread starter yolanda
  • Start date Start date
Y

yolanda

I am coming up with errors when I'm importing from Excel
to Access. The error message is "field truncation." The
way I saved my Excel Sheet is in Tab Delimited so that
all my data comes through, otherwise, most of my data is
eliminated. What do I need to do so that all my data
comes through and I receive no more errors? All
suggestions appreciated.
 
You can go through your Access table, and make sure the lengths of each
field are bigger than the biggest piece of data that needs to fit there.

Also, if you're using the "import" tool, you may try copying/pasting
directly into your table, or vice versa.

One more thing, you can try pasting one record (row in Excel) at a time into
your access table. I did that once, and found that only one of the records
was keeping all the data from being pasted in.
 
yolanda said:
I am coming up with errors when I'm importing from Excel
to Access. The error message is "field truncation." The
way I saved my Excel Sheet is in Tab Delimited so that
all my data comes through, otherwise, most of my data is
eliminated. What do I need to do so that all my data
comes through and I receive no more errors? All
suggestions appreciated.
Click to expand...

You should be able to do this direectly from the Excel workbook. The
trick is to get Jet to recognize your Excel column/field as data type
'Memo'.

Standard answer number 4, amended for the MS Access user:

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 when '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 256 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