Export time series data to Excel or CSV format

  Product(s): WaterGEMS, WaterCAD, HAMMER, SewerGEMS, SewerCAD, StormCAD, CivilStorm
  Version(s): CONNECT Edition, V8i (see below)
  Area: Output and Reporting

 

Introduction

This article applies to

WaterGEMS, WaterCAD, HAMMER versions 08.11.06.113 or later (including CONNECT Edition).

SewerGEMS, SewerCAD, StormCAD, CivilStorm 08.11.05.113 or later (including CONNECT Edition.

These versions include the ability to export time series data for multiple elements and element types to Excel or CSV format.

Steps to Accomplish

1. Prepare the model.

  • Set the active scenario you want to export data from.
  • Make sure calculation options include the time and time steps you want.
  • Compute the model.
  • To specify the properties that are exported, create a custom FlexTable that only includes the properties you want exported. This needs to be done for each element type being exported. (See: Customizing FlexTables - moving, adding, and removing columns)
  • To only export data for some elements, of an element type, use the following methods:
  • Select the elements.
  • Create a Selection Set.
  • Set the element type's FlexTable filter.

2. Go to File > Export > Export to Excel

3. Set the export options.

Select the element types to export.
This is done in the "Specify Properties to Export" section on the left side. The element types that are checked will be exported as separate tabs in the spreadsheet.

Specify hydraulic model elements to export

  • This can be se to export All Elements, the currently selected elements ("Selection"), or a Selection Set.
  • If the "Publish a subset of elements based on the Flex Table filters" box is checked, only those elements that are in the filtered flex table will be included in the spreadsheet.
  • If the "Exclude topologically inactive elements" box is checked, only active elements (Is active? = True) will be included in the spreadsheet.

Table/Properties
Select an element type and choose to export all properties or just the properties included in an existing FlexTable. This can be selected from the dropdown list or by clicking the ellipses "…" button. This needs to be done separately for each element type. You can create a new custom flextable (under "Hydraulic Model" to save it just in the current model, or "Shared" for it to be available in all models - see more here) to help reduce the amount of data exported.

Table Path
It is possible to have multiple FlexTables with the same name. This field shows if the FlexTable selected is Shared, Predefined, or if it is only contained in the Hydraulic Model.

Filter
If the selected FlexTable is filtered, the filter is displayed in the Filter field and in the left pane, the Is Filtered column is set to True for that element type.

Properties
This shows the properties that are to be exported for that element type. As mentioned above, you can create and select a custom flextable with only the fields you need, to help reduce the exported data.

Specify result time steps to export
This is a new feature available starting with CONNECT Edition Update 4 (10.04.00.XX and greater). This enables you to filter calculated results for an EPS using a Constant output step, or a Variable output step. With the "variable" option, you can choose between No results, All results or a Constant output step, for multiple time ranges, just like the calculation option described here. Note that this feature applies a filter on top of the existing computed results, and the setting you choose is persisted across all models (be sure to adjust it for other models as needed). For example if you wish to only export hydraulic results for the last 24 hours of a one week simulation, you can use the "Variable" option and set the first 6 days to "None" and then use the "All" or "Constant" for the last 24 hours.


4. After all settings are established for all element types, click OK.

5. Specify the format (Excel or CSV) and the location to save to, then click OK. Note that the Excel (XLSX) format has a limit of 1,048,576 rows.

Advanced Export Automation

For information on a more advanced, automated approach to data export, see: Automatic processing of results (or other) data using an extensible data exchange framework

Troubleshooting

Problem: The following error occurs when attempting to export

Export to Excel failed.
Exception from HRESULT: 0x800A03EC
Error code -2146827284

Solution: You may have exceeded the limit on the number of rows supported by the Excel format. Use the available filtering options (consider the time step filtering mentioned above) or consider choosing the CSV format using the dropdown in the bottom-left corner of the Excel Export dialog.

See Also

Customizing FlexTables - moving, adding, and removing columns

Can I easily export my results to an Excel spreadsheet?

What’s new in StormCAD, SewerCAD, SewerGEMS and CivilStorm V8i SELECTseries 5 with CONNECT Integration?

What's new in WaterCAD, WaterGEMS and HAMMER V8i SELECTseries 6 with CONNECT Integration?

  Original Author: Craig Calvin
Recommended
Related