Navigation:  Module 1 - Energy Accounting > Accounts & Invoices Tab > Importing Utility Invoices >

Import Spreadsheet Layout

Previous page Next page
  rev. 2011-07-28        

This section should help you understand what ManagingEnergy expects to see when you are importing utility invoice spreadsheets.


                                                              Import Spreadsheets and Tariffs

Fields (columns) in the spreadsheet are matched against the inputs needed for invoices.  Those inputs are defined by the tariffs attached to the utility accounts.  Before laying out an invoice import spreadsheet, make sure the tariffs are properly defined and assigned to the utility accounts.


Import spreadsheets may contain invoice records for one or many Accounts, on one or more worksheet pages, with one invoice record per line.  The invoice records are not required to be in any particular order.


                                                            ManagingEnergy uses Account Numbers to match invoices to accounts, so it is possible to include invoices from various Utility Providers on a single sheet.  However, invoices for different tariffs will typically have different Meter Reading fields.

The easiest way to import invoice records from more than one tariff in the same file is to organize the invoices in multiple pages (worksheets), with one worksheet for each tariff.

A second option is to have one sheet for each account, though this results in a greater number of sheets in the import workbook.


Although there is some layout flexibility, import spreadsheets must follow a few rules:

Must be a Microsoft Excel spreadsheet with  the *.xls file extension.
At a minimum, the spreadsheet must contain fields for the Account number, the Start Date (same as the reading date for the previous invoice), the Reading Date, each Meter Reading, and the Final Total amount (in $ or other currency).


                                                             There is no need to include fields that can be calculated by the tariffs.  Minimize data entry effort by letting the ManagingEnergy tariff engine do the work.


ManagingEnergy creates three record-keeping fields by default: Invoice Date, Due Date, and Estimated. Estimated is a flag indicating those times when a guess is used rather than an actual reading. Estimated is a True/False value, and by default is False for all invoice entries.  To stipulate an estimated reading, use either True or T.  Actual readings are specified by False or F.


                                  Start Date Conventions.  Most Utility Providers set the Start Date for a invoice to be the same as the Reading Date of the previous invoice.  But there are some providers who use the day following the previous reading date.  In this situation, for example, a November invoice could have a Reading Date of Nov.28.  The following (December) invoice would show a Start Date of Nov.29.

ManagingEnergy can handle either situation.  Internal date validation accepts a start date within one day of the previous reading date, and all internal calculations automatically close the one-day gap.


The first row contains the field names.  The rows underneath contain the invoice details.
Although it is convenient to have Field Names match the Tariff Component names, they don't have to match.
Field Names cannot begin with a blank space, and cannot include these special characters. ! . ` [ ( ) ]
Records may contain fields that are not imported into ManagingEnergy.
Empty rows are ignored.
The import process assumes a field data type based on the values and formats in the first few records.  Possible data types include Number, Text, Date, and Currency.  If the data type in a column changes part way down the list of records, the entire spreadsheet will be rejected.  This commonly happens when a text character is added to a numeric value during data entry.
Text fields beyond 100 characters in length are not allowed.
Check date fields carefully.  An improper date in one field (e.g. Apr 31) will cause several records to fail.
Formulas in cells.  In our testing, import spreadsheets produced by Excel can include formulas in the cells.  The associated values will be imported cleanly.  However we have found that spreadsheets produced by other programs, such as OpenOffice Calc, are not as compatible.  Formulas in those spreadsheets may be imported as Null values, ruining the import process.  The best policy is to convert formulas to constant values before importing.


                                                             Use the spreadsheet Copy and Paste Values functions to convert formulas in import spreadsheets into constant values.

Import integrity checking is very processor-intensive, sometimes taking over a minute for a large batch.  The more records in the spreadsheet and the more unnecessary columns in the spreadsheet, the longer the import process will take. Wait for the red progress bar to complete before trying to move on.


Related Topics

Importing Utility Invoice Spreadsheets

Import Profiles


S2_Import_Spreadsheet_Layout         ©2014 Managing Energy Inc.