Understanding the Starter Spreadsheet

The Data Exchange Starter Spreadsheet v1.4.1 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 red 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.

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
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
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.
KNOWLEDGE BASE



User Guide