Importing Utility Bills from a File
Before using the bill import wizard for the first time, we recommend that you:
- Read Rules for Bill Import to get an overview of the various options and rules for the import file. Much of this information is also included with the sample bill import file mentioned next.
- Download the Sample Bill Import File for examples of what bill import data formats.
Once you have a file ready to import, follow the steps below to process the file.
Using the Bill Import Wizard
- All bill imports are done through Bill Processing. From the main menu select Bill Processing. Select Enter Bills on the left.
- Click Import Bills to launch the wizard. The first page of the wizard provides some general overview items covered in this help. From this first page you can download a Sample Bill Import File which contains further information to help you use your existing spreadsheet data. Click Continue to proceed.
- In the File Selection tab, select which batch you want these bills to be added to. If you create a new batch, you can accept the batch name provided or modify it for your needs.
- Select the file type and then Browse for the file.
- Click Continue. The file will be uploaded to the server and prepared for the next step. This can take up to 30 seconds depending on the size of your import file.
- On the Profile screen select an existing profile or create a new one. Profiles are used to help you quickly import files that have the same format. Once you've mapped the columns in the next step, the same mapping will be reused the next time you use this profile. This saves time and ensures accurate mapping. Profile names must be unique.
- The Column Mapping screen is the most essential step in successful data import. In this step, the data in the import file is matched with the corresponding import fields in UtilityTrac Plus. The Import File Column displays header data from the first row of your import file. To map your import data to the desired field in the
- Plus database, click the drop-down arrow and select the UtilityTrac Plus data label that best matches. You can scroll through the list OR use the keyboard to type the first letter of the data label you wish to map. UtilityTrac Plus will try to automatically select the correct data label based on your column heading, if a match can be found. Otherwise, you must select the appropriate values.
- You will see that some drop down list choices are Orange. This means that this Data Label is an "informational" field that is not rolled up on reports and charts. The most common informational fields are electric and natural gas "supply usage" -- the consumption reported on a deregulated supplier bill. Typically, both your local utility company distributor (the LDC - Local Distribution Company) and your supplier of choice show the SAME usage on their bills. It's common to track the usage on both bills, but you don't want to count it twice on roll-up reports and charts. So use an orange informational data label for the supplier (the electric generator or the gas wellhead) to enable both bill-level tracking and accurate roll-ups.
- Be sure to select the date format that matches the dates in your file most closely. All dates must use the same format.
- Data items normally found on the top portion of a utility bill (Account Code, bill dates etc.) are listed first in the drop down list, followed by an alphabetical list unique bill line items choices. The Advanced options allows you to add new items to the list of Data Label choices.
- Select Do Not Import for any columns that should be ignored by the importer.
- Required fields for mapping to the database (Account Code, Start Date, End Date) are indicated in bold type with an asterisk.
- All required fields must be mapped or the import will fail.
- When a usage value is mapped, a supplemental Units of Measure column will appear; make certain that the appropriate usage unit is selected.
- After verifying that all data to be imported has been mapped correctly, click Continue.
- The Import Wizard will display the first three lines of data from your file on the Sample Data tabs, mapped to the column you selected during Column Mapping. Verify that the bill data is mapped as expected and click Import. If something isn't mapped correctly, click Back and modify the column mapping.
- Click Import to begin the import process. For large files a progress indicator and estimated time remaining will be displayed. Under normal circumstances 10,000 bills should take between 8-10 minutes to import.
- The results from your import will be displayed once the bill import has completed. Note the batch name for the bill import, the total number of bills imported, and the total number of bills which failed to import. If these numbers do not match what you expected to see, examine the import batch using the Filters tab at the top of the bill list.
*Note: To investigate/correct import errors, click Import Errors, or select Bill Import Errors from the menu on the left. Bill error records include the reason the bill did not get imported as well as a form for editing the data so that a re-import can be attempted.
Addressing Import Errors
To display import errors, click Bill processing > Bill Import Errors. The associated list will include the Account Code (if it was provided in your import file), bill error message, created date, and the User ID associated with the import.
To correct the bill error, click the Pencil icon to open the Import Errors window for that bill. You can then edit the billing information. The most common error reason is Account Code Not Found, which means that the Account Code in the import file does not match up exactly with an Account Code in the database. To correct, you can edit the Account Code in either the kickout record or in UtilityTrac Plus. When done, click Save to save the corrected bill to the Import Errors folder. Or click Re-import to import the bill again. If successful, the bill will be removed from the Import Errors folder. Repeat the process for any remaining bills in the errors folder.
Instead of editing a kickout, you may want to delete the kickout, edit the import file and re-import the entire file. To delete a bill error kickout record, click the Trash Can icon, and confirm the delete by clicking Yes at the prompt.
Deleting Bills from a Faulty Import Bill Batch
If bills are imported using the Create a New Batch option, and a problem with the bill import is discovered after the billing data has been successfully imported, simply go to Bill Processing and click Bills by Bill entry Batch in the Bill Lists. The list of Bill Batches will be displayed. Click to select the problem batch; then click Delete. The entire bill import batch, with all associated bills, will be deleted from the UtilityTrac Plus database. You can then try the import again.
If bills were imported to a previously-open batch, click on the Display Batch drop-down arrow from Bill Processing > Enter Bills. Then select the desired batch to display the bill list. Manually select all bills to be deleted, and then click the Delete Selected Bills option.