Using the Import Data Wizard | Microsoft Visio Version 2002 Inside Out (Inside Out (Microsoft))

3 4

Imported data can bring a space plan to life, but you don't need it to create a space plan in Visio. However, if you have a floor plan of some type and a spreadsheet or database of facility and employee information, you can bring the two together in Visio Professional. The Space Plan template includes the Import Data command on the Plan menu, which starts a wizard that you can use to import the information you want to include. It's easiest to import data from an Excel spreadsheet, but you can import information from Microsoft Exchange Server, Microsoft Active Directory, or any ODBC-compliant database, which includes most popular database programs.

Note

ODBC stands for open database connectivity, a standard that Microsoft created to help you import database information into many different kinds of applications. If you aren't sure whether your database is ODBC-compliant, check its documentation.

The one piece of information your data source should include is a space identifier, as Figure 26-11 shows. The wizard uses the space identifier to figure out where to put the imported data in your floor plan. If you're importing information about spaces, the space identifier is the field in your data source that uniquely identifies each space—typically, a room number. If you're importing information about people or equipment, the space identifier is the field in your data source that matches the identifier already assigned to the space shapes in your drawing. For example, you can import an employee list so that your floor plan shows who sits where. In addition to employee names, your employee list must also include a space identifier that matches the spaces you've set up in your floor plan. Then, when the employee names are imported, the wizard can automatically associate people with spaces.

figure 26-11. when you import employee names from a microsoft access table, the wizard automatically places the names in the correct offices.

Figure 26-11. When you import employee names from a Microsoft Access table, the wizard automatically places the names in the correct offices.

For details about assigning identifiers to space shapes, see "Defining Spaces in a Floor Plan."

If your data source doesn't include location information or a space ID, you can still import information from it, but you'll have to place the imported items in your floor plan manually where you want them. For example, if you have a database of computer equipment owned by different departments in your organization, you can import the data into your floor plan. To show where the equipment is located, you must drag individual computers from the Explorer window into the offices or rooms of your floor plan.

Starting the Import Data Wizard

The Import Data Wizard works like this: you point the wizard to your data source and indicate which columns or fields to import and where to put the data in your floor plan. The wizard imports the information and displays it in the Explorer window, from which you can choose the items you want to add to your floor plan. You may not want to display all of the data you import in the drawing, but you can always view and work with it in the Explorer window. You can use the imported data as criteria for color-coding spaces, and you can change which data is displayed at any time. To begin importing data with the Import Data Wizard, select Plan, Import Data. The sections that follow describe each screen in the wizard.

Tip

The Import Data Wizard can place people and assets in their correct locations, as long as each space shape in your drawing includes a space identifier (such as an office number) and your external data source includes the same identifier.

Preparing to Use a Database

Visio is able to link shapes in a floor plan to external data through the shapes' custom properties. The link is one-directional—that is, from the data source to the Visio shapes. You can refresh your floor plan with the latest information from your data source, but changes you make to facilities information in Visio cannot be written back to your data source. If you plan to work with an external data source, it helps to do the following before you start:

Create a data source. If you're using a database other than Exchange Server or Active Directory, make sure that you have a data source for it. A data source is a file with the extension .dsn that allows you to connect to a data provider. You may already have the necessary data source on your computer, or an information systems technician or database expert can create one for you. If you don't know whether you have a data source, you can start to set up your space plan, and then determine whether a data source for your database is listed when you attempt to import data. You can use the Windows Control Panel to set up ODBC data sources.
Ensure that you have access to the data source. To import data from a corporate or shared database, you may need specific permissions and network access. If database access requires a password, make sure you have the right one.

InsideOut

Even if your database isn't ODBC-compliant, you can probably export data from it into a format that Visio can import, such as tab-delimited or comma-delimited text, which are common output formats supported by most database programs. Then you can set up an ODBC data source for the exported file. The bottom line is that if you need to pull data in from a proprietary or legacy data source, you probably can—but you might need to have a professional prepare your data.

Step 1: Selecting a Category

On the first screen of the Import Data Wizard, select the category of shape for which you want to import data, as Figure 26-12 shows. The category you select here determines:

The category in the Explorer window under which the imported data will be listed.
The shape that Visio uses in your floor plan to represent the imported data.
The custom properties associated with each shape.

The category choices are Person, Space, Boundary, Computer, Printer, Asset, Equipment, Fixture, and Furniture. The shapes associated with each category are shown on the Resources stencil. Use the Space and Boundary categories to import information about locations and the Person category to import lists of people. Use the Computer, Equipment, Fixture, Furniture, and Printer categories to track these types of assets. The Asset category is useful for anything else not covered by the other categories.

When you've chosen the shape category, click Next to proceed to the next screen in the wizard.

