Home and Learn: Microsoft Access Course
Import Excel file into Microsoft Access
Microsoft Access lets you import files from a wide variety of sources to turn into a database table. What we'll do first is to import data from an Excel spreadsheet and turn it into an Access table. In the next lesson, you'll turn a CSV file into a database table.
First, download the spreadsheet in the link below (right-click the link and select Save As):
This is product information. We're selling Widgets in a range of colors. (The CSV file will be the sales data.) We'll then link the two together in a relationship.
Save the above Excel file to your own computer. The Excel Workbook should look like this, if you were to open it:
Now that we have our Excel workbook, let's import it into Microsoft Access.
Create a new blank database in Access. Call it WidgetSales.accdb.
When the database loads, click on External Data at the top of Access. From the Import & Link tab, notice that there is an item called New Data Source:
Click on New Data Source, then select From File > Excel from the menu that appears:
You should see the following dialog box appear when you select From File > Excel:
The first thing to do is to click the Browse button at the top and locate the spreadsheet you downloaded.
Next, you need to select one of the three import options. The one already selected is to Import the source data into a new table in the current database. We'll leave it on this default. But the other two options are intriguing. You can Append data to a database you already have, or you can link to the data source. Linking to a data source means that if we were to update, say, the prices in Excel, then this update will filter through to Access. (The other direction, an update in Access won't update your Excel spreadsheet.)
There is only one worksheet in the workbook, Widgets. If you had more than one, Access would show you the worksheets and ask you to choose one. (You can only import one worksheet at a time.) But the screen you'll be taken to looks like this:
This step is asking if the first row in your spreadsheet contains headings. If so, Access will use these headings as field names. The Widget worksheet does indeed contain headings. Make sure the option is selected and click Next. You'll see this:
This step in the Wizard is a little trickier. But what you do here is to select each column and tell Access what type of data the column contains.
Access has highlighted the first column, ProductID. In the Field Options at the top, it has the Field Name we want. The Data Type is set to Double. We'd like Integers here, so click the dropdown and change Double to Integer.
The Indexed option says, Yes (Duplicates OK). However, we want to use the ProductID column as a Primary Key. You can't have duplicates in a Primary Key column, so change this to Yes (No Duplicates).
The final option, Do not import Field (skip), is OK unchecked. But if you have a column that you don't want to appear in your Access database, make sure to check this column.
Now select the WidgetColor column. The Field options will change:
These options are all OK, so you can leave them. But they should be these values:
Field Name: WidgetColor
Data Type: Short Text
Indexed: No
Do not import: unchecked
Now select the BuyPrice column:
The Data Type is wrong for this column. Change it from Double to Currency. Your options should be these:
Field Name: BuyPrice
Data Type: Currency
Indexed: No
Do not import: unchecked
Select the TotalPrice column to see its field options:
Again, the Data Type needs changing from Double to Currency. So you should have these Field Options:
Field Name: TotalPrice
Data Type: Currency
Indexed: No
Do not import: unchecked
Click Next when you've made the changes.
The next screen is where you set a Primary Key. Access has added one for us. But we already have one we'd like to use, which is the ProductID column. Select the option, Choose my own primary key. In the dropdown list, make sure ProductID is selected:
Click Next to see the penultimate screen:
This is where you choose a name for your table. It's OK on Widgets, so click Finish. There will be one more screen asking you if you want to save the import steps. Leave the option unchecked and click Close. Access will create the table for you and add it to the All Access Objects area on the left.
Double click the table name to open it up. You should see all the information from the spreadsheet in your new table:
(Close the default table close Table1 and it will disappear from the area on the left.)
Now let's see how to import a CSV file to use as an Access database.
<-- Previous Lesson: Access Reports | Next Lesson: Import CSV file -->
<--Back to the Microsoft ACCESS Contents Page