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

Chapter 17. Exporting Data to World Wide... > Importing Data from HTML Tables

Importing Data from HTML Tables

Access 97 includes the capability to import or link data from an HTML table to a Jet 3.5 table. This feature appears to have been included in Access 97 for HTML symmetry; the theory apparently is that if one can export to HTML, one also should be able to import from HTML. Few Access users are likely to employ this feature because relatively little useful 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:

Choose File, Get External Data,Import to open the Import dialog. Select HTML Documents (*.html, *.htm) in the Files of Type drop-down list (see Figure 17.30).

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.

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

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.31).

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

Click the Advanced button to open the Suppliers Import Specification dialog. This dialog lets you customize import operations on date and time fields and choose 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 Supplier ID field to Long Integer, and specify a No Duplicates index on the field (see Figure 17.32). Data types and indexes for the remaining fields are satisfactory.

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


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.

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

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

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.34). Click Next.

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

By default, the wizard proposes to add 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 drop-down list (see Figure 17.36). Click Next.

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

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

There is a Suppliers table in Northwind.mdb, so edit the proposed table name to tblSuppliers (see Figure 17.37). 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.

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.38). 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