figure 26-12. the first screen of the import data wizard shows you categories of facility information. the wizard will import your data using the shape that represents the category you select.

Figure 26-12. The first screen of the Import Data Wizard shows you categories of facility information. The wizard will import your data using the shape that represents the category you select.

Step 2: Attaching to a Database

On the second screen of the Import Data Wizard, you identify the external data source you want to use. In the Source box, choose the type of database or spreadsheet that contains your space information. Depending on your choice, the options differ as follows:

If you choose Excel, click the Browse button, locate the spreadsheet file, and then click OK. If the spreadsheet contains multiple sheets, select the one containing the information you want to use in the Worksheet box, as Figure 26-13 shows.

Figure 26-13. When you choose Excel for Source, the File, Browse, and Worksheet options are available. Click Browse to locate the spreadsheet you want to use as your data source.
If you choose Active Directory or Exchange Server, click Next to log on to the appropriate server.
If you're using any other kind of database or spreadsheet (such as a Microsoft Access database, a proprietary human resources system, or an Oracle database), choose ODBC, and then click Next. On the screen that appears, select a data source (DSN) in the Data Source box. A Select Database dialog box opens, where you locate a specific database file and then click OK. In the Import Data Wizard, Visio then lists the database's catalogs and tables, as Figure 26-14 shows. Select the one you want to use. A preview of the data from the selected table appears.

figure 26-14. after you select a data source, you can choose the catalog and table that contain the information you want. this access database doesn't support catalogs, so the wizard displays its file name instead.

Figure 26-14. After you select a data source, you can choose the catalog and table that contain the information you want. This Access database doesn't support catalogs, so the wizard displays its file name instead.

When you've specified the file from which to import data, click Next to continue with the wizard.

Step 3: Identifying the Name and Location Information

The third wizard screen (see Figure 26-15) is critical; in this step you specify the fields in your database that provide the information the Import Data Wizard needs to match the data to the right space. The wizard needs to be able to name a record, match it to a space in the floor plan, match it to that space in the correct building, and link the database records to the space plan.

To link your data source to the floor plan, you specify the following:

Name. Choose the field from your data source that you want to appear in the Explorer window to identify each shape. If you're importing data in the Space category, choose a field that lists room or office numbers, so that each space will be treated uniquely. If you're importing information for the Person category, you can choose a field that lists people by their employee identification numbers, e-mail addresses, or other unique identifier.

Figure 26-15. The wizard previews the fields and data from your data source to help you determine the purpose of each field—which is particularly useful if you didn't create the database.
Space ID. If you're importing space data, choose the field in your data source that uniquely identifies each space, which is typically the field that contains room numbers. You can use the same field that you used for the Name box. If you're importing person or asset data, choose the field that contains values that match the space IDs already in your drawing, so that the wizard can place your information in the correct space on the floor plan. If the data source doesn't include location information, choose <None>; your data will be imported as Unplaced Data in the Explorer window but won't be placed in your drawing. If you choose <None>, when you click Next, you'll see a warning about this effect, which you can ignore by clicking OK.
Building ID. If you're creating space plans for more than one building, or if you're pulling data from a database that includes information for multiple buildings, choose a field that uniquely identifies the building. This field ensures that the information for Room 231 in Building 3 isn't imported for Room 231 in Building 4. You can leave this blank if you are only working with one building, and your data source includes information for only one building. You can't use the same field for Building ID and Space ID.
Key. If you want to refresh your data later, choose a primary key from your data source. A key is required only if you want to update your imported information later; it's not necessary for importing the data. For example, if you're linking to an employee database and want to be able to update your floor plan with names as new employees are hired, you'll want to supply a key. If your data source is Excel, choose a field that contains unique information for each row, such as an ID.

When you've finished choosing fields for each of these areas, click Next.

Map Database Fields with Custom Properties

The next screen in the Import Data Wizard is optional but worth noting. Your space, person, or asset data will be imported whether or not you take any action on this screen. Its purpose is to allow you to specify additional information about the space, people, or assets in your database and import that information as custom property values that you can use in reports.

On this screen, you map the fields in your data source to existing custom property fields or indicate that the data should be brought in as a new custom property, as Figure 26-16 shows. The wizard lists the fields or columns of your data source and the existing custom properties that Visio tracks for the category you've selected. If you want to add information from your data source to your spaces, select an option in the Fields box, select an option in the Properties box to map the information to, and then click Add.

figure 26-16. on this screen, you can specify additional information about the space, people, or assets in your data source that you want to be imported as custom properties.

Figure 26-16. On this screen, you can specify additional information about the space, people, or assets in your data source that you want to be imported as custom properties.

