Importing a Spreadsheet

To import a spreadsheet into an Access database, do the following:

1. Open the Access database that will receive the spreadsheet. If that database is already open, close any open objects so that you see only the Navigation pane.
2. On the External Data tab, in the Import & Link group, click the Excel command to open the Get External Data - Excel Spreadsheet dialog box shown. Note, if you do not have any tables in your database, you won't see the second option to append a copy of the records to one of the tables in your database.
3. Click Browse to open the File Open dialog box shown. Select the folder and the name of the spreadsheet file that you want to import and click Open to return to the Get External Data - Excel Spreadsheet dialog box.
4. Make sure the Import The Source Data Into A New Table In The Current Database option is selected and then click OK. If your spreadsheet is from Excel version 5.0 or later, it can contain multiple worksheets. If the spreadsheet contains multiple worksheets or any named ranges, Access shows you the first window of the Import Spreadsheet Wizard, as shown in the following illustration. (If you want to import a range that isn't yet defined, exit the wizard, open your spreadsheet to define a name for the range you want, save the spreadsheet, and then restart the import process in Access.) Select the worksheet or the named range that you want to import, and click Next to continue.
5. After you select a worksheet or a named range, or if your spreadsheet file contains only a single worksheet, the wizard displays.
   Select the First Row Contains Column Headings check box if you've placed names at the tops of the columns in your spreadsheet. Click Next to go to the next step.
6. On the next page, you can scroll left and right to the various fields and tell the wizard which fields should be indexed in the new table. Your indexing choices are identical to the ones you'll find for the Indexed property of a table field in Design view. You can also correct the data type of the field. In this case, for the ID field, select Yes (No Duplicates) from the Indexed list and select Long Integer from the Data Type list and for the Zip field, select Yes (Duplicates OK).
   As you move from field to field, the Data Type box displays the data type that the wizard chooses for each field (based on the data it finds in the first few rows). If what you see here is incorrect, click the arrow and select the correct data type from the list. Access 2010 allows you to select the correct data type on this page of the Import Spreadsheet Wizard. You can also choose to eliminate certain columns that you don't want to appear in the final table. For example, it's quite common to have intervening blank columns to control spacing in a spreadsheet that you print. You can eliminate blank columns by scrolling to them and selecting the Do Not Import Field (Skip) check box. Click Next to go to the next step.
7. On the next page, you can designate a field as the primary key of the new table. If you want, you can tell the wizard to build an ID field for you that uses the AutoNumber data type. (It so happens that this sample spreadsheet already has a numeric ID field that we'll attempt to use as the primary key.) If multiple fields form a unique value for the primary key, you can tell the wizard not to create a primary key. Later, you can open the resulting table in Design view to set the primary key.
8. Click Next to go to the final page of the wizard, where you can change the name of your new table. (The Import Spreadsheet Wizard uses the name of the spreadsheet or the named range you chose in step 4.) You can also select the option to start the Table Analyzer Wizard to analyze your new table. If you enter the name of an existing table, Access asks if you want to replace the old table.
9. Click Finish on the last page to import your data. Access opens a dialog box that indicates the result of the import procedure. If the procedure is successful, the new table will have the name you entered in the last step. If you asked to create a new table and Access found errors, you will find a new table that has the name of the import table you specified with a $_ImportErrors suffix. If you asked to append the data to an existing table and Access found errors, you can choose to complete the import with errors or go back to the wizard to attempt to fix the problem (such as incorrectly defined columns). You might need to exit the wizard and correct data in the original spreadsheet file, as noted in the following section.

Fixing Errors

If you were to import that spreadsheet, Access would first display an error message. This indicates that the wizard found a problem with the column that you designated as the primary key. If you have duplicate values, the wizard will also inform you. When the wizard encounters any problems with the primary key column, it imports your data but does not define a primary key. This gives you a chance to correct the data in the table and then define the primary key yourself.

In addition, if the wizard has any problems with data conversion. Access displays this message at the top of the Save Import Steps page of the Get External Data - Excel Spreadsheet dialog box if it encounters data conversion errors while importing a spreadsheet.

Note that if your import was successful, you might want to select the Save Import Steps check box before you click Close if you might run the exact same import again in the future. You can find all saved imports by clicking the Saved Imports button in the Import & Link group on the External Data tab.

When the Import Spreadsheet Wizard has problems with data conversion, it creates an import errors table in your database (with the name of the spreadsheet in the title) that contains a record for each error. Notice that the table lists not only the type of error but also the field and row in the spreadsheet in which the error occurred. In this case, it lists the one row in the source spreadsheet that contains the Canadian postal code. The row number listed is the relative row number in the source spreadsheet, not the record number in the resulting table. You can correct some of the errors in the table in Design view.