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

Importing Utility Invoices

Previous page Next page
  rev. 2011-07-28        

One method for entering large volumes of invoice data is to use the Import Utility Invoices tool, available from any tree node when the Utility Invoices tab is active.  Import Utility Invoices allows you to import a batch of any number of invoices, from any number of accounts using a Microsoft Excel spreadsheet.  Spreadsheets are a common electronic format that may be supplied by a Utility Provider, manually created, or exported from an Accounting or Enterprise Resource Planning (ERP) information system.

Note that any overlap in the dates in existing entries and entries that are currently being imported will result in the existing data being overwritten.  Make sure that dates are unique and current, and that there is no data in the same range in order to avoid accidental changes to data.

Columns must have unique headers.  More than one column with the same header will prevent the spreadsheet from being imported.


                                                            Some clerical staff are very familiar with Excel, and find it easier and faster to create multiple-invoice spreadsheets rather than key  in invoices through the ManagingEnergy invoice entry interface.  Imported invoices are validated in the same way as individually-keyed invoices, so there is no reason not to use this method.

Spreadsheet import has certain less obvious advantages:

Entry Batch functionality.  Batches can be rolled back in one step if there is a problem, and re-imported once the spreadsheet is fixed.
Automatic invoice archive.  Spreadsheets are a universally familiar file format.  Batch spreadsheets can be stored for other uses or simply as a long-term backup.

Spreadsheet Layout

Invoice import spreadsheets must follow a few basic rules:

1.The top row must contain the field headings
2.One field must be named Account, which is the matching key.  This entire column should be in Text format to allow for all kinds of characters and alpha-numeric patterns.
3.Four fields must contain these dates, although the header names are not critical: Invoice Date, Due Date, Beginning Read Date,Ending Read Date. If there is more than one utility being referenced on an invoice, each must have the Beginning and Ending Read Dates defined.
4.Only one invoice per line.
5.Must provide entries for all Meter Components.  Meter components will be highlighted in yellow during the field mapping process
6.In order to ensure that there is no date confusion in Excel due to formatting differences, it is best to enter dates into the spreadsheet using "mmm dd, yyyy"  format. i.e."Jan 01, 2011".

The Import Process

Click on Import Utility Invoices in the Tasks area.  Then browse to the spreadsheet and click Upload once you've selected it.  Click Next once the upload has completed.

Import spreadsheets can have multiple worksheets.  Choose any number of available worksheets to import.

If the invoice is unmatched (account numbers on some invoice records do not match any of the account numbers in the portfolio), ManagingEnergy does nothing with it.  You will  have an opportunity to export unmatched invoice records to another spreadsheet.

The Specify Field Mappings screen allows the user to associate names on the Invoice Import Spreadsheet to the component names used in the Tariff. If the headings are the same as the Tariff Components, this screen will appear filled out for verification by the user.  If they are different, after mapping the fields the mapping will remain whenever the same headings are used for future imports.

There is an option to Specify Utility Invoice Validation Settings. There are two check boxes that allow the enabling or disabling of some basic checks. One validates dates appearing on the invoice, and the other allows you to validate the invoice total against what the software calculates. By default these are enabled.

Errors will appear in the Microsoft Excel Export file that is offered in the Import Complete screen based on what is selected in the Validation Settings screen. The error will be in columns added to the far right of any data entered, and will be highlighted according to severity. Always check this file to ensure that the data has been loaded correctly.

Some possible errors that may come up are:

The calculated value for Component 'InvoiceTotalCalc' exhibited a variance of ('-#.## %') and exceeded your threshold of #.## %. The value calculated by ManagingEnergy exceeds the variance that was input in the Specify Utility Invoice Validation Settings screen.

The due date 'yyyy-mm-dd' occurs before today 'yyyy-mm-dd'. This warning message indicates that the due date for this invoice has passed. This will be a common warning when importing older invoices.

Other errors may indicate issues with the order or format of dates used in importing. Check the import file against the error file to determine if this is the case.


                                                                Be Patient When Importing !

The import process proceeds as a series of steps.  Always wait for feedback before moving to the next step.

Red bars at upper left must be stopped.
Green progress indicator at bottom must be complete.
On the file upload step, the file selection bar must have turned green.

If you click around or try to move on before a step is complete, you can cause and error and force yourself to start over.


Entry Batches

ManagingEnergy supports the concept of Entry Batches.  All invoices imported at the same time become members of a single time-stamped batch.  A batch can be rolled back (reversed).


                                    Entry Batches are identified by User and Date/Time of import.



Related Topics

Import Spreadsheet Layout

Import Profiles

Defining Utility Accounts

Bill Validation


S2_Importing_Utility_Invoice_Spreadsheets         ©2014 Managing Energy Inc.