The spreadsheet copy options in the Edit menu of DRAINS can transfer input data and results to spreadsheets and other programs that can interface with DRAINS.  Data can be transferred both ways - from and to DRAINS.

This has the following advantages:

      (a) The transfer format is in ASCII or CSV (comma separated variable), form and can be read by almost all spreadsheet programs, text editors and word processors. It is also readily readable by data base and GIS programs.

      (b) For design applications, the spreadsheet outputs provide complete documentation of the design inputs and results in a standardised format, which can be presented to approval authorities for checking, or pasted into drawings.

      (c) For analysis applications, the spreadsheet’s facilities can be used to perform many functions that are not available in DRAINS, such as the ranking of components (according to pipe size or flow velocity, for example) or the plotting of a number of hydrographs together.

      (d) As it operates as an input-output, it can be used to transfer data from any pre-processing program into DRAINS, and to transfer DRAINS results to any post-processing program. This can be used for dedicated links such as those with Autodesk Land Desktop and 12d, and programs or spreadsheet macros developed by others. DRAINS is therefore an open system regarding data, which can be easily transferred out and inspected.

      (e) The spreadsheets are ideal for transferring data to and from asset databases. Information is set out in a systematic manner.

Transfers are made from DRAINS’ Edit menu:

For example, you can set up a system in DRAINS and run it in Design mode. Using the Copy Data to Spreadsheet option in the Edit menu, you could transfer the input data describing the system to the Clipboard. You can then open a spreadsheet capable of displaying multiple sheets, such as Excel. Switching to this program, you can paste this on the first worksheet using the Paste option in the right-click pop-up menu. The sheet can be renamed ‘Data’ by clicking on its name on the tag at the bottom and entering the new title.

A Data output from the Horton (ILSAX) hydrological model is shown below.  Similar outputs are available for other hydrological models.

You can then go back to DRAINS and use the Copy Results to Spreadsheet option in the Edit menu to transfer the results of the Design run. After copying this to the second worksheet in the Excel file, you can rename this ‘Design’ or 'Minor'.

Next, you can go back to DRAINS and carry out an Analysis run. You can use the Copy Results to Spreadsheet option in the Edit menu to transfer the results of this Analysis run to a third worksheet, renaming this ‘Analysis’, 'Check' or 'Major'.

From the DRAINS Edit menu, it is also possible to transfer a Check HGL sheet which provides a simplified HGL analysis based on peak flowrates occurring simultaneously.

In this way, pertinent information can be transferred to the spreadsheet. (If your design or analysis involves multiple storms, the worst case results (corresponding to the storm producing the highest flowrates or HGLs) are transferred.)

Data is arranged by type of component - pits/nodes, sub-catchments, pipes, etc. In the sheet containing the data, the x-y coordinates of pits, nodes and detention basins are provided.  This information, and the information on which sub-catchment and pipe is connected to each pit, provides a specification of the system layout.  The id numbers are generated by DRAINS for its own internal purposes and should not be altered to other numbers.  (Blanks can be substituted when performing operations such as tidying up after merging two models.)

The information presented for Horton (ILSAX) and Rational Method procedures will differ. The data and the results for the various components are on similar rows of the sheets for results. For results of runs, a continuity check, applying to the storm deemed to give the general worst-case results, is also presented, together with the comments that appeared in the run log for the run that produced the results. The continuity check adds the volumes of the inflow and outflow hydrographs at all pits and nodes and presents the differences.

It is followed by the contents of the run log, which was presented on-screen after the run calculations finished.

The order in which data is presented is as follows: the assumed main branch, from top to bottom, and then side branches. This facilitates the plotting of long sections.

You need to note that the velocities in the results are those corresponding to the peak flowrates. This may be for full pipe (closed conduit) flow, or for part-full (open channel) flows, depending on the peak heights of the calculated hydraulic grade lines. Open channel flows in a roofed section such as a circular pipe have their greatest velocity and greatest flow capacity at depths less than the full conduit height - typically at about 0.8 or 0.9 times the height. Thus it is possible that a higher velocity might occur than that stated. The difference will be 10% at most.)

More information on how transfers are made  is given in Transfers between DRAINS and Spreadsheets.