Software

Avoid import glitches when bringing Excel data into Access

Importing data from Excel to Access can be somewhat dicey. 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

Note: This information is based on the article Techniques for successfully importing Excel data into Access. It's also available as a PDF download.

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.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

8 comments
MsPinkflipflops
MsPinkflipflops

Thank You Thank You Thank You Thank You Thank You~~~ My ICT exam is the day after tomorrow, you simply saved me...

Answers
Answers

I use Excel's TRIM function to remove extra spaces and the Text to Columns to split data eg into FirstName LastName. I also find that checking date fields in Excel gives a much better result after import.

lauenld
lauenld

The biggest problem with importing to ACCESS from EXCEL is where the column has a mixture of numeric values and alphanumeric texts. If the first 25 rows has numeric values, then ACCESS treats this column as numeric field. Then if subsequent rows has alpha text, these values will be ignored. I get around this by creating a new column and use the TEXT function to assure all values are treated as text.

staffordd
staffordd

Hello, I was very happy (and surprised) to see an article on this topic. It's about time. I haven't completed reading the piece yet, but I wanted to say, I've been dealing with import/export issues between Excel and Access for about 13 years now, and that includes getting a multitude of different types of files into Access - various delimited files, fixed width files, and so on. So if anyone is struggling, and Susan's excellent article did not answer your question, I MIGHT be able to help - feel free to email me, and if I can, I will. I used to experience a lot of frustration with this, and there are some very simple precautions you can take to minimise unwanted conversions. But I mainly just wanted to express my delighted SURPRISE to even SEE an article on this topic - one of the things that most people never talk about or deal with, and I can see for myself that many, many people just use the import wizard, import the data, and NEVER LOOK AT IT - and often, more often than not, the move from Excel to Access has damaged some or most of the data. And you don't always get an IMPORT ERROR when there is a problem, either - you can't count on that. I suppose for me, it goes back to a very basic concept that is so, so often overlooked - you have to SPEND TIME actually LOOKING AT the data. Most people "are too busy" or think, "that's why I have these tools, the import wizard will take care of it". But Microsoft maybe should have given us some proper disclaimers about import/export wizards and maybe they should have been a little more upfront about just how difficult it can be to get Excel and Access to talk. Anyway, if anyone else is interested, or you have a current difficulty with importing, drop me a line - staffordd@stirling.gov.uk. I will try to help if time permits, and if I know the answer, which I may or may not! Even with many years experience, I approach each new import task as a potential disaster waiting for me to avert it by exercising skill and care in ensuring that the data quality is NOT compromised. That's why I am here. I think data quality is paramount, it's our data that MATTERS, so I become almost...resentful when a program such as Excel "decides" when something is a date (when it isn't) or decides to reformat something just because it feels like it !! What a nerve ! Stick with TAB DELIMITED TEXT FILES for input. If I have one primary rule, that is it. DO NOT USE COMMA QUOTE DELIMITED or CSV or EXCEL files - use a TAB DELIMITED TEXT FILE. None/Tab to be precise. Happy importing ! Dave :-)

lkeppel
lkeppel

I import tons of data from Excel into Access and rarely have a problem. My general process is to first clean and format the Excel data. I then create a table and define the fields prior to importing. This almost always ensures that I do not have a problem with data types.

Marshwiggle
Marshwiggle

... she used "comprise" correctly, as in, the whole comprises (not 'is comprised of') the parts. That would be enough to get her flunked out of most modern schools of journalism. :-o

ssharkins
ssharkins

Although the basics are the same, the possibilities are so huge that it is difficult to find common ground with importing/exporting. I think that's why you see so little information about it, and that's why I chose to take the route I took when writing this article -- it's about basics that everyone can apply rather than specifics that will apply to only a few.

Tony Hopkinson
Tony Hopkinson

The bots will hook it and spam you to Hades and back. TR has peer mail, if you enable it, you can be mailed by any member, without them knowing your address. You can then choose whether or not you want to help some one with an access import or move some money out of Nigeria. Not sure tabs instrad of commas matters though, it might to the inherrent preference conversions in the software. You hit the nail on the head in terms of data quality, in the source file though. Nothing more likely to prove the GiGo law than a data import.

Editor's Picks