Convert an Excel spreadsheet to an Access 2007 database

After sending out your Christmas cards last year, did you promise to organize your mailing list next year to make the process easier? Have a huge Excel spreadsheet that you can’t make heads or tails of? Your address book may look something like the one in the file below. Or maybe you keep your address book on (gasping!) scraps of paper.

Content
  1. Prepare your data
  2. Create a new Access 2007 database
  3. Start the import process in Excel
  4. Choose source and destination
  5. Select column headings
  6. Create any indexes you want
  7. Choose primary key
  8. Name your table
  9. View your data

Prepare your data

It’s time to deliver on that promise to yourself: organize your contact list in a Microsoft Access database. It is much easier than you might imagine and you will definitely be happy with the results. This tutorial will take you step by step through the entire process.
If you don’t have your own spreadsheet and want to follow the tutorial, you can download the sample Excel file used to create the tutorial.

This guide is for Access 2007. If you are using Access 2010, read Convert Excel to an Access 2010 database. If you’re using Access 2013, read Convert Excel to Access 2013 Database.

Create a new Access 2007 database

If you don’t have an existing database that you use to store contact information, you probably want to create a database from scratch. To do this, click the Empty Database icon on the Getting Started with Microsoft Office Access screen. You will see the above screen. Give your database a name, click the “Create” button and you’re good to go.

Start the import process in Excel

Then click the External Data tab at the top of the Lock screen and double click the Excel button to start the Excel import process. The position of this button is indicated by the red arrow in the image above.

Choose source and destination

You will then see the above screen. Click the Browse button and navigate to the file you want to import. Once you have found the correct file, click the “Open” button.

  • Link your database to an Excel sheet, so that changes to the original sheet are reflected in the database.

  • Import data into an existing database table

After selecting the correct file and option, click the OK button to continue.

Select column headings

Often, Microsoft Excel users use the first row of their spreadsheet to specify column names for their data. In our sample file, we did this to identify the columns Last Name, First Name, Address, etc. In the above window, make sure the “First row contains column headers” box is checked. As a result, Access treats the first row as names rather than the actual data stored in the Contacts list. Click the Next button to continue.

Create any indexes you want

Database indexes are an internal mechanism that you can use to increase the speed with which Access can find information in your database. At this point, you can apply an index to one or more database columns. Just click on the “Indexed” drop-down menu and select the appropriate option.

Keep in mind that indexes create a large overhead for your database and increase disk space usage. For this reason, you want indexed columns to be minimal. In our database, we usually search by the last name of our contacts, so let’s create an index on this field. We may have friends with the same last name, so we want to allow duplication here. Make sure the Last Name column is selected at the bottom of the window, then select “Yes (Duplicates OK)” from the “Indexed” drop-down menu. Click ‘next’ to continue.

Choose primary key

The primary key is used to uniquely identify records in a database. The easiest way to do this is to let Access generate the primary key for you. Select “Allow access to add primary key” and click Next to continue. If you are interested in choosing your own primary key, you can read our article on database keys.

Name your table

You must name Access to refer to your table. We will call our table “Contacts”. Enter this in the appropriate field and click Finish.

View your data

You will see an intermediate screen asking if you want to save the steps for importing your data. If not, click Close.

You will then be returned to the main database screen where you can view your data by simply double clicking the table name in the left pane. Congratulations, you have successfully imported your data from Excel to Access!

Leave a Reply

Your email address will not be published.