In order to be able to run the balancing module, the utility must first have all data in place. For utilities that do not currently own the EDAMS Billing & Customer Services module, we have introduced functionality that enables them to import their billing connections and readings using an excel file.
Billing Connections/Readings Excel Template
The excel template, which will be provided, contains all necessary information so that each billing connection and their associated readings can be easily identified by the users.
The excel template is split into two sections, a) Details about the connection b) Details about the readings.
Connection Details
In this section, the user must complete the following:
- SER_NO
- CONNECTION_ID (Mandatory)
- CUSTKEY
- CUSTOMER_NAME (Nice to have)
- PROPERTY_TOWNSHIP (Nice to have)
The mandatory field in this section is the connection id. The customer name is not mandatory, however, it would be very helpful to have it in order to easier identify each connection. If the customer name is left empty, the system will automatically insert ‘Unknown’ as customer. Moreover, the users can also provide the township that the billing connection falls into; having this information will enable the users identify if a connection is assigned to the wrong mass balancing zone or note. Other than the aforementioned data, the users can also fill in the rest of the fields for more comprehensive connection details.
Readings Details
In this section, the user must complete the following:
- READ_DATE1 (Mandatory)
- READING1 (Mandatory)
- MSG1 (Mandatory)
- …
- READ_DATE12 (Mandatory)
- READING12 (Mandatory)
- MSG12 (Mandatory)
Each reading is associated with a date and a message flag. The message is necessary in order to know if the reading was:
(R): Regular/Normal Reading
(N): New meter – Provide First Reading
(E): Estimate – Estimated Reading
All three fields are mandatory in order for the readings to be imported correctly into the system. The excel file must contain at least one reading (triplet of information, reading date, reading and message flag) in order for the connection and its details to be imported into the system.
In other words, the user can import one (1) up to twelve (12) readings in a single import. If more than one reading is captured in the excel file, the user must provide the readings in sequence, i.e. provide the oldest reading first and then the rest going up to the newest reading (See example below).
Even though the template can store up to twelve readings, the utility can use the reading fields as they normally do in their own billing systems. This means, that they can opt to capture the readings:
- Monthly readings
- 3-month readings
The utility can opt to import multiple excel files so they can better manage their data. i.e. one excel file per zone/township.
Connections Graphical Information
Once the connections have been imported into the system, the users need to also update the connections’ graphical information (coordinates) by using an external shapefile. The shapefile must contain at least the Connection ID in its attribute data. The system will use the Connection ID to associate the connection that was imported through the excel file. Consequently, it’s extremely important to check and make sure that the excel file and the shapefile have connection IDs that link to each other. If not, the system will not update their position and balancing will fail since the imported connections will have no graphical representation and therefore cannot be linked to a mass balancing zone.
Shapefile/Excel file links theory
The excel file must be able to be linked with the billing connections shapefile. The shapefile’s primary use is to update the connection coordinates in the database in order to be used during the water balancing procedure. The shapefile must contain point features [one point = one connection] and the only important information needed is the Connection ID that must link to the imported excel file.
Note: The Connection ID in the excel file and the Shapefile must link to each other
