Convert an Excel spreadsheet to an Access 2010 database

Contents
  1. Prepare your data
  2. Create a new Access 2010 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

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 here. Or maybe you keep your address book (breathless!) from shredded paper.
It’s time to keep 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.


Remark † This guide is for Access 2010. If you are using Access 2010, read Convert an Excel spreadsheet to an Access 2007 database. If you are using Access 2013, read Convert Excel to an Access 2013 database.

Create a new Access 2010 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. Press the button Overview 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 desired file and option, click the button Okay Get on.

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. This forces Access to treat the first row as names rather than the actual data stored in the contact list. Press the button Further Get on.

Create any indexes you want

Database indexes are an internal mechanism that you can use to increase the speed at 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 drop down menu indexed ” 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 will 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 Duplicate OK )” from the “Indexed” drop-down menu. Click Further Get on.

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. Choose an option Allow access add primary key “and press Further Get on.

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 on the button Finished

View your data

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

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 into Access!

Leave a Reply

Your email address will not be published.