How can I create a selection set from data I have in an excel spreadsheet?

   
  Applies To 
   
  Product(s):
Bentley WaterGEMS, Bentley SewerGEMS, Bentley CivilStorm, Bentley StormCAD, Bentley PondPack, Bentley SewerCAD, Bentley HAMMER, Bentley WaterCAD
  Version(s): 08.11.XX.XX
  Environment:  N/A
  Area: 
  Original Author: Mark Pachlhofer, Bentley Technical Support Group
   

Problem

How can I create a selection set from data I have in an excel spreadsheet?

Solution

Option 1

  • 1) List the labels or ID's of the elements you will be selecting horizontally across a single row of your spreadsheet.

    2) Save the file as a comma separated value (.CSV) file.

    3) In whichever of our programs you're using go to View > Queries and choose to create a new query

    4) Choose the element type you'll be creating the query for

    5) Then you can create a query similar to this one, which uses 'Label' as the field you're going to be selecting elements based on.

    Label IN ()

    6) Now open the .CSV file inside Notepad or another text editor and copy the comma separated labels you have.

    7) Paste what you have inside the parentheses of your query and click the "Validate on Ok" check box. Then click the OK button to close the query.

    8) Now double click the query name to highlight the elements in the drawing pane.

    9) Right click on the elements in the drawing pane and choose "Create Selection Set"

    Option 2

    This solution assumes that you already have a label field or ID field in your spreadsheet with labels or ID's that match the elements you have in your model. 
     
    1) Create a user data extension field for whichever element you will be creating a selection set for.
     
    2) Add a column to your spreadsheet that will be used as an identifier of which elements you are going to have in your selection set. The column can contain a word or number. Something simple such as using the word "Updated" or the number 1 in the column will work. Just make sure that if you use a word you created the user data extension and assigned it the text type or if you are using a number you gave the user data extension a numeric field type. The only purpose of this field to update the elements you want to include in your selection set, so it doesn't matter what information goes into the field.
     
     
    3) Start ModelBuilder (Tools > Modelbuilder) and create a new run.
     
    4) Choose your Excel worksheet as the data source type and proceed to step 5 (field matching) of Modelbuilder.
     
    5) On step 5 choose the Key field and in the field matching step just match the new user defined field name with the column that you created on in your spreadsheet.
     
    6) Proceed to the last step of ModelBuilder and choose to the following options found in the screen shot below and then hit "Finish". This will automatically create a selection set based on the elements that you modified.
     
     
  • Created by
  • When: Thu, Apr 23 2015 12:25 PM
  • Last revision by
  • When: Mon, Jun 8 2015 11:40 AM
  • Revisions: 4
  • Comments: 0
Anonymous
Recommended
Related