• Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint
Share this Page URL

Chapter 17. Generating Web Pages from Ta... > Importing Data from HTML Tables

Importing Data from HTML Tables

Access 2000 includes the capability to import or link data from an HTML table to a Jet 4.0 table. This feature appears to have been included in Access 2000 for HTML symmetry; apparently, the theory is that if you can export to HTML, you should also be able to import from HTML. Few Access users are likely to make use of this feature because relatively little useful, public domain (not copyrighted) tabular data is available on the Internet. For completeness, however, following is an example of importing data from an HTML page created in the section "Exporting Table and Query Datasheets to HTML" earlier in this chapter:

  1. Choose File, Get External Data, Import to open the Import dialog. Navigate to the folder in which you stored Suppliers.html and select HTML Documents (*.html, *.htm) in the Files of Type dropdown list (see Figure 17.18).

    Figure 17.18. Displaying .html and .htm files in the Import dialog.

  2. Select the Suppliers.html file you created in the "Exporting Table and Query Datasheets to HTML" section at the beginning of this chapter and click the Import button to close the Import dialog and open the first Import HTML Wizard dialog.

  3. The Wizard imports the table header data, if present, together with the table data. The formatted version of Suppliers.html includes headers, so check the First Row Contains Column Headings check box (see Figure 17.19).

  4. Click the Advanced button to open the Suppliers Import Specification dialog. This dialog lets you customize import operations on date and time fields and select the decimal symbol. You can change the field names, data types, and indexing for each of the fields, as well as skip the import of specific fields. Change the Data Type of the PostalCode column from Long Integer to Text. The Supplier ID field is the primary key, so specify a No Duplicates index on the field (see Figure 17.20). Data types and indexes for the remaining fields are satisfactory.

    Figure 17.19. Specifying column headers in the Import HTML Wizard's first dialog.

    Figure 17.20. Specifying field data types and indexes in the Suppliers Import Specification dialog.

    Tip #152 from

    Always set the data type of postal code fields to text. If the first few rows contain U.S. (5-digit numeric) ZIP codes, the Wizard assumes that all postal codes are numeric and assigns the Long Integer data type. You receive Import Errors messages when the Wizard encounters an alphanumeric postal code, such as those used in Canada and the U.K. Other countries sometimes prefix a alphabetic country-code abbreviation, such as S-15151 (Sweden).


    Alternatively, you can specify field data types and indexing in the third Wizard dialog. The Wizard proposes to add Duplicates OK indexes on any field that contains ID or Code in the column name.

    If you encounter unexpected import errors see the "HTML Table Import Errors" topic of the "Troubleshooting" section near the end of the chapter.

  5. Click the Save As button to save the Import Specification. Edit or type a new name for the specification in the Specification Name text box of the Save Import/Export Specification dialog (see Figure 17.21). Click OK to close the dialog, and then click OK to close the Suppliers Import Specification dialog.

    Figure 17.21. Editing the default name for the saved Suppliers Import Specification.

  6. Click Next to display the second Import HTML Wizard dialog. Select the In a New Table option to store the data in a new table whose name you specify at the end of the import process (see Figure 17.22). Click Next.

    Figure 17.22. Specifying import of the tabular HTML data to a new table.


    If your HTML table includes date fields, added combo and text boxes let you specify date formats. You can select a period or comma as the decimal symbol if the table includes real numbers.

  7. You can make last-minute changes to field names, data types, and indexes in the third Wizard dialog (see Figure 17.23). If you made the required changes to the Suppliers Import Specification dialog in step 4, click Next.

    Figure 17.23. A second chance to change field names, data types, and indexes offered by the third Wizard dialog.

  8. By default, the Wizard proposes adding a numeric primary key field to the table. The Supplier ID field qualifies as a primary key, so select the Choose My Own Primary Key option and pick the Supplier ID field in the dropdown list (see Figure 17.24). Click Next.

    Figure 17.24. Selecting the Supplier ID field as the primary key for the imported table.

  9. There is a Suppliers table in Northwind.mdb, so edit the proposed table name to tblSuppliers (see Figure 17.25). You don't need to analyze the Suppliers table, so don't mark the I Would Like a Wizard to Analyze check box. Click Finish to export the HTML table to tblSuppliers and terminate the Wizard.

    Figure 17.25. Editing the name of the new table in the final Wizard dialog.


    If data in your source HTML table violates primary key integrity or has rows with data type conflicts, the Wizard generates an import errors table. If an import errors table is present, check its contents to determine the source of the problem(s).

  10. Open the tblSuppliers table to verify the import wizardry. The table is essentially identical to the original Suppliers table from which the Web page was created (see Figure 17.26). The most significant differences are the field data type of the Supplier ID column (Long Integer, not AutoNumber) and the field names (derived from the Caption property of the original table).



Not a subscriber?

Start A Free Trial

  • Creative Edge
  • Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint