importing demands into an existing model from excel data

Hi Travis,

It looks like Mark and Jesse have provided some good options, but here's a way to do this that's closer to what I believe was your initial approach.

Sample Excel data:

In WaterGEMS, create a user data field (Tools>User Data Extensions) called customer demand with units of gpm (or gpd, or whatever you prefer):

Use Modelbuilder to import junction nodes, coordinates, and customer demand into your user data field:

Customize a junction FlexTable to include your Customer Demand field, and use the button in the upper left of the table to export the shapefile. You can then use the shapefile as the source of customer locations and demands when you set up LoadBuilder in your actual model.

Regards,

Kristen Dietrich

Thanks so much Kristen,

I've been messing around with this just testing it out and I think this will work fine using the Theissen Polygon and importing the shape file using nearest node.

Travis

The simplest way is to just use ModelBuilder?  Importing Coordinates AND Demands at the same time can be done out of the box!

In ModelBuilder you can set it up to use the same worksheet twice (or 3,4.....how ever many times you like) in the SAME ModelBuilder import using a little known feature.   All you need to do the below is to have a column named "Row Number" or "Row Label" etc.  which can just be 1,2,3,4.... in Excel for the different junctions.

The "trick" is back on the first step of ModelBuilder.......there is a button marked "Duplicate" underneath "Choose the tables you would like to work with"  Hit "Duplicate" with the worksheet containing the data selected, so that it appears twice in the ModelBuilder data table list:

1.  One link for the Junction Label, X and Y (Table Type: Junction)

2.  In the same ModelBuilder operation, another duplicate data link for the Junction Demand Collections (Table Type:Junction, Demand Collection)

Go forward in the wizard to the next step and you will see the same worksheet similarly appear twice.  Setup the first data link for X and Y coordinate import under Table Type: Junction

Similarly click down onto the second, duplicated link of the data worksheet and setup this as Type Type: Junction, Demand Collection (both links will need the Key Field specified identically as eg. The row number label.)

Import and voila! Junctions with both coordinates AND demands imported!

Thanks a ton Ben,  That worked perfectly to import the demands.  What I've realized is that I now have the demands imported but no way of exporting them via the junction flextable.

It will not let you export the "Demand Collection" field.  It will allow you to export the demand field.  The only way to get the correction demands is to have pipes connecting all the nodes and have a reservoir.  I will have to add piping to all the junctions/nodes to be able to use this method I believe.

Thanks a ton Ben,  That worked perfectly to import the demands.  What I've realized is that I now have the demands imported but no way of exporting them via the junction flextable.

It will not let you export the "Demand Collection" field.  It will allow you to export the demand field.  The only way to get the correction demands is to have pipes connecting all the nodes and have a reservoir.  I will have to add piping to all the junctions/nodes to be able to use this method I believe.

Let me add one more option here.

Reading the original post, I feel like this is the exact reason why I wrote this tool. The tool can take .CSV (which you can save from an Excel) file and will give you a shapefile so that you can use it loadbuilder. (if needed same shapefile can be used to build the model)

http://communities.bentley.com/other/old_site_member_blogs/peer_blogs/b/akshayas_blog/archive/2013/02/05/csv-to-shapefile-a-waterobjects-net-tool.aspx

There is a little video too to help the conversion process.

Let me add one more option here.

Reading the original post, I feel like this is the exact reason why I wrote this tool. The tool can take .CSV (which you can save from an Excel) file and will give you a shapefile so that you can use it in loadbuilder. (if needed same shapefile can be used to build the model)

http://communities.bentley.com/other/old_site_member_blogs/peer_blogs/b/akshayas_blog/archive/2013/02/05/csv-to-shapefile-a-waterobjects-net-tool.aspx

There is a little video too to help the conversion process.

Akshaya,

Thanks for the link.  Jesse posted a link to your tool previous in the thread.  I haven't had a chance to try it yet as my I.T. dept has yet to copy the exe to the proper directory.  Also for some reason when I try to watch the video nothing shows up.  I can see the preview and as soon as I click play, the whole video disappears.

Hi All,

I am trying to use model builder for importing demands into Unit demand control centre.  I am using Bentley WaterGems V8i Select Series4 and this is not giving me th option of using "Junction,Demand Collection" as Table Type.  Has anyone else encountered this and what is way around this to import my unit demands from a shapefile into Unit Demand Control Centre.

Hi Travis,

There are a few options for export here.   Possibly one handy interface to get familiar with is the Demand Control Center (under the Tools Menu) which is the equivalent "FlexTable" for Base Demand Collections.

Here you have to ability to edit ALL base demands in the model, and copy/paste to/from other programs like MS Excel.   In fact, if you play with some of the tools in the Demand Control Center, you'll see there is even yet another alternative for importing demands by using a combination of  the "Initialize Demands for All Elements" or "Add Demands" command followed by a straight copy/paste from Excel into the Control Center Table!  It's particularly handy when you have true demand collections of more than one demand entry per junction element.

It's the easiest export option here since it is just straight table copy/paste from WaterCAD into Excel.

Another option is that you can also reuse the ModelBuilder import script you setup to also export back the other way (ModelBuilder pushes data both ways), however I would probably recommend with one or two key changes.

Back at the main ModelBuilder page listing all the ModelBuilder connections that have been setup (of which you may only have one defined at the moment.......the Import script),  there is similarly another "Duplicate" button back here that allows you to make a copy of the Import connection as the basis for a separate Export operation to Excel (You can actually use the same connection definition for both import/export if you wish............but for other reasons, like not overwriting your source files, you often setup separate import and export ModelBuilder operations)

You might want to edit this export script to be different in a couple of ways from the Import routine:

1.  You would probably not want to overwrite your input worksheet, and instead export the data to a new, separate worksheet called "Demands-Exported" or similar.  You will need to create this new Excel worksheet, and give it column headings like Label, Demand, Pattern in Row 1, but otherwise blank data underneath.

2.  Similarly, following the ModelBuilder Wizard you will want to untick the worksheet(s) being used to import from (as you probably don't want to overwrite these with exported data!), and instead tick the new worksheet to export demand data to.    Just like the Import, you setup the Table Type as Junction, Demand Collection and map the Excel column headings to the WaterCAD Demand Property Fields, Label (as the Key Field), Demand (Base) and Pattern (Demand) (Label)

3.   In the Wizard Page, "Would you like to Build the Model Now" you set to NO (Otherwise the Build/Import operation will commence and blank out all your model demands!)

4.  Finish the Wizard to complete the ModelBuilder definition, then select the completed ModelBuilder definition from the list and hit the "Sync Out" button to export from WaterCAD to Excel.