Interval Data import

Import multiple meter readings for one or more meters using:

  • Interval Data Import (located in the Interval Data module).
  • With Create (plus sign).

Interval data is tracked for a meter

Interval data is tracked at the meter level on channels. A channel tracks the data for a specific time interval and a specific data type. Meters can have many different channels (for example a 15-minute channel and a monthly channel).

How to get your interval data

  1. Typically you work with your IT department or meter vendor to coordinate the file formats and file locations. What’s most important is to identify the sources of your interval data, determine the available file outputs, and what capabilities are available in those systems to deliver the files on a regular time interval. Common questions to ask IT: What file formats can they give you? Can the formats be customized? Where will the files be located? How often can the systems provide the files?
  2. The metering system uses a meter import ID or channel ID to identify each of the meters. This ID needs to be entered into UtilityManagement. You can use spreadsheets to enter the meter import ID or channel ID in UtilityManagement.
  3. At a minimum, your interval data file needs to contain the meter or channel import ID, timestamp, and interval data value. It can contain other information that can be mapped or ignored when you import the file.
  4. Create an import format to correctly load your file. While different import formats let you import a variety of data with your file, the file does need to follow several formatting rules explained below.

How to import interval data

  • Import files manually.
  • Use APIs and automation scripts to schedule the import.
  • Use service to obtain and upload interval data.

Create an interval data format

Before you can upload your interval data file you need to create an interval data format. This format maps the columns in your file to specific data fields in UtilityManagement.

Your interval file needs to be a flat file delimited by comma, tab, or space.

  1. A descriptive name helps you choose the correct import format.
  2. Choose the time interval for the data in your file.
  3. Choose the timezone for your file. Use the timezone of data in the file. Often the file is in UTC. UtilityManagement automatically converts the times to the timezone assigned to the meter.
  4. Is the timestamp one or two columns? Enter the format of your date and time. This can be a tricky step and more details are included below.
  5. How many header rows are in your file and should be ignored by UtilityManagement?
  6. What is the delimiter for the file? A comma-delimited file cannot have commas in the reading numbers. (Yes -> 432100.98, NO -> 432,100.98)
  7. Are any of the readings estimated? Enter the value that indicates estimated readings.
  8. Are notes included in the file?
  9. Choose meter import ID or channel import ID and then pick the corresponding column.
  10. What column contains the timestamp? Choose it from the menu.
  11. What column contains the interval data? Choose it from the menu

add new interval data import format

Estimated readings and notes

Estimated readings can be imported when the actual readings are unavailable. After selecting Readings may be estimated, an additional column header is available to map.

If bills are created from estimated readings they are tagged as estimated bills.

Notes from meter readings are displayed on the Interval Data tab and on any bills created from that reading.

import format

Upload your file

Additional details

  • Imported readings with 24:00 timestamp are imported as 00:00 for the next day. Example: 1/1/2021 24:00 is imported as 1/2/2021 00:00.
  • If your file contains a variety of commodities, choose Value as your Units.
  • When defining the format, choose the time zone of the timestamp in the data file. Times are converted and stored in the time zone of the meter.
    Example: The metering system provides an interval file using UTC as the time zone and the meters are configured with the time zone of their location. In this case, the timestamp is converted to the time zone of the meter.
  • The interval timestamp indicates the end of the interval.
  • The file import cannot be reversed. Options to correct an import include:
  • Overwrite with a new file. When the date timestamps are the same, old interval data is overwritten with new interval data.
  • Delete the readings from each meter manually.
  • The Update Readings spreadsheet is useful to update or delete multiple incorrect readings.
  • Files can be loaded in any order.

Timestamp format and examples

Year: yyyy, yyy, yy
Month: MMMM for name ("March"), MMM for partial name, MM for 2 digit ("02"), M for 1 or 2 digit ("1" or "12")
Day: dddd for full name, ddd for partial name ("Sun"), dd for 2 digit ("01"), d for 1 or 2 digit ("1" or "12")
Hour: h for 1 digit, 12 hour time, hh for 2 digit, 12 hour time, H for 1 or 2 digit ("1" or "12"), 24 hour time, HH for 2 digit 24 hour time
Minute: m for 1 or 2 digit time ("1" or "12"), mm for 2 digit time ("02")
Second: s for 1 or 2 digit seconds ("1" or "12"), ss for 2 digit seconds ("02")
AM/PM: t for 1 character ("A" , "P"), tt for 2 character AM/PM

Example formats

Here are some popular date formats examples.

SAMPLE DATE      September 5, 2024 2:03:08 PM
Format of data in interval file Timestamp format to select
2024-9-5 14:03:08 yyyy-M-d H:mm:ss
20240905 14:03:08 yyyyMMdd HH:mm:ss
09/05/2024 02:03:08 PM MM/dd/yyyy hh:mm:ss tt

Example for each option 

September 5, 2024 2:03:08 PM

YEAR
yyyy 2024
yyy 2024
yy 24
MONTH
MMMM September
MMM Sept
MM 09
M 9
DAY
dddd Saturday
ddd Sat
dd 05
d

5

HOUR
h 2
hh 02
H 14
HH 14
MINUTE
m 3
mm 03
SECOND
s 8
ss 08
AM/PM
t P
tt PM


Edit or delete a format

If your file format changes you can edit your interval data import format. When you no longer need the interval data import you can delete it.

manage data import formats

Examples import files

Three column monthly readings.

csv fileimport format

Four column monthly readings. 

csv file

import format

Import counter data

A counter, similar to a meter, tracks items for a place. The ability to manage counters is available in the installed client.

Examples

  • Number of boxes shipped per day for a warehouse 
  • Number of visitors per day to a museum.

Interval data import

Interval Data Import lets you

  • Add a comment to the import
  • Download an error spreadsheet
  • Filter the list by date, user, or status
  • Perform imports

interval data import results

The ability to import interval data can be controlled with permissions.

Permissions

A report of user roles and their permissions is available for download on the Users and Roles page.

Custom user roles are created by administrators.