Creating selection lists with imported data

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.

1. In a file browser window, navigate to the folder containing the exercise data.
2. 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.

3. Close the file.

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

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

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

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

10. In the SQL dialog box, expand the Object Type drop-down and select Customer Point.
11. 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.

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

This loads your CSV into the list variable.

17. 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:

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

20. Click Apply.

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

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

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

OR, to create a new one:

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

27. Name the selection list “Customers”.
28. Click OK.

To clear the current selection:

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

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

31. With the Customers still selected, in the Selection menu, pick SQL Select.
32. In the SQL dialog box, expand the Object Type dropdown and select Customer Point.
33. Enable Apply Filter to Current Selection.
34. Type the following query: SELECT reference INTO FILE ‘
35. 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.

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

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