You are currently reviewing an older revision of this page.
Document Type: TechNote Product(s): Bentley WaterCAD, Bentley WaterGEMS Version(s): V8 XM and V8i Original Author: Rob Gurrieri, Bentley Systems
Document Type: TechNote
Product(s): Bentley WaterCAD, Bentley WaterGEMS
Version(s): V8 XM and V8i
Original Author: Rob Gurrieri, Bentley Systems
Time Series Field Data can be imported into WaterCAD and WaterGEMS V8 XM and V8i using ModelBuilder. This technote explains how to do it.
All tabular data entry grids in WaterGEMS allow you to easily paste in data from Excel. Simply copy the data from Excel, and click [Ctrl]+[V] to paste it into WaterGEMS. If that is not an option in the case where you need to bring in a lot of data, ModelBuilder can be used to import this data.
Here is the "Components" > "Time Series Field Data" editor in WaterGEMS:
The data in here maps onto the following *two* table types in ModelBuilder: Time Series, and Time Series Collection. The "Time Series" mapping represents entries in the TreeView along the left of the form (including the simple "Start Date Time", "Element", and "Notes" values shown on the right). The "Time Series Collection" mapping represents the tabular data shown in the table at the bottom right of the form.
To automatically determine the appropriate values for handling Pipe Flow time series data, we're going to first export a sample from WaterGEMS to Excel.First, create a sample Pipe Flow time series in WaterGEMS as shown above.
Next, create a new Excel .xls file. You will need two "sheets" to receive the data (the default "Sheet1" and "Sheet2" will do).
Note: We recommend choosing MSAccess over MSExcel if possible; there is no explicit way to specify the data-type of a column in Excel, which can result in some problems.
Time Series: This is the more involved of the two Excel sheets we need to set up. To determine the columns to define in Excel, create a temporary ModelBuilder connection and get to the "Specify Field Mappings" step (you won't be saving this connection, so to get past Step 1 of the Wizard, just pick any data source). Navigate to this step, choose the Time Series table type, and click on the "Property" drop-down field.
Click on the Sheet1 tab in Excel to define the necessary columns for the "Time Series" table (You don't need all of these columns for Flow Data, but go ahead and define them all to be sure we don't miss any that are required for your use-case). It should look something like this:
Again, get to the "Specify Field Mappings" step in ModelBuilder, choose the "Time Series Collection" table type, and click on the "Property" drop-down field to determine the columns to define.
Click on the Sheet2 tab in Excel and define the necessary columns for the "Time Series Collection" table. It should look something like this:
Save and close your spreadsheet.
Now we're ready to create the ModelBuilder connection to this spreadsheet.
Open ModelBuilder and create a new Connection. In step 1 of the Wizard, choose "Excel" as the data source type, browse to the Excel spreadsheet that you created to select it. You should see Sheet1 and Sheet2 in the list of available tables, select those (and unselect any others that appear).
Navigate through the next few steps, just use the defaults there. When you reach the Mapping Step, set things up for Sheet1 and Sheet2 as shown below:
Navigate to the end of the Wizard. On the last step, click "No" for the "Would you like to build a model now?" prompt and click [Finish].
Choose the connection you just defined (be sure to close the Excel spreadsheet you just defined), and click the Sync Out toolbar button.
The sample time series data from WaterGEMS will now be available in the Excel spreadsheet you created.Using that as a go-by, you should be able to enter the data in the appropriate format to import in to WaterGEMS.
Product TechNotes and FAQs
Haestad Methods Product Tech Notes And FAQs
General WaterGEMS V8 FAQ
Water and Wastewater Forums
Bentley Technical Support KnowledgeBase
Bentley LEARN Server