Importing an Excel File into Access

Before an Excel file can be imported, it must be in the appropriate format. You can only import information that is a contiguous table.

1. Open Excel and prepare the file in a database format.
2. In this format the top row will be the row headings, or field names.
3. Either clear off any cells that you do not wish to import or copy the ones that you do to another worksheet.
   
4. Save the file.
   
5. Remember the name of the worksheet and the file and its location.

 

Access 2007 & 2010

Access 2003

Access 2007 & 2010

1. On the Home Ribbon click on the External Data tab.
2. In the Import group select Excel.
3. Select the source of the data by clicking the appropriate radio button.
4. If the Excel file contains more than one worksheet you will be asked to select the one you want.
5. On the next screen, click "First Row Contains Column Headings."
6. Click Next if you wish to change some of the headings.
7. Click Finish.
8. If you get some error messages, see #10 below.

 

 

 

Access 2003

1. Open Access
2. Either create a new database, or open an existing one, depending on where you wish to place the Excel file.
3. On the menu click on File > Get External Data > Import
4. On the bottom of the Import window drop down the window next to "Files of type:" and select Microsoft Excel.
5. If the name of the file you wish to import is not visible in the import window, browse to the folder where it is located.
6. Click on it to select it.
7. Click on import in the lower right corner of the screen.
8. Select the worksheet from the pop up window. (Or if you have named a range click on that on the top and select it.)
9. Click next.
10. If you get a message that the first row contains some data that can't be used for valid field names click OK to let the wizard assign valid field names. (You can clean this up later.)
11. If the box "First Row Contains Headings" is not checked, check it. (Assuming of course that this is so. If not, Access will name the fields, Field 1, Field 2, etc.)
12. Click Next.
13. Select "In a new table" and click next.
14. To change the name of a field while importing
    1. Select the field whose name you wish to change by clicking anywhere in that column,
    2. Type a new name in the selected box on the pop-up window.
    3. Continue until all field names are named as you desire.
    4. Click Next.
15. Make a choice about the primary key as per the window and click Next
16. Enter the name you wish for this table and click Finish.
17. You will receive a message that Access has finished importing the file. Click OK. (If the Excel file was not set up properly, and you get an error message, open up Excel and prepare the file as in Step 1.)
18. If there were blank lines in the Excel file you may wish to delete these "records."

Created November 20, 2011