For example, if your data source includes a field called Date Of Hire, you can add it as a new custom property by selecting <New> in the Properties list and then clicking Add. The wizard displays your selection in the Mapped box. If your data source includes a field called Phone Ext., you can map it to the existing Phone Number custom property by selecting Phone Number in the Properties box and then clicking Add. When you've selected the fields you want to import as custom properties, click Next.

Step 4: Preparing to Import the Data

The final screen of the Import Data Wizard contains several logistical options related to importing your data, as Figure 26-17 shows.

figure 26-17. the final screen of the import data wizard provides options that determine how the data will be placed in the drawing.

Figure 26-17. The final screen of the Import Data Wizard provides options that determine how the data will be placed in the drawing.

You can choose the following options:

Building ID. If your database has information for more than one building, choose the building for which you want to import data. If the database contains data for only one building, and you didn't identify a Building ID field on a previous screen, this option is not available.
Update. If your floor plan includes multiple pages, select an option under Update to indicate whether the imported spaces apply to all pages or only the current page.
Remove Unconnected Shapes. If you want to remove any shapes in your floor plan that do not correspond to a record in your data source, select this check box. However, it's best to leave this option unselected unless you're certain you won't need those shapes later. For example, if your floor plan includes shapes for closets, your data source may not include any records for those shapes, but you wouldn't want to delete them from the drawing. This option is available only if your data source includes a primary key that you identified in the Key box on the third wizard screen.
Show Report. If you want Visio to show you a status report after the data is imported, select this check box. The report is an XML file that opens in your Web browser, as Figure 26-18 shows. It shows which category of shape you imported, the name and space ID you imported for each item, and the action taken by the wizard for each item.

Click Finish to complete the wizard.

figure 26-18. if you select the show report check box, the import data wizard displays a report, like this one, that shows how your data was placed in the drawing.

Figure 26-18. If you select the Show Report check box, the Import Data Wizard displays a report, like this one, that shows how your data was placed in the drawing.

Step 5: Importing the Data

The wizard imports the fields from your data source and adds the information to the Visio file as follows:

If you supplied Space IDs for the imported fields, the wizard creates a shape for each person or other resource and places it in the matching space on your floor plan. You might need to resize the shapes if they're too large for the room or office. Each person or asset is also listed on the Categories tab of the Explorer window under the category you selected on the first screen of the wizard.
If you didn't specify a field for Space ID, the wizard imports your information as Unplaced Data in the Explorer window.
If you mapped fields to properties in the wizard, the data is added to the custom property fields for each corresponding person or other shape. To see the data, display the Custom Properties window, and then select a shape in the floor plan.

If your data was imported as Unplaced Data, you can associate it with a location in one of two ways:

Drag an item from the Explorer window into an office or room.
On the Categories tab, expand the Space list. Drag an unplaced item onto a space in the Space list. This is a little easier if you resize the Explore window so that Unplaced Data and Space are both visible.

Troubleshooting

The Refresh Data command does not update the information in the space plan.

The Refresh Data command works only if you imported data from a data source that includes a field that uniquely identifies each record and you mapped this field to a key in the Import Data Wizard.

Refreshing the Data

If you want to update the data in your drawing when your database changes, you can as long as you identified a Key field when you imported the data. Updating, or refreshing, the data is a one-way process: Visio reconnects to the original data source and imports the fields and their current values, which overwrites the information in your current drawing. If you've made changes to your space plan—for example, if you've moved employees around or reallocated resources—your changes may be lost after you refresh.

Choose Plan, Refresh Data to display the Refresh Data dialog box, as Figure 26-19 shows. The dialog box lists the categories of data you have imported and provides options for updating a single page or all the pages in the drawing. Like the Import Data Wizard, this dialog box contains options to remove unconnected shapes and show a report of the import activity.

figure 26-19. to update the information in your floor plan, choose plan, refresh data to display this dialog box. visio re-imports the data, overwriting the values in your model.

Figure 26-19. To update the information in your floor plan, choose Plan, Refresh Data to display this dialog box. Visio re-imports the data, overwriting the values in your model.

Troubleshooting

Refreshing the data in the space plan overwrites changes that were made to the diagram.

If you want to refresh the data in your facility model without losing the changes you've made in the drawing, the only way to do this is to make the same changes to the original data source. For example, if you move a person in a floor plan based on an imported spreadsheet of employee names, you must record the new location in the Excel spreadsheet. If you aren't sure whether you made changes that will be overwritten, make a backup copy of your drawing before you refresh the data. That way you can compare the drawings later.

Another trick for keeping the information in sync between a space plan and your data source is to check the Unplaced Shapes listing. It can serve as a red flag for identifying problems in your data source. You can print a list of unplaced shapes for your database administrator to use in correcting the data.

If Visio can't locate your data source, it displays a message. If you click OK in the message box, a Login dialog box appears with which you can reconnect to the data source.