Access 2002 left out numeric values in text fields in xls file

  • Thread starter Thread starter Lee
  • Start date Start date
L

Lee

When I tried to read in a xls file as a recordset by using
opendatabase method, I found that none of numeric values
were read in from a text column in an excel 2002 file. A
bizarre thing was that a user of mine read in the data
with text values left out. Has anyone come across such a
problem? Please shed some light on it. Thanks in advance.
 
Hi Lee,

I've never had to use OpenDatabase on an Excel worksheet so haven't come
across this precise issue. However similar things are common when
importing or linking to Excel.

Excel cells and columns don't have data types - any cell can store
anything - so there's actually no such thing as a "text column", only a
column of cells formatted as text or/and containing only text values.

Access assigns field types by looking at the first few rows of the Excel
data. It applies different rules in different circumstances: for
instance when importing (TransferSpreadsheet acImport) it will create a
text field if there's a single text value in the first few cells in that
column, but when linking (TransferSpreadsheet acLink) it only does so if
all the first few cells in the column contain text values (not numeric
values formatted as text). So it's possible for different data to
produce different results

If you search this newsgroup (e.g. with Google's advanced newsgroup
search) you'll find more information, including something about registry
settings that control how many rows Access examines when deciding data
types.
 
John Nurick wrote ...
Excel cells and columns don't have data types - any cell can store
anything - so there's actually no such thing as a "text column", only a
column of cells formatted as text or/and containing only text values.
Click to expand...

That depends on your perspective. What you have said applies to the
Excel UI. Even then, you are not entirely correct: you can limit the
data that can be added to an Excel column in the Excel UI using data
validation functionality. In Excel, select a column and from the menu
choose: Data, Validation, 'Settings' tab, drop the 'Allow' dropdown to
show the data types.

Take Jet out of the equation and saying, 'Excel cells and columns
don't have data types' is a bit like saying, 'MS Access doesn't
contain any data' ;-)

When Excel is used as a Jet data source, (and surely that's the
correct perspective here) columns *must* have a data type and 'text'
is one of the data types Jet can choose to assign. BTW in case you are
wondering, the aforementioned Data Validation in the Excel UI does not
apply when using Excel as a Jet data source.
Access assigns field types
Click to expand...

No, Jet does that part of the process
If you search this newsgroup
you'll find more information, including something about registry
settings
Click to expand...

Try here:

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

Jamie.

--
 
Back
Top