Overview

Time Series Field Data can be imported into WaterCAD and WaterGEMS V8 XM and V8i using ModelBuilder. This technote explains how to do it.

Background

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.

WaterGEMS Time Series Field 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.

Export Sample Time Series data

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:

Time Series Collection

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.

Define the ModelBuilder Connection

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

Synchronize Out from ModelBuilder

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.

Data Import from Excel

Now you have the template excel file (following the above steps), save a copy of this excel for a back up and start populating the relative information for importing to the Model. In the Model Builder window, select the link that was generated in the above step, duplicate it and use that link to import the Observed Data.

See Also

Product TechNotes and FAQs

OpenFlows Product Tech Notes And FAQs

General WaterGEMS V8 FAQ  

Hydraulics and Hydrology Forum

External Links

Bentley SELECTservices

Bentley LEARN Server