I have a file in Excel, and someone wants me to send it to them in Access. I don’t want to re-type everything. Is there any way to automatically transfer everything from Excel to Access?
Take a look at Excel. Now take a look at Access. A spreadsheet and a database are not the same thing, but there are some similarities between the two. For starters, do you notice how they both display data in a grid format? For two programs that appear to have so much in common, there must be a way to automatically transfer data between the two, right? Right. You can pump data from Microsoft Excel into an Access database using Access’ Get External Data feature.
This article applies specifically to Microsoft Office 2003. The general process described here may also apply to earlier versions such as Office 97, 2000, and XP.
Start the Import
Open up an existing Access database or create a new one, then click File → Get External Data → Import. The File menu is the first option on the top left portion of the MS Access window.
Locate Your Excel Spreadsheet
In the Import window that has appeared, change “Files of Type” to Excel, then locate your spreadsheet, click on it, and click “Import”. The “Import Spreadsheet Wizard” will appear.
Import Spreadsheet Wizard, Step 1: Locate your data
The Import Spreadsheet Wizard will ask you from which portion of your spreadsheet you would like to import data. You may select either a worksheet or a named range. Select the portion of the spreadsheet that contains your data, then click “Next”.
Import Spreadsheet Wizard, Step 2: Column headings
Next, you will be asked whether or not the first row of your spreadsheet contains column headings. If so, check the box labeled “First Row Contains Column Headings”. Click “Next”.
Import Spreadsheet Wizard, Step 3: Choose a destination for your data
You can import the data from your spreadsheet into a new table, or one that already exists. If one already exists, its design should match the column layout of your spreadsheet. If you have not already prepared a table for your data, simply click “Next”. If you have, check off “In an existing table”, and select that table’s name from the drop-down list.
Import Spreadsheet Wizard, Step 4: Fine-tune Columns
Step 4 allows you to fine tune the data types of each column in your spreadsheet. Access usually does a pretty good job of guessing these data types, however you might want to make a few minor changes such as ensuring postal code columns are “text” instead of “numeric” (otherwise you will lose any leading zeros). You may also rename and exclude columns during this step. When you are finished fine-tuning your columns, click “Next”.
Import Spreadsheet Wizard, Step 5: Primary Key Column
At this point, Access will ask whether or not it should add a primary key column. A primary key column contains a value that uniquely identifies each row in a table. The primary key column can be thought of as an “id” column — a good example of a set of primary keys is an incremental series of numbers. Check off yes, no, or choose an existing column to act as the primary key. Click “Next”.
Import Spreadsheet Wizard, Final Step
This is the home stretch. If you are importing your data into a new table, give that table a name. Click “Finish”, and Access will transfer your data from the spreadsheet into the new database.
