Export portfolio meter data to CSV

In this guide, we will show you how to extract meter data for multiple sites from the CIM API and export it to a CSV file. This could be handy if you're looking to analyze this data in third-party spreadsheet or BI tools or if you need to generate reports for offline usage.

1. Install Dependencies

Before we start, make sure to install the necessary libraries. We will use axios for HTTP requests and json2csv to convert JSON data to CSV.

Open your terminal and initialize a new project:

npm init -y

Install these libraries by running the following commands:

npm install --save axios json2csv

2. Authentication

To follow this guide, you will need to have your CIM API Refresh Token. If you are on the Enterprise Plan you can request this by contacting CIM support at support@cim.io.

Here is a function which fetches an Access Token using your Refresh Token.

const axios = require('axios');

// Refresh token
const REFRESH_TOKEN = 'YOUR_REFRESH_TOKEN'; // replace with your actual refresh token
const CLIENT_ID = 'api-external';

// OAuth 2.0 endpoint and client details
const URL = 'https://login.cimenviro.com/auth/realms/cimenviro/protocol/openid-connect/token'

// Function to get new access token using refresh token
let getAccessToken = async () => {
  const options = {
    method: 'POST',
    url: URL,
    headers: {'Content-Type': 'application/x-www-form-urlencoded'},
    data: `grant_type=refresh_token&refresh_token=${REFRESH_TOKEN}&client_id=${CLIENT_ID}`,
  };

  const response = await axios.request(options);
  return response.data.access_token;
}

3. Fetching Meter Data and Exporting to CSV

Here is a script to fetch meter data across multiple sites from the CIM API and write it to a new CSV file.

In this section, we will utilize the getAccessToken function we defined in the Authentication section above. This function fetches an access token which is then used to authenticate our requests to fetch meter data.

Create a new file in your folder named main.js and copy and paste the script below. Replace YOUR_REFRESH_TOKEN with the the Refresh Token you received from CIM.

// main.js

const axios = require('axios');
const json2csv = require('json2csv').parse;
const fs = require('fs');

// Refresh token
const REFRESH_TOKEN = 'YOUR_REFRESH_TOKEN'; // replace with your actual refresh token
const CLIENT_ID = 'api-external';

// API URL Endpoint for meters
const API_ENDPOINT = 'https://api.cimenviro.com'

// Array of site_ids
const site_ids = [3, 148]; // replace with your array of site_ids

// Date Range for meters endpoint
const start_date = '2023-01-01';
const end_date = '2023-06-01';

// Aggregation period for meters endpoint
const aggregate_period = 'P1M';

// OAuth 2.0 endpoint and client details
const URL = 'https://login.cimenviro.com/auth/realms/cimenviro/protocol/openid-connect/token'; 

// Function to get new access token using refresh token
let getAccessToken = async () => {
  const options = {
    method: 'POST',
    url: URL,
    headers: {'Content-Type': 'application/x-www-form-urlencoded'},
    data: `grant_type=refresh_token&refresh_token=${REFRESH_TOKEN}&client_id=${CLIENT_ID}`,
  };

  const response = await axios.request(options);
  return response.data.access_token;
}

// Get meter history for a single site
let getMeterDataBySite = async (site_id, accessToken) => {
  const options = {
    method: 'GET',
    url: `${API_ENDPOINT}/sites/${site_id}/meters`,
    params: {
      utility_type: 'Electricity',
      reportable: 'true',
      group_by: 'site',
      aggregate_period,
      start_date,
      end_date
    },
    headers: {
      accept: 'application/json',
      Authorization: `Bearer ${accessToken}`
    }
  };

  const response = await axios.request(options);
  return response.data.data.meterdata_history.map(row => ({...row, site_id})); // add site_id to each row      
}


// Get meter data for an array of sites
const getMeterDataForSites = async (site_ids, accessToken) => {
  const promises = site_ids.map(site_id => getMeterDataBySite(site_id, accessToken));
  const allData = await Promise.all(promises);

  return allData.flat();
};

// Convert data to CSV format
const convertToCSV = (data) => {
  const fields = ['date', 'data', 'unit_name', 'site_id'];
  const csv = json2csv(data, { fields });

  return csv;
};

// Export CSV data into a new file
const exportToCSV = (csv) => {
  const timestamp = new Date().toISOString();
  const filename = `meter_data_${timestamp}.csv`;
  fs.writeFileSync(filename, csv);

  console.log(`CSV file '${filename}' has been created successfully.`);
};

// Main function to fetch meter data for sites and export to CSV
const run = async () => {
  try {
    const accessToken = await getAccessToken();
    const meterData = await getMeterDataForSites(site_ids, accessToken);
    const csv = convertToCSV(meterData);
    exportToCSV(csv);
  } catch (error) {
    console.error(error);
  }
};

run();

import requests
import json
import csv
from datetime import datetime

# Refresh token
REFRESH_TOKEN = 'YOUR_REFRESH_TOKEN'  # replace with your actual refresh token
CLIENT_ID = 'api-external'

# API URL Endpoint for meters
API_ENDPOINT = 'https://api.cimenviro.com'

# Array of site_ids
site_ids = [3, 148]  # replace with your array of site_ids

# Date Range for meters endpoint
start_date = '2023-01-01'
end_date = '2023-06-01'

# Aggregation period for meters endpoint
aggregate_period = 'P1M'

# OAuth 2.0 endpoint and client details
URL = 'https://login.cimenviro.com/auth/realms/cimenviro/protocol/openid-connect/token'


# Function to get new access token using refresh token
def get_access_token():
    data = {
        'grant_type': 'refresh_token',
        'refresh_token': REFRESH_TOKEN,
        'client_id': CLIENT_ID
    }

    response = requests.post(URL, data=data)
    response_data = json.loads(response.text)
    return response_data['access_token']


# Get meter history for a single site
def get_meter_data_by_site(site_id, access_token):
    url = f"{API_ENDPOINT}/sites/{site_id}/meters"
    params = {
        'utility_type': 'Electricity',
        'reportable': 'true',
        'group_by': 'site',
        'aggregate_period': aggregate_period,
        'start_date': start_date,
        'end_date': end_date
    }
    headers = {
        'accept': 'application/json',
        'Authorization': f"Bearer {access_token}"
    }

    response = requests.get(url, params=params, headers=headers)
    response_data = json.loads(response.text)
    return [{**row, 'site_id': site_id} for row in response_data['data']['meterdata_history']]


# Get meter data for an array of sites
def get_meter_data_for_sites(site_ids, access_token):
    all_data = []
    for site_id in site_ids:
        meter_data = get_meter_data_by_site(site_id, access_token)
        all_data.extend(meter_data)

    return all_data


# Convert data to CSV format
def convert_to_csv(data):
    fields = ['date', 'data', 'unit_name', 'site_id']
    csv_data = [fields] + [[row[field] for field in fields] for row in data]
    return csv_data


# Export CSV data into a new file
def export_to_csv(csv_data):
    filename = f"meter_data_history.csv"
    with open(filename, 'w', newline='') as file:
        writer = csv.writer(file)
        writer.writerows(csv_data)
    print(f"CSV file '{filename}' has been created successfully.")


# Main function to fetch meter data for sites and export to CSV
def run():
    try:
        access_token = get_access_token()
        meter_data = get_meter_data_for_sites(site_ids, access_token)
        csv_data = convert_to_csv(meter_data)
        export_to_csv(csv_data)
    except Exception as e:
        print(f"Error: {str(e)}")


run()

In your terminal execute the script by running node main.js