This discussion has been locked.
You can no longer post new replies to this discussion. If you have a question you can start a new discussion

importing demands into an existing model from excel data

Is there a way to import demand data into an existing model?

I have an existing model with approximately 13,000 customers.  I've been trying to come up with a method of importing customer demands from meter read data via an excel file. We currently do not have any GIS data but will in the future.  I've tried using LoadBuilder but it appears you can only import shapefiles. 

My thought process is if I can somehow create a shapefile with customer locations and demand data, I can use Loadbuilder to assign those to nodes/pipes.

I have an excel file with customer meter read data.  The data includes meter reads over the past year which I've converted into a gpm unit.  I also have the x,y coordinate of each of the customers.

I've attempted to use ModelBuilder to build a model with the junctions with the assigned demands.  I've tried to map the fields to their appropriate properties.

ModelBuilder gets an error and says it cannot import the demands because it is a read only field.  I then used the Demand Control center to assign the demands to each customer by copying the data out of the excel file and pasting it into the demand control center.

This gets me to the step I cannot figure out.  Is there a way to export a model to a shapefile?

I thought I could create a Thiessen polygon with this information and use Loadbuilder to import but LoadBuilder wants a shape file for the Meter data which I don't have.

Maybe I'm going about this all wrong but have yet to find a way to accomplish what I want without a shapefile.

 

Any advice or ideas would be helpful.

 Thanks in advance

 

Travis

 

 

Parents
  • 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.

     

     

  • 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.

     



Reply Children
No Data