Legacy Billing Data File Order and File Format

Billing data must be sent in a particular order and file format.

Data File Order

The Oracle Utilities Opower platform processes files according to the order received, then dated. If we receive multiple files on the SFTP server in the same time frame (as could be done for full historical data files), they will be automatically queued by their filename date stamps.

For example, if a file is received on the SFTP server named opwr_util_full_20140520.txt and then receive one named opwr_util_full_20140519.txt an hour later, opwr_util_full_20140520.txt will be processed first despite the fact that its date stamp is later than the opwr_util_full_20140519.txt. However, if these files are received at the same time, the file name data stamp will take precedence and opwr_util_full_20140519.txt will be processed before opwr_util_full_20140520.txt.

To ensure that all files are queued in the correct order, files should be named so that the date stamps are consistent with the intended load order of the files.

Data Order Matters!
Data (rows) for each service point should always be arranged from the oldest to the most recent to ensure that your data is processed in the correct order.

  • Multiple Usage Reads for a Single Service Point: Any time a single file contains more than one usage read for a single service point covering the same period of time, the most recent read needs to appear last in the file. If the usage periods for those two reads are the same, Oracle Utilities considers the last record read to be a correction. See Billing Data Entity Definitions for more information.
  • Multiple Records for the Same Entity: If there are multiple records for the same entity (such as customer, account, or service point), in the same file, only the last record for that entity is loaded. Oracle Utilities assumes that the last record represents the most current data from the utility.
  • Customer Information: The data in the last row of the last file processed is always the active data in the Oracle Utilities database for that customer.

Back to Top

File Format

All files generated by your extract program need to meet the following standards to load successfully into the Oracle UtilitiesOpower platform:

  • Oracle Utilities prefers to receive all data as tab-delimited text.
  • Files must use UTF-8 encoding so that both Latin and non-Latin characters can be supported.Note: The only fields that do not yet support non-Latin characters are premise_id and email. Contact your Delivery Team if you need to send non-Latin characters for these fields, so that a workaround can be provided.
  • Some fields must have English values. These are fields that have an enumerated set of expected values in the field tables. For example, for the customer_type field, the values must be AGRICULTURAL, RESIDENTIAL, SMB, or OTHER. The Oracle UtilitiesOpower platform can only accept these exact strings.
  • All files must include a header row containing column names from the tables specified in this document. Header column names must exactly match the names specified in this data specification.
  • All rows should have the same number of tabs even if some fields have null values. It is important that null values are represented by empty strings and not denoted by /N, NA, null, or another value.
  • Rows can use "line feed" and "carriage return / line feed" as valid end-of-line separators.
  • It is strongly recommended that files be compressed for transfer. Zip and gzip compression are supported, but gzip is strongly preferred. Files can be sent uncompressed, but compressing the files will greatly decrease the transfer time. Zip files must contain only one file each. There should be no directories in the zip file. Compressed gzip or zip files must contain only one text file each.

Back to Top