Issues getting data from Excel to Access (2002)

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,

I have several Excel spreadsheets. One column is numeric. I have set it to
numeric through the "Format Cells...". When I try to import it, either from
the excel workbook or a tab delimited file, the numeric values are eith:

1. arbitrarily excluded (some get in, others don't)
2. arbitrarily quoted (same thing as above)

It needs to be numeric. That is why the number keeys were used to enter the
data and why the poor users chose to use Excel.

Questions -

1. How can we get Excel to recognize number as number?
2. How can we get Access to recognize numbers as numbers?

Now that I have asked questions for which no useful answer will likely be
forthcoming (in at least a timely manner), might I offer a few comments?

This is a straight forward business function. I have been working with
computers for a long long time. Some of the worst times have been spent
working with Microsoft solutions for users (the dev tools are mostly pretty
good). I have to wonder why these problems are found in Release code? Why
am I sitting here, looking at a column of numbers that are not being treated
like numbers?
 
One approach might be to import whatever you can get into a temporary table
in Access, then run queries that "coerce" the data into a final data
type/format in more permanent tables. I use "tables" because Excel data is
usually "flat", where a well-normalized Access application uses relational
data (table) structures.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Actually, that was pretty close. I change the column to Text and loaded the
rows. Used a Find and replace to remove quotation marks ( one at a time.
The command would not get both in the same 'cell'. Had to redefine the data
type a couple of times because Access kept truncating the cents.

I conditioned the Excel data into a nicely normalized table on each sheet
and followed what anyone would have called due diligence in preparing the
sheets for import. The frustrating thing was the indeterminate behavior of
Access. There was absolutely no condition that was useful in predicting or
controlling the behavior. That may be acceptable for some types of fantasy
AI but NOT for a database or spreadsheet application.
 
BTW - Thanks for your response.

Jeff Boyce said:
One approach might be to import whatever you can get into a temporary table
in Access, then run queries that "coerce" the data into a final data
type/format in more permanent tables. I use "tables" because Excel data is
usually "flat", where a well-normalized Access application uses relational
data (table) structures.

Regards

Jeff Boyce
Microsoft Office/Access MVP
Click to expand...
 
Actually, I was recommending doing the work inside of Access, importing all
the "raw" Excel data first.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Back
Top