Linking Excel Worksheets to Access, Type Code Conversion Issue

[Guest]

When linking Excel Tables to Access, is there a way to force Access to accept
or change Field Codes. I have a Field I need for it to interpret as Text,
but it keeps setting the field as a Number Field. I cannot run update
queries on the data because it tells me the Type codes don't match.
Importing this table directly is not an option. I need it as a linked table.
I am using Office 2000 for NT. Thanks

 

[G. Vaught]

Ensure your column in Excel is formatted as Text. It may be necessary to
place the ' in front of each entry. If you have numbers in the field, Access
will automatically assume numbers, unless you specifically assigned the data
as a text entry.

 

[Jamie Collins]

Ensure your column in Excel is formatted as Text. It may be necessary to
place the ' in front of each entry. If you have numbers in the field, Access
will automatically assume numbers, unless you specifically assigned the data
as a text entry.

For details on how Jet determines Excel data types, see:

http://www.dicks-blog.com/excel/2004/06/external_data_m.html

In brief, data type is determined based on the majority type of the
rows scanned, with numeric (Double) breaking ties only. To say that
having numbers automatically makes the data type numeric is incorrect.

BTW linked tables and Excel data types are both (separate) Jet
processes, not MS Access processes.

Jamie.

--

 

[Guest]

Hi G Vaught,

Thanks for your suggestion. I may use it for smaller spreadsheets. However,
the spreadsheet I am linking to has over 10k records and your suggestion of
adding a ' in front of the numbers and letters although might work, it simply
isn't feasable for me to maintain it in that fasion. The spreadsheet changes
daily which is why I need it linked. Also, changing the Column to a "Text"
format in the spreadsheet is ignored by Access. (I tried that several times
already...) It simply doesn't recognize that format coding, hence my
continued issue.

 

[Guest]

Hi Jamie,

Thanks for the reply. You are correct. Access and Excel cannot seem to
share Type Codes. So no matter what I have the Format Code set for a Column
in the spreadsheet, Access interprets it depending on the number of entries
that are numeric in nature as opposed to Alpha-Numeric or straight lettered
text. I found a trick which is a temporary work around (for others who may
be experiencing the same issue.) I created about 20 bogus records at the top
of the spreadsheet and in the column which contains the numbers I need for
Access to read as text, I placed "AAAAA" in that field. When I relinked the
spreadsheet, Access finally interpreted the field as Text instead of numbers
and I was able to run my update queries unabated. Good info, but not exactly
the fix I am looking for. Strange no one else has asked MS about this
issue.. I certainly can't be the only one having it!
______________________________

 

[Jamie Collins]

You are correct. Access and Excel cannot seem to
share Type Codes.

By 'Type Codes' do you mean data types? When Excel is used as a data
source its data types are a subset of Jet's (MS Access's) data types,
so actually they *do* share data types.

So no matter what I have the Format Code set for a Column
in the spreadsheet, Access interprets it depending on the number of entries
that are numeric in nature as opposed to Alpha-Numeric or straight lettered
text.

You've made a couple of incorrect assumptions there.

First, you can format the column to be of data type Text. This is
easily demonstrated by creating an Excel table of with a column of
type Text, inserting numeric values, then querying the data and
testing its data type e.g. (execute these statements individually):

CREATE TABLE
[Excel 8.0;HDR=YES;Database=C:\test.xls;].MyExcelTable
(MyTextCol VARCHAR(255) NULL)
;
INSERT INTO
[Excel 8.0;HDR=YES;Database=C:\test.xls;].MyExcelTable
(MyTextCol) VALUES (1.23)
;
INSERT INTO
[Excel 8.0;HDR=YES;Database=C:\test.xls;].MyExcelTable
(MyTextCol) VALUES (4.56)
;
INSERT INTO
[Excel 8.0;HDR=YES;Database=C:\test.xls;].MyExcelTable
(MyTextCol) VALUES (-78)
;
SELECT MyTextCol, TYPENAME(MyTextCol)
FROM
[Excel 8.0;HDR=YES;Database=C:\test.xls;].MyExcelTable
;

Second, MS Access is not interpreting the data type. The data type is
determined by Jet on the Excel side before it gets to MS Access
(perhaps more correctly to say, before it gets to Jet on the MS Access
side). If you were querying Excel data from, say, SQL Server, Jet
would determine the Excel data type before the data reached SQL
Server, so there's no point 'blaming' SQL Server in these
circumstances.

I found a trick which is a temporary work around
I created about 20 bogus records at the top
of the spreadsheet

not exactly
the fix I am looking for.

In the link I posted, it discusses the registry keys Jet uses to scan
the rows and resolve a mixed types situation. It should be clear (but
I admit, it may not be) that the registry settings may be changed in
your favor. In brief, set the registry key value TypeGuessRows to 0
(zero) and ensure the ImportMixedTypes reg key is Text (the default
value). All rows will be scanned and it will take just one value with
a different data type for a mixed types situation to be detected and
the column will be 'seen' as Text.

Strange no one else has asked MS about this
issue.. I certainly can't be the only one having it!

I see questions about this issue in these newsgroups practically every
day. I'm not sure asking MS would get you anywhere. It is most
definitely a design feature rather than a bug. A different design
choice was made for text files, where a schema.ini file (or import
specs for MS Access UI?) may be used to explicitly specify data type.

MS decided to do things differently for Excel, which is a shame
because the schema.ini file approach works well IMO. I think the
consensus is that MS will not be further developing Jet, other than to
address security issues.

Jamie.

--