Exporting Shape Data to a Database
When you export shape data, you can choose the shape data and ShapeSheet cells to export either from selected shapes or all the shapes on a drawing page. The Export To Database command on the Tools menu works with the ODBC Data Source Administrator, which is part of the operating system, to connect you to a data source or create a new one. After you have established an ODBC connection, you can export shape data to an existing database table or to a new table in an existing database, as Figure 24-3 shows.
Figure 24-3. When you export shape data, Visio 2007 creates a new table in an existing database or a NewTable worksheet in an existing Excel file, as shown here.
By default, Visio 2007 exports all the shape data and ShapeSheet cells for each shape. At a minimum, Visio 2007 exports approximately 50 built-in size, position, and formatting data. If you want to export only a subset of this data, you can remove data from the list in the Export To Database dialog box. Removing data from the list doesn’t delete them from the shape.
Follow these steps to export shape data:
1. | If you want to export data for certain shapes only, select the shapes you want. Otherwise, cancel all selections to export data from all shapes on the page. |
2. | Choose Tools, Add-Ons, Visio Extras, Export To Database.
|
3. | In the Name list, select the ODBC data source that you want to export to. If you select Excel Files from the Name drop-down list, the Select Workbook dialog box appears. Locate the file (.xls or .xlsx) that you want to use, and then click OK. For details about creating a data source, see the section titled “Connecting to an ODBC Data Source” earlier in this chapter. |
4. | If your data source supports multiple databases, type the database you want in the Qualifier list. To limit the number of tables that Visio 2007 displays, type or select the name of a database creator in the Owner list. Note If your data source is Excel, Access, or another program that creates a single database, the Qualifier box displays the database name and the Owner box defaults to All Users. |
5. | In the Table list, type or select the specific table to which you want to export the data. |
6. | In the Field box, type a new name for the field that Visio 2007 will create to uniquely identify each record. By default, Visio 2007 creates a field called ShapeKey, the value of which is the shape’s internally generated ID number. To use a shape’s GUID as the unique identifier instead, select GUID from the Type list box. To mark this field as the primary key for your table, select the Make Key Field The Primary Key For Table check box. Note Not all ODBC drivers can create primary keys. |
7. | To reduce the amount of data that Visio 2007 exports for each shape, select an unwanted data in the Cell column, and then click Delete. |
8. | To export data using a different name or data type, select the data, click Modify, make the changes you want, and then click OK. |
9. | To export the data, click OK. |
Inside Out: Database Export WizardThe Export To Database command is a more streamlined version of the Database Export Wizard. If you prefer to step through multiple pages, you can use the wizard, which does offer the ability to select shapes by layer for export. To start the wizard, choose Tools, Add-Ons, Visio Extras, Database Export Wizard. |
Mapping Shape Data to an Existing Database
When you export shape data, you can specify a field in an existing database table into which Visio 2007 writes data values. You can also change how Visio 2007 maps data when it exports shape data and specify a data type to use. In the Export To Database dialog box (Cells To Export section), you can click Modify to display options for changing exported data and specifying database fields, as Figure 24-4 shows. The next sections describe how to use the options in the Modify dialog box.
Figure 24-4. When you export shape data, you can change how Visio 2007 maps data names and values to database fields.
Changing How Data Values Are Exported
The way that Visio 2007 exports shape data reflects the way that data is stored in the ShapeSheet. In the Modify dialog box, you can specify how you want a value to be exported in the Evaluate As list, which provides a long list of options that boil down to three choices:
Value Visio 2007 exports the contents of data or ShapeSheet cell as a value—a text string such as an employee name, a number-unit pair (such as 1 inch), a date, or some other value.
Formula A ShapeSheet cell can contain a formula, that is, an expression that can include constants, cell references, functions, and operators that evaluate to a value. In the ShapeSheet window, the default view displays cell contents as formulas (on the View menu, Formula is selected). When you export data as a formula, Visio 2007 exports the expression in a cell without evaluating it. For example, the width of a shape can be a formula such as .5 * Height. If the shape’s height is 4 inches, the value of the Width cell will be 2 inches, but if you export the cell’s contents as a formula, Visio 2007 exports .5 * Height.
Measurement unit Many shape data are dimensional. For example, a shape’s height is represented by a number and a unit of measure, such as 4 inches. You can export a value using an explicit unit of measure by selecting a unit in the Evaluate As list. If you don’t specify a unit, Visio 2007 exports a value using the default units defined for the data.
Changing How Data Is Mapped
When you export shape data to an existing database table, you can map the data values to existing fields. In the Modify dialog box, under Field Details, you can type the name of a field in the Name box, and then specify the field’s data type by selecting an option in the Type list. Visio 2007 writes the value of the shape data you select in the Export To Database dialog box to the field you specify in the Modify dialog box.
If the existing field is of variable length, type the size in bytes in the Length box. If the selected type supports decimal numbers, you can specify the precision by typing a number to indicate the number of decimal places in the Decimal box.