Creating selection lists with imported data

Any referenced datasets can be downloaded from "Module downloads" in the module overview.

Video quiz

Step-by-step Guide

When using InfoWorks WS Pro, SQL is a flexible tool for importing and exporting data. In this example is a CSV containing a list of customers who have reported low water pressure. This example creates a selection list of these customers.

In a file browser window, navigate to the folder containing the exercise data.
In Notepad or a spreadsheet program like Microsoft Excel, open the Customers.csv file to check the structure.

The first value must be the name of the variable to be used; in this case, it is $customers. All values, or for this example, customer IDs, are separated by commas.

A Microsoft Excel spreadsheet with the Customers.csv file open and the first value highlighted in red.

Close the file.

To locate the full file path, which is necessary to load it in an SQL query:

In the file browser window, right-click Customers.csv and select Properties.
On the Security tab, in the Object name field, select all the text.
On your keyboard, press CTRL+C

The Properties dialog box for Customers.csv, Security tab, with the complete file path selected and highlighted in red.

Click OK.
From the Model Group window, drag-and-drop the BridgeNet network into the workspace to open the GeoPlan.
In the Selection menu, pick SQL Select.

The InfoWorks WS Pro interface with the Selection menu displayed and SQL Select selected; and in the workspace, the BridgeNet GeoPlan open.

Your SQL query needs to load the list of customers, and then select any customers in that list.

In the SQL dialog box, expand the Object Type drop-down and select Customer Point.
In the text box, type: LIST $customers;

This defines the list variable you will load your CSV into. Remember that this must match the first value in your CSV.

On your keyboard, press ENTER.
Type: LOAD $customers FROM FILE ‘
On your keyboard, press CTRL+V to paste the complete file path.
Type another apostrophe (‘).
Type a semicolon (;).

This loads your CSV into the list variable.

Type: SELECT WHERE MEMBER(reference, $customers)

This will select any customer whose reference field is in your $customers list. Your syntax should match the image below:

The SQL dialog box with the SQL query entered.

Click Test to ensure the query syntax is correct.
In the notification, click OK.

The SQL dialog box with Test highlighted in red and the notification with OK selected.

Click Apply.

There should now be customers selected in the network. If the customers are not visible:

Move the SQL dialog box to one side.
Right click in the GeoPlan and select Find selection.

The GeoPlan shortcut menu with Find selection selected and called out.

Close the SQL dialog box.
In the Model Group window, open the existing Selection Lists group.

OR, to create a new one:

Right-click the Selection Lists group.
Select New > Selection List.

In the Model Group window, the Selection Lists shortcut menu with New selected, and in the flyout, Selection List selected and called out.

Name the selection list “Customers”.
Click OK.

To clear the current selection:

On the Operations toolbar, click Clear Selection.
From the Model Group window, drag-and-drop the Customers selection list into the GeoPlan to re-select the customers.

In the Operations toolbar, Clear selection highlighted in red, and below, the mouse pointer dragging the Customers selection list from the Model Group window into the GeoPlan.

Now, repeat the process in reverse, and then export the affected customers:

With the Customers still selected, in the Selection menu, pick SQL Select.
In the SQL dialog box, expand the Object Type dropdown and select Customer Point.
Enable Apply Filter to Current Selection.
Type the following query: SELECT reference INTO FILE ‘
On your keyboard, press CTRL+V to paste the file path to the CSV again.

Note: If needed, repeat Steps 4-7 to copy the full file path.

Change the filename to CustomersExport.csv.
At the end of the filename, type an apostrophe (‘).

The SQL dialog box with the export SQL query entered and Apply selected.

Click Test to make sure that the query syntax is correct.
Click OK.
Click Apply.
Navigate to the data folder, where you will find a list of exported customer IDs.

A Microsoft Excel spreadsheet with a list of exported customer IDs.

Was this information helpful?

Cookie preferences

Your privacy is important to us and so is an optimal experience. To help us customize information and build applications, we collect data about your use of this site.

May we collect and use your data?

Learn more about the Third Party Services we use and our Privacy Statement.

Are you sure you want a less customized experience?

We can access your data only if you select "yes" for the categories on the previous screen. This lets us tailor our marketing so that it's more relevant for you. You can change your settings at any time by visiting our privacy statement

Your experience. Your choice.

We care about your privacy. The data we collect helps us understand how you use our products, what information you might be interested in, and what we can improve to make your engagement with Autodesk more rewarding.

May we collect and use your data to tailor your experience?

Explore the benefits of a customized experience by managing your privacy settings for this site or visit our Privacy Statement to learn more about your options.

Creating selection lists with imported data

Video quiz

If you had a CSV file that listed customers who reported low water pressure, you could create a selection list of these customers by doing which of the following?

Step-by-step Guide

Cookie preferences

Strictly necessary – required for our site to work and to provide services to you

Improve your experience – allows us to show you what is relevant to you

Customize your advertising – permits us to offer targeted advertising to you

Strictly necessary – required for our site to work and to provide services to you

Improve your experience – allows us to show you what is relevant to you

Customize your advertising – permits us to offer targeted advertising to you

Are you sure you want a less customized experience?

Your experience. Your choice.

If you had a CSV file that listed customers who reported low water pressure, you could create a selection list of these customers by doing which of the following?

Cookie preferences

Strictly necessary – required for our site to work and to provide services to you

Improve your experience – allows us to show you what is relevant to you

Customize your advertising – permits us to offer targeted advertising to you

THIRD PARTY SERVICES

Strictly necessary – required for our site to work and to provide services to you

Improve your experience – allows us to show you what is relevant to you

Customize your advertising – permits us to offer targeted advertising to you

Are you sure you want a less customized experience?

Your experience. Your choice.