Avoid import glitches when bringing
Excel data into Access
Accommodating Excel’s structure
A lot of foreign data comes in the form of an Excel file. Unfortunately, Excel and Access
don’t store data in the same way. That’s why importing Excel data into Access seldom
produces the exact table structure you ultimately need. Still, it’s a good idea to let the
import process properly arrange as much of the data as possible.
Importing a single sheet or range of data is straightforward. You import the entire sheet or
range into a single table and then normalize (if necessary). If the Excel file contains more
than one sheet, you must decide whether all that data belongs in separate tables or in one
inclusive table. The general rules of thumb follow:
If the sheets share a consistent purpose and structure, you’ll probably combine the
sheets or ranges into one table.
Inconsistent data, whether related or not, should be imported into separate tables.
Suppose you have a file that contains 14 sheets: a list of customers, a list of products, and
12 monthly lists of orders. The customers, the products, and the orders are all related.
Customer and products records comprise two different purposes and structures — the
data is inconsistent, even though it’s related. Consequently, you’d import the customer
and product sheets into separate tables. The 12 monthly sheets are consistent with one
another, so you’d probably import them into a single table.
Figure A shows the result of importing (as ranges) two consistently structured sheets into
the same Access table. The process yields three tables in Access: a customer table, a
products table, and an orders table. After normalizing, you would probably have several
You can combine individual sheets into one Access table during the import process.
Aiming for consistency
Sheets that contain similar data, such as order records for each month, may need some
work before Access will import them correctly. Specifically, the columns must be
consistent from sheet to shee