Importing Excel Data to Access

  1. To format data in an Excel spreadsheet, you need to make sure the data is in list format. This means that each column has a label in the first row and contains similar facts, and there are no blank rows or columns within the list. Then, close the Excel workbook that contains the data you want to use in Access.

  2. In Access, open the database (if you already have one) or create a new database where you want to copy the Excel data.

  3. On the Access File menu, point to Get External Data, and then click Import.

    4. figure20 Figure 20

  4. In the Import dialogue box, click Microsoft Excel in the Files of type box. Then locate the file you want to import and double-click the file.

    5. figure21 Figure 21

  5. After you select the spreadsheet to import, you'll be presented with a new screen asking you to select the Worksheet or Named Range that you wish to import. In the example here, there is only one worksheet. Select the worksheet and click Next.

    6. figure22 Figure 22

  6. Specify whether column headings are present. Often, Excel users utilize the first row of their spread sheet to provide column names for their data. In our example file, we did this to identify Author_ID, etc. In the window shown below, ensure that the First Row Contains Column Headings box is checked. This will tell Access to treat the first row as names, rather than actual data to be stored in the list. Click the Next button to continue.

    7. figure23 Figure 23

  7. Choose a destination type. If you're working with a brand-new database, you'll want to check the In a New Table radio box. If you are importing data into an existing database, you could specify the destination table here. Click the Next button to continue.

    8. figure24 Figure 24

  8. Create any desired indices. Database indices are an internal mechanism that can be used to increase the speed at which Access can find information in your database. You can apply an index to one or more of your database columns at this step. Simply click the Indexed pull-down menu and select the appropriate option.

    9. figure25 Figure 25

  9. Select a primary key. You can either let Access generate a primary key for you, or you can define one of the fields in your worksheet as primary key.

    10. figure26 Figure 26

  10. The last step is to name your table and click Finish. A dialogue box will appear to inform you that your spreadsheet has been imported successfully.

    11. figure27 Figure 27

    figure28 Figure 28

  11. Return to the database window; you will find the new table a part of your database now.

    12. figure29 Figure 29

next section >