AMRS Report Conversion
Note: the conversion process may take up to two minutes depending on the amount of data in the CSV file.
The source for this tool is available on GitLab.
Background
The Schneider Electric EcoStruxure™ Power Monitoring Expert CSV format is particularly unhelpful for energy audits and utility billing. It provides the requested meter data in columns with the values provided in fifteen minutes increments for the specified period of time going down in rows. This means that any report for a given calendar year is made up of approximately 35,000 rows. A typical month is approximately 3,000 rows.
For energy audits and utility billing, it would be preferable to have a listing of meter IDs in rows with a roll-up of monthly consumption in KWh in the columns based on date with the older data to the left and newer data to the right.
This program reads the EcoStruxure Power Monitoring Expert CSV, creates a matrix of consumption per month, and then outputs the data in the Microsoft Excel format for use. Details and edge cases are described in-line with the code.
Data Format
The CSV file identifies the group, meter ID, and type of data in each column header. So to identify a meter in group XXXX with a facility ID of 10000 reporting the Real Energy Into The Load has a format like this as the header for each column in the CSV file:
XXXX.010000E01#Real Energy Into the Load#kWh
After the header row, the left-most column contains the timestamp for the value being reported.
2023-10-01 00:15:00
The scipt utilizes regular expressions to extract the meter id and time stamp for processing. They default to extracting the anticipated format described above, but can be modified to recognize different header or date formats.
The data type in each cell depends on what the given column is reporting. Depending on the report settings it could be text, an interger, or in the case of Real Energy Into the Load it is a floating point number.
Dependencies
The script requires Python 3 and openpyxl. If you are running a debian-based Linux distribution, you can run the following command:
apt-get install python3-openpyxl
The script creates an Excel file directly with some styling to make it more useful.