Importing and Linking Spreadsheet Files
Access can import files created by spreadsheet and related applications, such as project management systems, in the following formats:
Excel 2.x, 3.0, 4.0, 5.0, and 95 .xls files, and task and resource files created by Microsoft Project in .xls format.
Lotus 1-2-3 .wks (Release 1 and Symphony), .wk1 (Release 2), and .wk3 (Release 3 and later) files. Most spreadsheet applications can export files to at least one of these Lotus formats.
You can use OLE to embed or link charts created by Microsoft Excel and stored in files with an .xlc extension. Copy the contents of the file to the Windows Clipboard from Excel. Then choose Edit, Paste to embed or link (via OLE) the chart in fields of the OLE Object type, and then display the chart on a form or print it on a report as an unbound object. Similarly, you can embed or link views displayed in Project for Windows 3.0 or 4.0, which also uses the Microsoft Chart applet, except task and resource forms and the Task PERT chart. Chapters 20 and 21 describe OLE linking and embedding techniques.
Creating a Table by Importing an Excel Worksheet
Figure 7.20 illustrates the preferred format for exporting data from Excel and other spreadsheet applications to Access tables. Most spreadsheet applications refer to the format as a database. The names of the fields entered in the first row and the remainder of the database range consist of data. The type of data in each column must be consistent within the database range you select.
Figure 7.20. Data from the Orders table in a Microsoft Excel 97 worksheet.
Caution
All cells that comprise the worksheet range to be imported into an Access table must have frozen values. Frozen values substitute numeric results for the Excel expressions used to create the values. When cells include formulas, Access imports the cells as blank data cells. Freezing values causes Access to overwrite the formulas in the spreadsheet with the frozen values. If the range to import includes formulas, save a copy of your .xls file with a new name. By using the worksheet window with the new file name, select the range to import and freeze the values by choosing Edit, Copy, or by pressing Ctrl+C. Choose Edit, Paste Special, select the Values option, and click OK. Save the new spreadsheet by its new name and use this file to import the data. The later section Using the Clipboard to Import Data presents an alternative to this procedure.
Tip
You get an opportunity to assign field names to the columns in the worksheet during the importation process, although it's easier if you add field names as column headings first.
To prepare the data in an Excel spreadsheet for importation into an Access table, follow these steps:
1. | Launch Excel, and then open the .xls file that contains the data you want to import. |
2. | Add field names above the first row of the data you plan to export (if you haven't done so). Field names cannot include periods (.), exclamation points (!), or square brackets ([]). You cannot have duplicate field names. If you include improper characters in field names or use duplicate field names, you see an error message when you attempt to import the worksheet. |
3. | If your worksheet contains cells with data you don't want to include in the imported table, select the range that contains the field names row and all the rows of data needed for the table. In Excel, choose Insert, Name, Define and name the range. |
4. | If the worksheet cells include expressions, freeze the values as described in the caution that precedes these steps. |
5. | Save the Excel file (use a different file name if you froze values), and exit Excel to conserve Windows resources for Access if your computer has less than 8M of memory. |
Now you are ready to import worksheets from the Excel workbook file. To import the prepared data from an Excel spreadsheet into an Access table, follow these steps:
1. | Launch Access, if it's not running, and open the database to which you want to add the new table. The Database window must be active before you can import a file. |
2. | Choose File, Get External Data, Import in Access to open the Import dialog (see Figure 7.21). The Files of Type drop-down list provides several more formats for importing tables than it provides for linking files. |
3. | Select Microsoft Excel (*.xls) in the Files of Type drop-down list, and then double-click the name of the Excel workbook that contains the spreadsheet you want to import (you also can click the file name to select it, and then click Import). Access now invokes the Import Spreadsheet Wizard (see Figure 7.22). |
4. | If you're importing an entire worksheet, select the Show Worksheets option; if you're importing a named range, select the Show Named Ranges option. The Import Spreadsheet Wizard lists the worksheets or named data ranges, depending on the option you select in the list box in the upper-right corner of the wizard's opening dialog. |
5. | Select the worksheet or the named data range that you want to import in the list box. The Import Spreadsheet Wizard shows a sample view of the data in the worksheet or named range at the bottom of the dialog. Figure 7.21. Choosing Excel and Lotus 1-2-3 spreadsheet formats in the Import dialog.
Figure 7.22. The opening dialog of the Import Spreadsheet Wizard.
|
6. | Click Next to move to the next step of the Spreadsheet Import Wizard. The wizard displays the dialog shown in Figure 7.23. |
7. | If the first row of your spreadsheet data contains the field names for the imported table, select the First Row Contains Column Headings check box. Click Next to continue with the third step; the Import Spreadsheet Wizard displays the dialog shown in Figure 7.24. |
8. | If you want to create a new table to hold the imported spreadsheet data, select the In a New Table option. To add the imported data to an existing table, select the In an Existing Table option and select the table you want to add the imported data to in the drop-down list. Click Next to continue with the fourth step; the Import Spreadsheet Wizard displays the dialog shown in Figure 7.25. Figure 7.23. Selecting whether the first row of imported data contains field names.
Figure 7.24. Choosing whether to create a new table from the imported data or to add it to an existing table.
Note If you elect to add the imported data to an existing table, the Import Spreadsheet Wizard skips over all intervening steps, and goes immediately to its final dialog, described in step 16. |
9. | If you want to exclude a column from the imported database, select the column by clicking it, select the Do Not Import Field (Skip) check box, and skip to step 12. |
10. | |
11. | If you want Access to index this field, choose the appropriate index type in the Indexed list box; you may choose No index, Yes (Duplicates OK), or Yes (No Duplicates). |
12. | If the data in the spreadsheet column is unformatted or is formatted as text, Access enables you to select the data type for the field in the Data Type drop-down list. The Data Type control is disabled in Figure 7.25 because the cells in the selected column of the Excel worksheet have a Double number format; Access recognizes the Double format and automatically selects a Double data type for this field. |
13. | Repeat steps 9–12 for each column in the worksheet or data range that you import. When you are satisfied with your options for each column, click Next to move to the next dialog in the Import Spreadsheet Wizard (see Figure 7.26). Tip Use an existing field column in the worksheet for a primary key field if the column contains only unique values. In Figure 7.26, the OrderID field is known to contain unique values. |
14. | Select the Let Access add Primary Key option to have Access add an AutoNumber field to the imported table; Access fills in a unique number for each existing row in the worksheet that you're importing. Select the Choose My Own Primary Key option and select the primary key field in the drop-down list if you know you can use a column in the worksheet or data range as a primary key for the imported table. If this imported table doesn't need a primary key, select the No Primary Key option. ▸▸See Using Access 97's Table Analyzer Wizard Figure 7.26. Selecting a primary key for the new table.
|
15. | Click Next to move to the final dialog of the Import Spreadsheet Wizard (see Figure 7.27). Type the name of the new table in the Import to Table text box; Access uses the name of the worksheet or data range as the default table name. If you want to use the Table Analyzer Wizard to split the imported table into two or more related tables, select the check box labeled I Would Like the Wizard to Analyze the Structure of My Table After It Finishes Importing the Data. (You can use the Table Analyzer Wizard at any time on any table by choosing Tools, Analyze, Table.) |
16. | Click Finish to complete the importing process. Access closes the Import Spreadsheet Wizard and imports the data. When Access completes the import process without errors, it displays the dialog shown in Figure 7.28. |
Figure 7.27. Giving the new table a name in the Import Spreadsheet Wizard's final dialog.
Figure 7.28. The message displayed by the Import Spreadsheet Wizard after successfully importing a spreadsheet.
Access analyzes, approximately, the first 20 rows of the spreadsheet you are importing and assigns data types to the imported fields based on this analysis. If every cell in a column has a numeric or date value, the columns convert to Number and Date/Time field data types, respectively. If a column contains mixed text and numbers, Access converts the column as a text field. If, however, a column contains numeric data in the first 20 rows (the rows that Access analyzes) and then has one or more text entries, Access does not convert these rows.
If Access encounters cell values that it cannot convert to the data type that it assigned to the imported field, Access creates an Import Errors table with one record for each error. You can review this table, select the records in which the errors are reported, and fix them. A better approach, however, is to correct the cells in the spreadsheet, resave the file, and import the corrected data.
Note
The Import Spreadsheet Wizard doesn't display an error message when it encounters inconsistent field data types; it just creates the Import Errors table. You must look in the Database window to see if the Import Errors table is present. After you resolve the import errors, make sure that you delete the Import Errors table so that you can more easily tell whether there are errors the next time you import a spreadsheet or other external file.
The Database window now contains a new table with the name you entered in the final step of the Import Spreadsheet Wizard. If you import another file with the same name as your spreadsheet file name, Access adds the number 1 to the file name.
To verify that you obtained the desired result, double-click the name of the imported table in the Database window to display the new table in Datasheet view. Figure 7.29 illustrates a portion of the Access table created from the USA Orders worksheet in the USAOrders.xls spreadsheet file of Figure 7.20.
To display the .xls file data types that Access chose, click the Design View toolbar button. Figure 7.30 shows the structure of the new USA Orders table.
After you successfully import the table, you may want to change the properties of the fields. Unlike the procedure with linked files, Access places no restrictions on altering the field properties of imported files. Later, the section Modifying Linked and Imported Tables discusses how to change the default field data types of tables created by imported files.
Figure 7.29. The imported Excel worksheet data in an Access table.
Figure 7.30. The structure of the imported USA Orders table based on Excel data types.
Linking an Excel Worksheet with the ODBC API
Office 97 installs 32-bit Excel, FoxPro, Paradox, Access, dBASE, and Text file ODBC drivers, plus the SQL Server driver. ODBC and the ODBC drivers are installed if you choose a Complete Microsoft Office 97 or Access 97 setup, but you must explicitly specify installation of the SQL Server driver during the setup process. If you don't have the ODBC drivers on your system, you can rerun Setup to install them. Additional ODBC drivers are available from various third-party vendors.
You can use the ODBC drivers provided with Office 97 to link Excel worksheets to your Access databases. Linking an Excel worksheet has the advantage of providing up-to-date information if the data in the worksheet is subject to periodic updates. You can use the 32-bit ODBC Manager application supplied with Access 97 (and installed into the Windows 95 and Windows NT 4.0 Control Panel) to create an ODBC data source from your Excel worksheet. You also can link Excel worksheets directly to a database by using ODBC directly from Access. Data source is a synonym for database when you use the ODBC API to link tables. (In Windows NT, the ODBC Manager is referred to as the ODBC Administrator.)
▸▸See Understanding ODBC Drivers
Note
To link data in an Excel worksheet, you must create a named data range for the worksheet data you want to link. Usually, you should make sure that the worksheet you link via ODBC meets all the requirements for a worksheet that you intend to import, as described earlier. That is, make sure that the data in the worksheet is laid out in a tabular format. You don't, however, have to freeze values for an ODBC-linked worksheet.
Linking an Excel worksheet via ODBC is similar to the spreadsheet importing and linking you learned about earlier. Assuming that you installed the ODBC Manager and ODBC drivers, follow these steps to link an Excel worksheet to an Access database via ODBC:
1. | Launch Access and open a database, if necessary. Choose File, Get External Data, Link Table to display the Link dialog (refer to Figure 7.1). |
2. | Select ODBC Databases in the Files of Type drop-down list. Access closes the Link dialog and displays the Select Data Source dialog (see Figure 7.31). Figure 7.31. Selecting an ODBC data source in the Select Data Source dialog.
|
3. | Click the Machine Data Source tab to select a previously defined ODBC data source or to connect to another data source. (You use the File Data Source page to specify a data source file that you can share with other users.) ▸▸See Creating a File Data Source Within Access 97 |
4. | If you previously defined an ODBC data source for the worksheet you want to link, select its name in the list. Otherwise, click New to display the first dialog of the Create New Data Source Wizard (see Figure 7.32). Figure 7.32. The opening dialog of the Create New Data Source Wizard.
|
5. | Select the User Data Source option if you want the new data source to be available only to you. If you want anyone who logs on to your computer to be able to use your new data source, select the System Data Source option. Click Next to continue with the second dialog of the Create New Data Source Wizard (see Figure 7.33). Figure 7.33. Selecting a database driver for an Excel worksheet.
|
6. | Select the database driver appropriate for the database you want to link. For an Excel worksheet, select Microsoft Excel Driver (*.xls). Click Next to display the wizard's final dialog. |
7. | The final dialog displays summary information about the type of data source you have created (system or user) and the name of the database driver you selected (see Figure 7.34). Click Finish to complete the creation of your new data source. Figure 7.34. The Create New Data Source Wizard's final dialog.
|
8. | After creating a new data source, you must configure it. Access now displays the ODBC Microsoft Excel Setup dialog (see Figure 7.35). Enter a name for your data source in the Data Source Name text box, and enter a description of the data source in the Description text box. Figure 7.35. Configuring a new Excel ODBC data source.
|
9. | Click Select Workbook to display the Select Workbook dialog, shown in Figure 7.36. The Select Workbook dialog is a Windows 3. x-style file opening dialog. If needed, use the Drives and Directories lists to navigate to the correct folder in which your workbook is stored and select its name in the Database Name list. Click OK; Access closes the Select Workbook dialog and adds the new data source to the list in the Select Data Source dialog. Figure 7.36. Choosing the workbook for the new data source in the Select Workbook dialog.
|
10. | Select your new data source in the Select Data Source dialog's list (refer to Figure 7.31) and then click OK. Access opens the Link Tables dialog (see Figure 7.37). |
11. | The Link Tables dialog lists the named data ranges and worksheets in the workbook. Click a worksheet or range name to select it. After you select all the worksheets or ranges that you want to link, click OK. Access closes the Link Tables dialog and displays the Select Unique Record Identifier dialog (see Figure 7.38). Figure 7.37. Selecting the worksheet or named range to link to your database.
Figure 7.38. Selecting a field in the linked table to create a primary key for the linked spreadsheet.
|
12. | If you want to be able to update the data in the ODBC-linked worksheet, you must select a field (or combination of fields) that creates a unique record identification for each row in the worksheet—essentially, you create a primary key for the linked table. To select a field, click it. |
13. | After you are satisfied with your key field selection, click OK. At this point, Access formulates the SQL query to establish indexes for the linked worksheet and analyzes the data in the worksheet. Depending on the outcome of this analysis, Access either immediately links the worksheet or invokes the Link Spreadsheet Wizard. Using the Link Spreadsheet Wizard is the same as using the Import Spreadsheet Wizard described earlier, except that Access doesn't allow you to edit the field names it assigns to the linked data. |
Your linked worksheet table appears in the Database window (with the ODBC globe turned to display Africa), as shown in Figure 7.39.
Figure 7.39. Identifying tables linked with ODBC drivers by the globe icon.
Click the Open button of the Database window to display your linked worksheet. The look of the worksheet as a linked table is identical to a table imported from a worksheet with one major difference—you cannot update a linked worksheet. When you link a table to an Access database by using the ODBC API and the Microsoft ODBC drivers, you can update the table only if it has a designated primary-key field. (The linked worksheet table is represented by a read-only Recordset object of the Snapshot type.)
Note
Some third-party ODBC drivers, such as the Intersolve ODBC drivers in the ODBC Driver Pack, have an option that enables you to edit Excel worksheet and text files that you link to Access tables. For more information, see Chapter 25, "Connecting to Client/Server Databases."