UB> Utilities> Account Export

Account Export

Summary

The Account Export window is used to create an export file (MS Access or CSV) containing Utility Billing customer, meter reading and/or transaction information. If you would like to create an MS Excel spreadsheet of the exported information, create an MS Access database using this process and then import the database information into an Excel spreadsheet. (This process is described in a related document.) This allows you to apply query filters to the information included in the MS Excel Export.

The Account Export window allows you to select which information will be included in the export: customer information, meter information, and financial information.

The Customer information will always be included on the export. The customer information in the export includes the address, deposit, refund, lot owner information, lot information, meter information (but no meter readings), services, and miscellaneous fields on the lot, customer, owner, and meter. The standard customer information also includes the balance forward, bill this period, payment this period, current billing, and ending balance.
The reading history of meters attached to the customer accounts will be included on the export if Meter History or Both is selected in the Include History drop-down menu. The meter history will include reading, reading date, reading period, reading year, consumption, and meter read description.
The transaction history of customer accounts will be included in the export file if Financial History or Both is selected in the Include History drop-down menu. The financial history export will include the batch and transaction information of all transactions for each customer in the batch. The transaction history will not include the detail line items on the transaction.

The files created by this process contain the social security numbers of customers. You can use the Menu Security feature (SS> Maintenance> Menu Security) to disable this menu option on selected user accounts. Click here for information on menu security.

Step by Step

1 Open the Account Export window (UB> Utilities> Account Export).

2 Select the information to be exported.

The Billing Cycles field is used to select the customer accounts that will be included in the export. Check the toggles of the billing cycles you would like to include.
The Account Statuses field is used to select the account statuses that will be included in the export. Check the toggles of the account statuses you would like to include.
- Use the header toggles to select or deselect all the toggles in the Billing Cycle or Account Statuses fields.
The Configuration drop-down menu is used to select a user-configured data export file or to select the standard non-configurable data export.
- User-configured export files are created and maintained on the Export Configuration Maintenance window (SS> Maintenance> Export Configuration). Click here for information on Export Configuration Maintenance.
Select the format of the export file in the Layout drop-down menu.

Select Export to MS Access 2003+ if you would like to create an MS Access database of the exported information. Export the information to an MS Access database. If you are using an earlier version of Access (prior to MS Access 2003), you may not be able to open the file generated by the Account Export window.

If you select this option, the MS Access database will contain the financial and meter history tables but those tables will only contain data if a selection is made in the Include History drop-down menu.
Select the MS Access version that is installed on your workstation. If you have a lower version of access than one of the options shown, you may not be able to export the data. If you have a higher version than the options shown, you should be able to export without any problem.
The MS Access database created by the export will create a database with fie tables: TEMP-BALANCES, TEMP-FINANCIAL, TEMP-MASTER, TEMP-METER and TEMP-METER-HIST.
The TEMP-BALANCES table includes the balance information on each customer account (for example, balance forward, balance due, payments in period, etc.).
The TEMP-FINANCIAL table includes the transactions information of the customer accounts included in the export. This table will always be created but will only contain information if financial information is included on the export.
The TEMP-MASTER table includes the customer, lot and owner information.
The TEMP-METER table includes the meter information of the customer accounts included in the export.
The TEMP-METER-HIST table includes the meter readings on both installed and removed meters attached to the customer account. This table will always be created but it will only be populated with information if financial information is included on the export.

Select the ASCII CSV if you would like to export the information to an ASCII file. The process will create three files regardless of the information you are exporting. The MTR file will contain the meter history information, the FIN file will contain the finance information and the MST file will contain the main customer information.

The CSV format will create six files: filename.csv, filename_2.csv, filename_3.csv, filename_3.csv, filename_4.csv, filename_5.csv, schema.ini. The “filename” will be replaced by the file name entered on the Export Settings window below.
The filename.csv file will display the customer, owner and lot information.
The filename_2.csv file will display the balance (balance forward, billings this period, payments this period, etc.) of the customer accounts included in the export.
The filename_3.csv file will display the meter information.
The filename_4.csv file will display the meter readings if they have been included in the export. This file will always be created by the export process, but it will only contain information if the meter readings have been included in the export.
The filename_5.csv file will display the transactions attached to the customer accounts in the batch. This file will always be created by the export process, but it will only contain information if financial transactions have been included in the export.
The schema.ini file displays the format of the columns included in the export.

Choose which information to export in the Include History field.

Select (N/A) if you would like to export the customer, winter average, lot and meter information.
Select Meter History to include the meter history information in the export.
Select Financial History to include financial information in the export. If you include the financial history in the export, the export file may take some time to generate.
Select Both if you would like to export meter history and financial history.

The Reading Year field will only be enabled if meter read information is included on the export file (if Meter or Both is selected in the Include History field). Enter the meter read year of the meter readings you would like to include on the export.

The Reading Year field will filter the meter readings by the year of the meter read date, not the year of the meter read period. For example, if a meter reading has a read date of 12/31/21, but a meter read period of 01/22, the meter reading will only be included on the export if you enter 2021 in the Reading Year field.