Almost every import process is complicated by import errors,
but losing even one value is usually unacceptable. If you’re dealing with only
a few records, discerning the missing value and entering it manually isn’t a
big deal. On the other hand, if the import drops hundreds of values, you’ve got
a problem.

The goal is to get Access to import all the data the first
time out, if possible. Even if the import gods favor you and everything goes
well, you’ll probably need to normalize the imported data—and that means
maintaining relationships while dividing the table into separate tables. In a
nutshell, you face two potential problems with every import task:

  • Getting
    Access to properly interpret data
  • Maintaining
    relationships while normalizing the imported data

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 more tables.

Figure A

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 sheet. For instance, if column A in the sheet for January contains
customer names, February through December should also contain customer names in
column A.

You should eliminate small inconsistencies between these
related sheets by deleting unnecessary columns or rearranging data. Be sure to
check for hidden columns—even one inconsistent column will generate an error
during the import process.

Sometimes, you can’t delete an inconsistent column because
you need to import that data as well. When this is the case, copy the data to
another sheet or range, delete the original data and column and then import the
copied data into an individual table.

Excel doesn’t make much of a distinction between column and row
headings, but Access does. In most cases, data that’s arranged from left to
right using row headings should be transposed so that the row headings become
column headings. That way, Access will use the column headings as field names. If
you import the data in its original format, Access will assign generic field
names and import the row headings as a column of data. This isn’t necessarily a
bad thing. You may actually find this arrangement beneficial in some situations.
The data is the key—there’s no right or wrong way.

If the data doesn’t include headings, enter them before
importing. This step is especially helpful if you have several consistently
formatted sheets. Otherwise, you’ll waste several minutes opening each Access
table in Design view and renaming each field. Let Excel and Access do as much
of the grunt work as possible.

Preparing the Excel data

Access does a good job of interpreting Excel data that
originates in Excel. However, data that originates somewhere else and
subsequently finds its way into an Excel file you must then import often causes
trouble. That’s because the data’s original format is (most likely) unknown to
Access. Access will do its best, but it often fails.

A failure is defined as a data type that’s converted
incorrectly or a value that isn’t imported. Incorrectly converted data types
are a problem that Access won’t identify, so they can be troublesome. Import
errors—values that Access can’t import—are logged in a special table during the
import process. Use this table to find those errors. If there are numerous
errors, you may have to tweak the Excel data and import again. Or you may
choose to manually enter the values yourself, if there are only a few.

While the data is still in Excel, you can make a few changes
that will ensure the import’s success and make the data easier to work with
once it’s in Access. First, delete empty rows. This might seem unnecessary, but
an empty record may contain phantom characters that confuse Access and generate
an import error. Within this context, phantom characters are usually mistakes
or errors left over from latent systems that the current application can’t
properly interpret and handle. (Not all phantom characters cause problems.) Eliminating
empty rows will ensure that they don’t have a chance to generate an error.
Besides, you don’t want empty records in your Access tables. Missing values can
play havoc with domain calculations and other criteria expressions. Sometimes,
Access inserts blank records before or after the actual rows of data. To avoid
this problem, export ranges instead of sheets whenever possible.

Resolve any known data type conflicts before importing.
There are only a few between Excel and Access, so there’s little danger of
losing data or precision when importing numeric data that originated in Excel.
There are three exceptions to this rule:

  • Excel
    doesn’t have a bit data type. If the Excel entry is a numeric TRUE or
    FALSE value, Access correctly assigns the Yes/No data type. A series of -1
    and 0 values are assigned a Number data type.
  • Access
    won’t import error values. Instead, Access inserts a Null value and logs a
    type conversion import error. If possible, you’ll want to clear any error
    values before importing.
  • Extremely
    long text fields that don’t originate in Excel might generate import
    errors if the text is extraordinarily long or if it contains phantom
    characters (which are usually the real source of the problem).

The quick and easy way: delimited text files

Unexpected problems can arise when using Excel as a
go-between for a foreign format that Access doesn’t recognize. All bets are off
when the data doesn’t originate in Excel. Despite your best efforts, you may
experience import errors you can’t resolve.

If you encounter problems when importing data in a foreign
format, dump the format altogether. Export the data to a delimited text file
and import the text file. Access will let you decide each column’s data type
and field size, as shown in Figure B.
All this control enables you to completely bypass many import problems. In
fact, this method is so stable, you may find you prefer to work with text files
exclusively—especially if you don’t know the data’s original format.

Figure B

The wizard lets you specify data types when importing data via a text file.

This method has one limitation: Excel won’t let you save a
multisheet file to a single text file. When the Excel file contains multiple
sheets, save each sheet as a separate text file and then import the files
individually. You can still append the files into one table during the import
process.

Normalizing the imported data

After importing data, you’ll probably need to normalize it. You
could spend a lot of time writing just the right queries to get the job done,
but try the Table Analyzer first. This helpful feature will divide the data into
separate tables and maintain relationships between related data. Unfortunately,
the previously imported data (Figure A) isn’t normalized, and there’s no simple
way to get from this familiar Excel format to the properly normalized table
shown in Figure C. You can re-create
the Excel sheet in database terms by creating a list, if the sheet is simple
enough. Or you can import the data and use queries to rework the structure,
which can be extremely time-consuming and difficult. Or you can work with the
unnormalized data.

Figure C

Some imported data will break normalizing rules.

Some imported data normalizes much better. Lists normalize
easily and often a sheet is really just a series of lists, such as the list of
articles topics shown in Figure D.
After importing this list, run the Table Analyzer to normalize it. Select the
table in the Database window and then choose Analyzer from the Tools menu. From
the subsequent submenu, select Table. Click the Next button twice, as the first
two panels contain instructions. In the third panel, select the table that contains
the article data and click Next. Choose the Yes, Let The Wizard Decide option,
and click Next. The wizard will then open the window shown in Figure E. This represents the wizard’s best
efforts at normalizing the data and maintaining the relationships by dividing
the data into separate tables and then adding the appropriate foreign key
values to the original table. You can see from this short demonstration how
useful the Table Analyzer can be when normalizing imported data.

Figure D

Some Excel data normalizes easily.

Figure E

The Table Analyzer divides data and maintains relationships.

Import success

Importing foreign data into Access is often a best-guess undertaking
that requires hands-on knowledge of the process. Even then, getting it right
the first time can be a challenge. Knowing and preparing the data before
importing can significantly reduce problems.

Susan Sales Harkins is an independent consultant and the
author of several articles and books on database and Web technologies. Her most
recent books are
Automating Microsoft
Access 2003 with VBA, Upgrader’s
Guide to Microsoft Office System 2003, ICDL
Practice Questions Exam Cram, ICDL Exam Cram 2, Absolute Beginner’s Guide to Microsoft Access 2003, and Absolute Beginner’s Guide to Microsoft
Access 2002 with Mike Gunderloy, all published by Que. Currently, Susan
volunteers as the publications director for Database Advisors
.

Subscribe to the Developer Insider Newsletter

From the hottest programming languages to commentary on the Linux OS, get the developer and open source news and tips you need to know. Delivered Tuesdays and Thursdays

Subscribe to the Developer Insider Newsletter

From the hottest programming languages to commentary on the Linux OS, get the developer and open source news and tips you need to know. Delivered Tuesdays and Thursdays