The data that makes up a DRAINS model and the results of runs can be transferred to spreadsheet programs using facilities in the Edit menu. Other spreadsheet transfers can be made for any results that can be pasted to the Windows Clipboard.
In the notes below it will be assumed that Microsoft Excel is used. Other spreadsheet programs should operate in a similar manner.
(a) Transferring information from DRAINS to Excel
When copying data and results from DRAINS into Excel, you need to switch between the two programs using by Alt-Tab or the buttons on the menu bar at the bottom or side of your screen. The main copying options appear in the Edit menu as shown below. The second one concerning Results will be greyed out unless a run has been made and results are displayed in the Main Window.
For most applications, you can select Copy Data to Spreadsheet or Copy Results to Spreadsheet. We suggest you paste the data to Cell A1 on one page, and the results to Cell A1 on another page of the spreadsheet. You might rename these pages as 'Data' and Results', by right-clicking on the tags at the bottom of each worksheet and selecting Rename, or by clicking on the tag name until it is highlighted, and altering this directly.
There is a relatively new option Copy Check HGL to Spreadsheet that transfers results of a simple, top-down hydraulic grade line analysis of a pipe system. This uses similar assumptions to the method in Australian Rainfall and Runoff, 1987, namely that all pipes flow full. Its HGL levels are less realistic than those reported from a DRAINS analysis, but they are verifiable using hand calculations.
When information is pasted into a spreadsheet it may not be formatted as you wish and some forms of data may have to be changed. For example, a pit name '29/1' may be interpreted by Excel as the 29th of January. You can format the spreadsheet as text to make it more readable.
The contents of the spreadsheets produced by DRAINS using this facility are described in spreadsheet formats and data.
(b) Transferring data from Excel to DRAINS
The fourth transfer option in the Edit menu can be used to transfer data from a spreadsheet to DRAINS. This facility can be used as a convenient way of editing a large DRAINS model, changing items in the spreadsheet and then transferring these back to DRAINS. It is also possible to develop a spreadsheet model in the required format and transfer this into DRAINS without creating a DRAINS model to start with. In particular, pipes, overflow routes and prismatic channels can be created in a spreadsheet and transferred to DRAINS, nominating the names of the nodes between which they are located.
While it is possible to transfer data for individual components into DRAINS, it is better to transfer data for a whole system, selecting this by clicking on the cell at the top left of a worksheet (between the "1" and "A" headings). This selects the whole sheet, which can then be copied to the clipboard using the Copy option in Excel's Edit menu. After switching to DRAINS, the Paste Data from Spreadsheet option in DRAINS' Edit menu can be used to bring in the data. Error messages may appear if some of the transferred data are unsuitable or incomplete. If the data for a component is accepted, but DRAINS cannot find a component of the same name, it will create a new one.
The easiest way to find the correct format for transferring data is to copy a set of data from a DRAINS file that contains a fully specified example (e.g. a catchment with comprehensive data, a prismatic channel, etc.). You will then see which cells should be filled in and which should be left blank. To get started we suggest you copy data from a working DRAINS file and paste it into Excel. You can then change some data values (e.g. a pipe length) and transfer the amended data back into DRAINS. You might try deleting some essential data (such as a pipe length) and see how DRAINS responds when you attempt to paste the data. When you are comfortable with editing existing data you might try creating new data in Excel and pasting it into DRAINS. To do this you must enter the component in the Main Window, and provide it with a name corresponding to the name of the new component entered into the spreadsheet.
Some changes cannot be transferred, such as altered slopes, which are ambiguous (it is uncertain whether the length, upstream invert level or downstream invert level of the conduit is to be changed). Some are ignored if they conflict with the linkages on the Main Window.
Limitations of this procedure are that you cannot:
Transfer hydrological models, rainfall data, run options and descriptions.
Introduce new pipe types into the DRAINS pipe data base. You can change the pipe type and nominal diameter within Excel. However, the new pipe type name must match the name in the data base exactly (including spaces, commas, etc.). The nominal diameter you specify must be one that is already specified in the data base within DRAINS. Any changes you make to the internal diameter in Excel will be ignored, and DRAINS will read the internal diameter from the pipe data base.
Change the network geometry (e.g. you cannot move a catchment by changing its node name).
(c) Examples of data manipulations using the spreadsheet transfers
The transfers can be used to manipulate DRAINS components in many ways. Some examples are: New components can be added by creating blank lines in a spreadsheet output, entering appropriate data and transferring the data to the DRAINS model. If a component with the name supplied is not found, the new component is added to the model. If it is desired to change, or to accurately locate, the position of a pit or node in the model, the x-y coordinates can be altered in the spreadsheet. However, the object will need to be deleted in the DRAINS model before transferring data back from the spreadsheet. Since the object is not present when the backwards transfer occurs, the position will change.
(d) Other spreadsheet transfers
You can also transfer a number of other sets of information from DRAINS to spreadsheets, and insert data such as rainfall patterns, hydrographs, detention basin and culverts rating curves into DRAINS from spreadsheets.