Issues Importing Excel Data to Access
Our previous
article on the perils of transferring Excel
data to Access is one of the most widely read articles we’ve ever written. You may
want to read that one before continuing on here. In this article we’d like to talk about some issues surrounding
the use of autonumber fields in standard Access databases.
Autonumbers are commonly
used in Access databases as primary keys. In Access projects (with SQL Server on
the backend) the fields are marked as the identity data type. This article doesn’t
address the merits (or lack thereof) of using these data types, save to say that
there is always a price for convenience.
If you
do use autonumber or identity fields you can expect problems when you try
to bring in external data using copy and paste or some of the data import functions.
The issue is that these fields cannot be set – they cannot be imported since the
database itself is responsible for assigning them to a record. They also can’t be
left out because the copy/paste or import function will try to assign data to the
wrong fields.
In this example, we see that our Excel data has no headings.
When we try to import this data – through a copy and paste
append – Access attempts to paste the First Name field into the autonumber field,
Last Name into First name, etc. Since autonumber is assigned by the database itself,
we lose the first name entirely and all other fields are placed in the wrong column.
Any hidden columns in Excel are not copied, so you have
to be careful to show all of the columns that Access is expecting to be there, whether
or not they have data. If you don’t your data will be placed into the wrong column.
Name Your Columns
One solution is to name your columns! This may be a hassle,
but remember you can go to Access, copy a single row of data, insert the copied
row (Access automatically
copies the headings!) into the top of Excel and then delete the single row of database data. This
is only a few mouse clicks and you can be sure that the column headings are spelled
exactly the same as in Access.
Insert A Blank Column
If you can’t or don’t want to name the columns, another
solution is to insert a blank first column into your Excel data. This first column
will be ignored since you are using an autonumber field, and all subsequent columns
will be read correctly.
Rearrange Your Database Fields
The final solution may seem a bit strange, but for repetitive
appends to a table, it may be the best one. Go into your database and open your
table in Design View. Drag the autonumber field to the very last field. It may look
strange to you since you’re used to seeing Primary Keys in the first field, but
the database doesn’t care at all. Your database fields and Excel columns now line up and
always will. Whenever you get a new Excel dump, you can copy and paste without manipulating
the Excel spreadsheet at all.