Historic data download manipulation

For data that is older than 1 year, we can zip this up and send to you.

The data will be in folders that split the data into device, month and each text file will be for 1 day.

For example, to see data for device ID 3500 on 20th October 2024, the file would be inside: .../2500/2024-10/2024-10-20.txt

The file contains comma delimited values on each line in the following order:

• id_output: Unique identifier for each data output.

• timestamp: The exact date and time the reading was taken.

• id_device: Unique identifier for each device.

• pulses: Number of pulses recorded.

• flowrate: Instantaneous flow rate (litres per minute).

• total: Cumulative total reading of the meter (litres).

• totalLowFlow: Total low flow/drip reading (litres).

• dt: Time difference between readings (seconds).

If your primary goal is to determine the consumption (change in total reading) over a specified period. This is achieved by subtracting the total reading at the beginning of the period from the total reading at the end of the period. Therefore you will likely only be interested in:

• timestamp: The exact date and time the reading was taken.

• total: Cumulative total reading of the meter (litres).


Step-by-Step Guide to Excel Manipulation

1. Open file or Paste into Excel: Open a new Excel workbook and paste the data into cell A1.

2. Use 'Text to Columns' (if necessary): If your data pastes as a single column, use Excel's "Text to Columns" feature:

   • Select the column containing your data.

   • Go to the Data tab on the Excel ribbon.

   • Click Text to Columns.

   • Choose Delimited and click Next.

   • Check Comma as the delimiter and click Next.

   • Click Finish.

3. Add a new column: In cell I1 (or the next available column header), type Period Consumption.

4. Enter the formula: To get the difference between the 'total' values at time1 and time2, perform the following:

   • In cell I2 (assuming your data starts from row 2), you'd manually find the total value for time2 and the total value for time1.

   • Enter the formula for cell references in those specific cells, e.g., =F45-F2.

   • Press Enter. This will give you the consumption for that specific period.

5. Additional Useful Excel Tips

• Filtering: Use Data > Filter to quickly view data for a specific id_device or a particular date range.

• Conditional Formatting: Highlight anomalies or significant changes in flowrate or pulses.

  • Select the flowrate column.

  • Go to Home > Conditional Formatting > Color Scales (or Data Bars).

• Charts: Visualize trends in flowrate or total over time.

  • Select timestamp and total columns.

  • Go to Insert > Recommended Charts and choose a Line Chart.

• Formulas for Time Differences: The dt column already provides a time difference, but if you needed to calculate it from timestamps, you can subtract time values directly, then multiply by 24*60 for minutes or 24*60*60 for seconds to convert to desired units.

By following these steps, you can effectively manipulate your meter data in Excel to gain insights into consumption patterns and identify important trends.