Importing data from Excel to Access can be a little dicey, often resulting in missing information, incorrectly converted values, or data that's difficult to work with. These pointers will help you prepare your data and sidestep common import problems.
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.
|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.
|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.
|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.
|Some Excel data normalizes easily.|
|The Table Analyzer divides data and maintains relationships.|
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.