In the newer versions of EPA SWMM after (5.0.1.11), there is a new feature of allowing for Add-ins and third-party tools. One such Add-in, the Microsoft Excel, can be very helpful for input data editing and model calibration.
1. To activate the Add-in
This process is detailed in pp. 141 of the EPA SWMM manual (http://www.epa.gov/ednnrmrl/models/swmm ... manual.pdf). Basically the user needs to go to "Tools->Program Preferences->Configure Tools" on SWMM main menu. Then in the pop-up "Tool Options" menu choose "Add." A "Tool Properties" window will pop-up, and the user can assign a name to the Excel Add-in for the "Name" field. For the "Program" field, the user needs to navigate to the location of the Excel executable file at "C:\Program Files\Microsoft Office\Office10\Excel.exe" (the file path may vary). Leave the "Working Directory" field as blank, and choose "INPFILE" macro for the "Parameters" field. Check both "Disable SWMM while executing" and "Update SWMM after closing."
After the above is set up, click OK and the Excel Add-in is registered in SWMM5. The Add-in tool is under the "Tools" menu. One important thing now is to go to "Tools->Program Preferences," and in the pop-up window check "Tab Delimited Project File."
2. Use the Excel Add-in
The SWMM5 input file by default is a tab-delimited .txt file. The user can view the file using Wordpad, but the editing is not very convenient, especially when it comes to calibration for a watershed with large number of subbasins. The Excel Add-in provides great relief for such operations.
Create a simple watershed model in SWMM, and then go to "Tools->Excel Editor (or whatever the user names the Add-in)." The input file for the watershed model is then displayed in tab-delimited format in Excel. In this environment, the user can edit the input data much easier (as compared to double-click each model component and key in the values in the Graphic User Interface). This becomes more apparent when the number of subbasins increases. When the editing is finished, close the Excel program, and then click "YES" or "OK" to all the pop-up windows. After that, the SWMM model interface pops back and the input parameters are updated.
So with this knowledge the model setup process can be much easier. In the initial model setup, the user may not bother to input any parameter values (i.e. subbasin area, width, slope, etc.). Instead, the model can be delineated and all components represented. Then the user can open the "Excel Editor" and copy/paste the model parameter values from another table of pre-created input parameter values (which is always the case). This process will totally by-pass the manually key-in of parameter values.
The second case of this feature applies is the model calibration. In a traditional way, suppose the user needs to change the value of depression storage for the impervious area. That means for a 30-subbasin watershed, the user needs to roam around the watershed and double-click 30 times to finish that single parameter change. Imagine if it takes five times to find the best value for that single parameter. With this feature, the user can open up the input file, set a depression storage value for the first subbasin, and then drag down for all the other 29 subbasins. Close Excel and go back to SWMM, and the updated model can be ran immediately.
Source: http://ceeforums.com/forum/viewtopic.php?f=26&t=256&p=582#p582
You need to be a member of SWMM 5 or SWMM or EPASWMM and SWMM5 in ICM_SWMM to add comments!
Replies
I have connected with excel..but how can I do calibration
Hi Bob,
I was able to access the input file using the excel executable file. However, when I made a change to the data and closed the executable file, I received the message:
"Do you want to save the changes you made to 'swm32F6.tmp'?
I then clicked 'yes' and saved the file to 'swm32F6.tmp'. I then received the message:
"swm32F6.tmp" may contain features that are not compatable with Text(tab delimited). Do you want to keep the workbook in the format?"
I then clicked 'yes'. The executable file then closed and opened the SWMM file. I then received the message:
"List index out of bounds"
I then found out that my input data was not modified. Do you have any suggestions on how to correct this issue?
Thanks, Tom
I tried various options and the only thing that worked for me was to save the file as a txt when i was in Excel - that saved the changes and was usable. I think the problem is that the tmp file directory is not being recognized.
This works for me but I need to save the changed file as another name when exiting Excel
My experience has been to save the file in Excel after editing (pressing the save icon or Office Button>Save) which will produce a warning "filename.tmp may contain features that are not compatible with Text (Tab delimited). Do you want to keep the workbook in this format?" to which I reply yes. Then when I close Excel I receive the prompt "Do you want to save the changes you made to 'filename.tmp'?" to which I reply no. The changes made in Excel are then present in SWMM. I am using EPA SWMM 5.0.022 and Excel 2007 SP2. Referring to Bob's image above, I believe it is important to have "Disable SWMM while executing" and "Update SWMM after closing" both checked.
Thanks for this tip, Bob. It was really helpful for editing and finding erroneous entries easily. The key is making the input file tab delimited. Are there plans to allow the report file to be tab delimited to be able to open and extract/use the results in Excel easily?
I will check, it does not seem that hard
You add a /t to the code for those of you who use MatLab. For example, here is the node inflow table
fprintf(Frpt.file, "\n \t%-20s", Node[j].ID);
fprintf(Frpt.file, " \t%-9s", NodeTypeWords[Node[j].type]);
getElapsedTime(NodeStats[j].maxInflowDate, &days1, &hrs1, &mins1);
fprintf(Frpt.file, FlowFmt, NodeStats[j].maxLatFlow * UCF(FLOW));
fprintf(Frpt.file, FlowFmt, NodeStats[j].maxInflow * UCF(FLOW));
fprintf(Frpt.file, " \t%4d \t%02d:\t%02d", days1, hrs1, mins1);
fprintf(Frpt.file, "\t%12.3f", NodeStats[j].totLatFlow * Vcf); //(5.0.014 - LR)
fprintf(Frpt.file, "\t%12.3f", NodeInflow[j] * Vcf); //(5.0.014 - LR)
fprintf(Frpt.file, "\t%12.3f", NodeOutflow[j] * Vcf); //(5.0.023 - RED)
and this is how it looks