Importing Excel Data to Access
- 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.
- In Access, open the database (if you already have one) or create a new database where you want to copy the Excel data.
- On the Access File menu, point to Get External Data, and then click Import.
- 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.
- 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.
- 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.
- 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.
- 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.
- 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.
- 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.
- Return to the database window; you will find the new table a part of your database now.