Problem

Can I easily export my results to an Excel spreadsheet?

Can I export results to an Excel spreadsheet for all the time steps in my analysis? (time series results)

Solution

Export from Flextables

The first and most basic method of exporting model results to Excel is to use the Export button in the top-left corner of any Flextable. From here, choose CSV as the file type and the table will be exported to a CSV file that can be opened in Excel. Make sure to add or remove columns as needed before export. You can set up a new Project-Level flextable first if you don't want to modify the predefined flextables.

Note that with this method, calculated / results fields will export the values for the current timestep only. To export all timesteps, see the next method below.

Excel Export from the File Menu

Previously, data could be exported to Excel via the Flextables, but needed to be done one Flextable at a time and did not include time series (results over time). Now, starting with V8i SELECTseries 6 (08.11.06.58+) of WaterCAD, WaterGEMS and HAMMER, and V8i SELECTseries 5 (08.11.05.58+) of SewerGEMS, SewerCAD, StormCAD and CivilStorm, we have added the ability to export data for all the element types at once, including time series.

See also: Export time series data to Excel or CSV format

The following are the steps to use this:

Go to File > Export > Export to Excel

Next, in the Export to Excel dialog, specify properties and elements to be exported by clicking the check boxes. Click OK. Note: these settings will be saved for the next export

In the FlexTable Export dialog, select either Excel or CSV format and specify where it will be exported. On the lower left side of the dialog box you can also choose to export all elements, elements by a selection set, exclude inactive elements, or publish a subset of elements based on the active Flextable filters. Click OK.

Available Platforms

Starting with the release of CONNECT Edition (version 10.00.00.XX or later), FlexTables can be exported to an Excel file in multiple products (SewerGEMS, SewerCAD, StormCAD, and CivilStorm) and for all integrated platforms (MicroStation, AutoCAD, ArcMap, and stand alone).

ModelBuilder Method

Another possible method would be to use ModelBuilder to populate a spreadsheet. This method works well for fields with collections, such as demands or sanitary loads.

1. First, create a new spreadsheet with headers for the data you want to export in the first row of the spreadsheet. The element "Label" should be one of these. Save this spreadsheet, then open the model. Note that if you have a 64-bit computer and a 32-bit version of Microsoft Office, you will need to open the 32-bit version of the program. You can find this executable at "C:\Program Files (x86)\Bentley\<produtname>\<productname>.exe". (such as WaterGEMS.exe for WaterGEMS)
2. Compute the model and then open ModelBuilder. Select the New button. For the Data Source type, choose Excel and select the spreadsheet you just created. Click Next. In the next screen, make sure the units are accurate and click Next. Then click Next two more times. You should be on the field mapping section.
3.  For the Table Type, choose the element type the spreadsheet is for. If you need to export a collection, find the name of the collection. For instance, catchments with subareas will be called "Catchment, Subareas" or sanitary loads will be called "Manhole, Sanitary Loads." Note: Clicking the > button next to the Table Type pulldown will allow you find elements, collections, or Components. For the key field, choose Label. In the lower right, you will see the column headers from your spreadsheet. Highlight on these and choose the appropriate results field from the Property pulldown menu (make sure the units are correct as well). Continue this until you have selected all of the items you want to export. Click Next.
4.  For the option "Would you like to build model now?" select No. After selecting No, click Finish. This will take you back to the ModelBuilder Manager. You should see the title of your spreadsheet listed in the list of ModelBuilder connections. Highlight this and select the Sync Out button. This will update the spreadsheet with the data from your model. Save this file.
5.  Now that the connection is created, each time you compute, you would simply need to open ModelBuilder, choose the connection and click Sync Out.

Please note that the export to Excel from file menu is actually easier to use than Modelbuilder method. We highly recommend using that instead of ModelBuilder to speed the process. You could also use a similar process for exporting your information to an Access database or another database format that ModelBuilder exports to.