The Data Exchange Starter Spreadsheet v1.4.6 contains a subset of data fields (columns) from the Data Exchange Specification for Accounts and Assets. It is provided in .zip format. To use it, download the file and double-click it to expand the ZIP file. Notice it is in comma-separated values (.csv) format for your convenience. Open the spreadsheet file using a text-editor such as Notepad++ of Sublime Text. Do not use Excel, which adds a near infinite number of blank rows and columns to the file. Using the guidelines in the table below, complete the spreadsheet, save it in comma-separated values (.csv) format and import it into AquaCUE.
Tip: Save the ZIP file. Any time you need a blank Starter Spreadsheet, just expand the Zip file again.
Use the Data Exchange Starter Spreadsheet to quickly associate endpoints with facility account information in AquaCUE. When using this workflow, some advanced AquaCUE features will not be enabled. This method is recommended for ORION Cellular 10-Pack Customers and facilities that wish to quickly provision a small number of endpoints.
Completing an AquaCUE Starter Spreadsheet
The table below describes each field in an AquaCUE Starter Spreadsheet. The data is a combination of information collected in the field, typically during endpoint installation, and data from your billing, accounting and meter management systems. Required data fields are color-coded in blue italic text. Recommended fields are color-coded in red non-italic text. Complete a separate row of facility data for each endpoint/encoder/meter at a location. Compound meters require a separate row for each encoder register.
Service_Point_Longitude
(SP_Longitude)
Longitude position of the Service Point in decimal degrees; provided separately from Location Latitude in case a more specific location for the service’s meter hookup is available.
Max. length: 15 characters
| Column Name | Description |
|---|---|
| Endpoint_SN* *Required for all meters with endpoints. For manually read meters, leave this field blank. |
Serial number of the endpoint paired with the specified meter. Max. length: 20 characters |
| Endpoint_Type* *Required for all meters with endpoints. For manually read meters, leave this field blank. |
A letter indicating the type of endpoint used to read the meter. ORION Cellular (J), ORION SE/ME (N), ORION CE (Z), GALAXY TR3 (G), GALAXY TR2 (g), Magnetic Barnacle (L), Pulse Barnacle (K). 1 letter: J, N, Z, G, g, L or K |
| Endpoint_Install_Date** **Recommended for all meters with endpoints. |
Date the endpoint was installed on the meter specified by Meter_ID. If this field is left blank, for ORION Cellular endpoints, the date defaults to the latest endpoint activation date. For all other endpoint types, the date defaults to noon on the date of the import in the service point timezone. Max. length: 10 characters |
| Account_ID | Identifier of the account used for billing purposes. Max. length: 32 characters |
| Account_Full_Name | Full name of the account holder. Max. length: 128 characters |
| SA_Start_Date** **Recommended. |
Date service began. The combination of Account_ID, Service_Point_ID, and SA_Start_Date is a distinct, unique Service Agreement. If this field is left blank, and the account is associated with a meter connected to an ORION Cellular endpoint, the date defaults to the latest activation date of the endpoint. For accounts associated with meters connected to any other endpoinnt types, the service agreement start date defaults to noon on the date of the import in the service point timezone. Max Length: 10 characters |
| Location_ID | Identifier assigned to the physical location of the meter. Max. length: 40 characters |
| Location_Name | This is typically the name of the property. Max. length: 64 characters |
| Service_Point_ID | Identifier used to distinguish between multiple service hookups at the same Location. The combination of Location_ID and Service_Point_ID represents a single point to which a meter is or will be attached. This value MUST be provided if multiple services are present at a single Location. If no Service Point ID is provided, the Service Point ID will be assigned a default value of 1. Max. length: 20 characters |
| Location_Address_Line1 | The street address where the meter is located. Max. length: 64 characters |
| Location_Address_Line2 | Second line for entering the street address where the meter is located. Max. length: 64 characters |
| Location_City | The city where the meter is located. Max. length: 64 characters |
| Location_State | The State or Province where the meter is located. If the service is located in a country other than the United States or Canada, leave this field blank. Max. length: 2 letters |
| Location_Zip | ZIP+4 or postal code where the meter is located. Max. length: 10 characters |
| Service_Point_Route | Route or book identifier the metered service belongs to. Max. length: 12 characters |
| Service_Point_Type | Type of service being measured. Water (W), Gas (G), Electric (E), Irrigation (I). If not specified, the default is Water.
1 letter: W, G, E, I or blank |
| Service_Point_Class_Code |
Bill rate of meter defined by the billing system; residential, commercial, irrigation, etc. Max. length: 12 characters |
| Service_Point_Latitude (SP_Latitude) |
Latitude position of the Service Point in decimal degrees; provided separately from Location Latitude in case a more specific location for the service’s meter hookup is available. Max. length: 15 characters |
| Meter_ID | User-defined identifier of the meter. It must be unique and is required. Max. length: 24 characters |
| Meter_Install_Date** **Recommended for all meters. |
Date the meter was installed. If this field is left blank, and the meter is connected to an ORION Cellular endpoint, the date defaults the latest endpoint activation date. If the meter is connected to any other endpoint type, the install date defaults to noon on the date of the import in the service point timezone. Max. length: 10 characters |
| Register_Number | For compound and composite meters only: identify the high (H) and low (L) register by entering 2 or H for the high side of the register and by entering a 0 or L for the low side of the register. Leave blank for single-register meters. NOTE: For compound and composite meters, the Location_ID, Service_Point_ID and Meter_ID are expected to be the same for all registers. Leave this field blank for single-register meters. 1 letter: H, L or blank |
| Register_Unit_Of_Measure | Entries in this field must be in all caps and exactly match one of these terms: GAL, CUBIC_FEET, CUBIC_METERS, LITERS, AF. Max. length: 12 characters |
| Register_Resolution | Factor of the rightmost movable digit (0.01, 0.1, 1, 10, 100, 1000, etc.). Taken together with the Register_Unit_Of_Measure this represents the smallest unit of change the encoder can report. Max. length: 6 numbers |
| Meter_Manufacturer | Manufacturer of the meter. For example: Badger Meter, Sensus, Neptune, etc. Max. length: 32 characters |
| Meter_Model | Model of the meter. For example: M25, M55, T160, etc. Max. length: 64 characters |
| Meter_Size | Numeric size of the meter. For example: 5/8 = .625, 3/4 = .75, 1-1/2 = 1.5, etc. Max. length: 10 numbers |
| Meter_Size_Unit | NPS or DN. Specifies whether the meter size is in inches (NPS) or millimeters (DN). If left blank, unit defaults to inches. Max. length: 3 letters |
| Last Update | What Changed | What You Need to Do |
| January 14, 2022-Version 1.4.6 | Added Service_Point_Latitude and Service_Point_Longitude files. | Use the new fields as needed. |
| October 7, 2016-Version 1.4.2 | The Meter_ID field now supports up to 40 characters. The Endpoint_Install_Date, SA_Start_Date and Meter_Install_Date descriptions reflect new default behavior for when no dates are given in those fields. | Use the expanded Meter_ID character limit as needed. Note the new default behavior for when no endpoint or meter install dates and no service agreement start dates are provided. |
| May 2, 2016-Version 1.4 | Added three columns: Endpoint_Install_Date, SA_Start_Date and Meter_Install_Date. | Enter the dates for when each endpoint and meter was installed along with the corresponding service agreement start dates. |
| April 21, 2016-Version 1.3 | Added a new Meter_Size_Unit column to allow facilities to specify meter sizes in inches or millimeters. | If you prefer metric sizes, use this field to designate millimeters when entering meter pipe diameters in the Meter_Size column. |
| March 28, 2016-Version 1.2 | Added Endpoint_Type as a required field for all non-manually read services. | If you are using this BDE spreadsheet, add this field to your BDE files. |
| March 24, 2016-Version 1.1 | Removed the Billing_Unit_of_Measure and Billing_Resolution fields due to changes in how BEACON gets data from endpoints. | If you are using this BDE spreadsheet, remove those fields from your BDE files. They are no longer needed. |
