Option C Tutorials

Lesson 3: Importing Bill Data into Your Option C Project

Lesson 3 covers importing utility data into Option C. Some users get their data in electronic format. Rather than retyping the data into Option C, they reformat the data and import it into the program, using the MT1 file format.

MT1 File Format

Utility bill data must be formatted in a certain way in order to import into Option C. MT1 files are Comma Separated Value (CSV) files. To create an MT1 file using Excel, just create a new worksheet and fill in the columns according to the values in the table below. Make sure that the worksheet doesn’t have any headers, just values.

Some restrictions that should be noted:

1. No commas should be in any field.
2. No dollar signs.
3. First 10 columns must have data.
4. The name and account fields must be less than 22 characters.
5. Fields with no value should be left blank (rather than filling them with 0).
6. Files should be saved with an mt1 extension in the filename. (Or, renamed with the mt1 extension in Windows Explorer, after saving as .csv.)

Columns for MT1 Format

Here are descriptions of all the fields in an MT1 file:

ColColColumn NameField Value/Data SourceFormat
1AFormat IDMT1
2BMeter NameMeter NameUp to 22 characters
3CAccount #Account numberUp to 22 characters
4DUtilityDefault value per appendix sent by” src Systems.One alphanumeric character
5EBTU/unit FactorHow many Btus in the Energy Unit, i.e. there are 3413 Btus in a kWh3413
6FBTUH/unitHow many Btuhs in the Energy Unit, i.e. there are 3413 Btuhs in a kW3413
7GYearMeter read date year1999
8HMonthMeter read day month3
9IDayMeter read date day5
10JNumber of daysBill days quantity29
11KTotal CostBilled amount for the account.123456.78
15OOn Peak Quantity Cost
16POn Peak Quantity
17QOff Peak Quantity Cost
18ROff Peak Quantity
19SPartial Peak Cost
20TPartial Peak Quantity
21USuper Off Peak Cost
22VSuper Off Peak Quantity
23WOn Peak Demand Cost
24XOn Peak Demand
25YOff Peak Demand Cost
26ZOff Peak Demand
27AAPartial Peak Demand Cost
28ABPartial Peak Demand
29ACSuper Off Peak Demand Cost
30ADSuper Off Peak Demand
31AEOn Peak rkVAh Cost
32AFOn Peak rkVAh
33AGOff Peak rkVAh Cost
34AHOff Peak rkVAh
35AIPartial Peak rkVAh Cost
36AJPartial Peak rkVAh
37AKSuper Off Peak rkVAh Cost
38ALSuper Off Peak rkVAh
39AMOn Peak Power Factor Cost
40ANOn Peak Power Factor %
41AOOff Peak Power Factor Cost
42APOff Peak Power Factor %
43AQPartial Peak Power Factor Cost
44ARPartial Peak Power Factor %
45ASSuper Off Peak PF Cost
46ATSuper Off Peak PF %

Saving an MT1 File

After preparing your utility data in the MT1 format in Excel, save the file with the type “Comma Separated Values” (.csv). After saving, browse for the file in Windows Explorer, and rename the file with the extension type “.mt1” instead of “.csv”. For example, the final filename should appear as “UtilityData.mt1”.

It is now ready for import into Option C.

Importing MT1 Format Bill Data

When importing MT1 data into existing Meters in Option C, the Meter Name and Account Number fields in the MT1 file should exactly match the Meter Name and Account fields in Option C. These are found in the Meter Details tab.

If the data in an MT1 file cannot be matched to existing project meters, Option C will create new meters for the data.

In these tutorials, we have already set up our Electric and Steam Meters accordingly, so we can now import data into them.

1. Click the Import Icon, in the bottom left of Option C. In the Import Menu, select Utility File Import:

Figure 3.1: Import Menu

2. Click the ellipsis (…) under File Path, to browse for: “OC Sample Proj PreRetrofit Bills.mt1” and “OC Sample Proj PostRetrofit Bills.mt1”. (These will be located under My Documents/Option C.)
3. Select both, and click ‘Open’ to continue.
4. In the Import Menu, click Next.
5. Imported data is now matched to existing project Meters. If matches aren’t found, Option C will create new meters by default. You may view or adjust the import action, under the Action column.
6. In this example, it appears our import data did not match our project meters. Two entries are listed under the ‘Unmatched’ tab, and none under ‘Matched’:

Figure 3.2: Unmatched Import Items

7. The default Action is set to ‘New’ – Option C will create new Meters for the data. We can manually match the import data to our target meters instead. Click on the dropdown menus under the Action column, and select ‘Import/Update’. Then click the adjacent row under the Target Meter column, to browse for the appropriate meter to update:

Figure 3.3: Manually Matching Import Items

8. Repeat for both meters, then click ‘Import’.

Figure 3.4: Manually Matched Import Items

9. After the confirmation screen, click ‘OK’ to return to the Import Menu.
10. Data import is complete. Click on the Project icon, to return to the Tree View, and view your updated Meters. Select your Electric Meter, and click the Bill Register tab. You should see the newly imported utility data populating the register:

Figure 3.5: Bill Register with Imported Data

Check your Steam Meter as well.

Utility data import is now complete. In the next lesson, we will follow similar steps to import Weather data.

<--Previous lesson   Next lesson–>