Importing Spreadsheet Data

Access 2007 also lets you import data from spreadsheet files created by Lotus 1-2-3, Lotus 1–2-3 for Windows, and Microsoft Excel version 3 and later. You can specify a portion of a spreadsheet or the entire spreadsheet file to import into a new table or to append to an existing table. If the first row of cells contains names suitable for field names in the resulting Access table, as shown in the Companies.xlsx spreadsheet in Figure 6–3, you can tell Access to use these names for your fields.

Figure 6–3: The data in the first row of this Excel spreadsheet can be used as field names when you import the spreadsheet into a new Access table.

Preparing a Spreadsheet

Access 2007 determines the data type for the fields in a new table based on the values it finds in the first few rows of data being imported (excluding the first row if that row contains field names). When you import a spreadsheet into a new table, Access stores alphanumeric data as the Text data type with an entry length of 255 characters, numeric data as the Number type with the Field Size property set to Double, numeric data with currency formatting as the Currency type, and any date or time data as the Date/Time type. If Access finds a mixture of data in any column in the first few rows, it imports that column as the Text data type.

Inside Out-Importing to a Temporary Table First 

If you want to append all or part of a spreadsheet to a target table, you should import or link the entire spreadsheet as a new table and then use an append query to edit the data and move it to the table you want to update. You can learn about append queries in Chapter 9, “Modifying Data with Action Queries.”

If the first several rows are not representative of all the data in your spreadsheet (excluding a potential field names row), you might want to insert a single “dummy” row at the beginning of your spreadsheet with data values that establish the data type you want to use for each column. You can easily delete that row from the table after you import the spreadsheet. For example, if you scroll down in the Companies.xlsx sample spreadsheet shown in Figure 6–3, you’ll find that the last entry is a Canadian address, as shown in Figure 6–4.

Figure 6–4: The Zip field entry contains data that can’t be stored in numeric format.

Because Access sees only numbers in the first few rows of the Zip column, it will use a Number data type for the Zip field. However, the entry for the Canadian address has letters and spaces, which requires the field to be defined as text. As you’ll see later, if you attempt to import this spreadsheet without fixing this problem, Access generates an error for each row that contains nonnumeric data. Access sets the contents of fields it cannot import to Null. You can solve this by inserting a dummy row at the top with the proper data types in each column, moving the row to the top, or fixing the one bad row after you import the file.

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 group, click the Excel command to open the Get External Data-Excel Spreadsheet dialog box shown next. (If you want to import a Lotus 1-2-3 file, on the External Data tab, in the Import group, click More and then click Lotus 1-2-3 File. Because you can only import a Lotus 1-2-3 file, you won’t see the options to append or link the data.)

   

3. Click the Browse button to open the File Open dialog box shown on page 262. 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. If you want to follow along with this example, select the Companies.xlsx file from the companion CD.

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 page 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 the following page.

   

   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, as shown here, 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. Previous versions of Access would not allow you to change the data type here, but Access 2007 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. See Chapter 4, “Creating Your Database and Tables,” for details about the Table Analyzer Wizard. 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

In “Preparing a Spreadsheet” on page 274, you learned that Access 2007 determines data types for the fields in a new table based on the values it finds in the first several rows being imported from a spreadsheet. Figures 6–3 and 6–4 show a spreadsheet whose first few rows would generate a wrong data type for the Zip column in a new Access table. The Number data type that Access would generate for that field, based on the first several entries, would not work for the last row, which contains character data. In addition, one of the rows has a duplicate value in the ID column. If you attempt to use this column as the primary key when you import the spreadsheet, you’ll get an additional error.

If you were to import that spreadsheet, Access would first display an error message similar to the one shown in Figure 6–5. 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.

Figure 6–5: Access displays this error message when it encounters a problem with your primary key values.

In addition, if the wizard has any problems with data conversion, it displays a message similar to the one shown in Figure 6–6.

Figure 6–6: 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 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. Figure 6–7 shows the import errors table that Access creates when you import the spreadsheet shown in Figure 6–3. 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.

Figure 6–7: Here is the import errors table that results from importing the spreadsheet shown in Figure 6–3.

Figure 6–8 shows the table that results from importing the spreadsheet shown in Figure 6–3. You can find one row that has no entry in the Zip column. If you switch to Design view, you can see that the Import Spreadsheet Wizard selected the Number data type for the Zip field. If you want to be able to store values that include letters, the Zip field must be a text field. Notice that in Design view there is no primary key defined.

Figure 6–8: After importing the spreadsheet shown in Figure 6–3, one row is missing a postal code entry, and there is a duplicate value in the ID column.

You can correct some of the errors in the table in Design view. For example, you can change the data type of the Zip field to Text (and perhaps change the name to PostalCode), save the table, and then enter the missing value. For the row that has a duplicate ID (16), you can switch to Datasheet view and either delete one of the rows or supply a unique value. You can then set ID as the primary key in Design view.