Import Excel Data To Access
The mechanical steps of moving Excel and Access data back and forth are
many, and most of them are quite simple. Whether you use Tools > Office
Links, File > Open, Files of Type, Import/Export under the File menu or just
good old copy and paste, the mechanics are not complicated.
See our article
in the June 2004 issue for more details on the mechanics. In this issue,
we’d like to mention some of the common problems that happen during the
import/export process.
Excel is very accommodating when it comes to data types. Numbers and dates
can get converted to text fields if Excel fails to recognize them as a
number or date. For instance, February 30 or Aplir 10 are not dates. If
these are mistakenly entered as dates, Excel doesn’t give a warning.
Spreadsheets that have been used for storing data over a long period of time
typically have a wide variety of data type errors; the letter O has been
typed for the number 0 or units have been typed into a number field like ‘3
pieces’ instead of just 3.
When you move data into a brand new table in Access, the wizard analyzes the
data being imported. Some portion of the data is sampled to determine data
type. The new table is constructed using this analysis. So, Access builds a
table with a field called Sales that it guesses is currency. It creates a
field called CustomerName that it guesses is text. Then it looks at the
field called InvoiceDate and notices February 30. It knows that February 30
is not a date and therefore assumes that the data is text. If one record is
text, the whole field is text! Arrgghhh!
Again, no warning message is issued as no problem has occurred as far as
Excel and Access are concerned. You must check your new table in design view
as soon as you have imported it. In the picture here, we can see that both
InvoiceDate and CreditLimit have been changed to text fields. It is very
unlikely that fields with those names are text!
(continued...)
Are You Ready For Office 365?
Office 365 is
- Email and calendaring powered by Microsoft Exchange
- Office Web Apps - the office productivity apps you already know, tailored for the
web
- Websites powered by Microsoft SharePoint both for internal-facing intranet and customer-facing
website
- Instant Messaging and Online Meetings powered by Microsoft Lync
- All with a financially-backed 99.9% uptime guarantee
Sign up for a free trial:
Your information will not be shared with any other party. You must provide
a valid email in order to recieve your login information and starting information
kit.
Watch
this video to see how Office 365 can help your business.
You must go back to the source Excel file and
track down the offending
entries. Once your data has been scrubbed (expect a half-day for every year of
data entries in Excel… just a rule of thumb, but never underestimate the
amount of time spent scrubbing bad data!), delete the table in Access,
re-import the Excel data and recheck the table in design view.
One other spectacular import problem that gives no warning: in all versions
of Access, a field name that begins with a space is a huge No-No. If an
Excel spreadsheet has been formatted by someone who didn’t know how to use
the ‘Align Center’ button then it probably contains spaces before the name.
The spaces are invisible to us humans and so the field name appears to be a
normal center-aligned title. Access sees the spaces though, and Access
freaks out.
The problem is, it doesn’t tell you what the real problem is! It just says:
“An error occurred trying to import file 'C:\BadData.xls'. The file was not
imported.” If you get this error, check your source file titles and make
sure they have no leading spaces. It’s hard to see, so look closely.
We’ll have more to say about converting spreadsheets to databases in our
next issue. If you have issues in the meantime, don't hesitate to contact
us.
|
|