Export portfolio asset register to CSV

In this guide, we will show you how to extract equipment asset list 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 [email protected].

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 = '<<URL_AUTH>>'

// 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 equipment asset list 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 equipment 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 actions
const EQUIPMENT_ENDPOINT = 'https://api.cimenviro.com/sites/'
const METADATA_ENDPOINT = 'https://api.cimenviro.com/metadata_types'

// Array of site_ids
const site_ids = [195]; // replace with your array of site_ids


// 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 equipment list for a single site
let getEquipmentDataBySite = async (site_id, accessToken) => {
  const options = {
    method: 'GET',
    url: `${EQUIPMENT_ENDPOINT}${site_id}/equipment`,
    params: {
    },
    headers: {
      accept: 'application/json',
      Authorization: `Bearer ${accessToken}`
    }
  };

  const response = await axios.request(options);
  return response.data.data.equipment.map(row => ({...row, 'site_id':site_id}))     
}


// Get equipment list for an array of sites
const getEquipmentDataForSites = async (site_ids, accessToken) => {
  const promises = site_ids.map(site_id => getEquipmentDataBySite(site_id, accessToken));
  const allData = await Promise.all(promises);

  return allData.flat();
};


// Get metadata list 
let getMetadata = async (accessToken) => {
  const options = {
    method: 'GET',
    url: METADATA_ENDPOINT,
    params: {
    },
    headers: {
      accept: 'application/json',
      Authorization: `Bearer ${accessToken}`
    }
  };

  const response = await axios.request(options);
  return response.data.data.metadata_types.map(row => ({...row}));  };


// Convert data to CSV format
const convertToCSV = (data) => {
  const fields = ['site_id','name','level','zone','equipment_type','vendor','model','role','capacity','capacity_unit','energyconsumption','energyconsumption_unit','target_running_load'];
  const csv = json2csv(data, { fields });

  return csv;
};

// Export CSV data into a new file
const exportToCSV = (csv) => {
  const filename = `equipment_data.csv`;
  fs.writeFileSync(filename, csv);

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

// Map equipment code to equipment type
const mapType = (equipmentData, metadata) => {
  const typeMapping = {};

metadata.forEach((item) => {
    typeMapping[item.type_id.toString()] = item.type;
  });

const result = equipmentData.map((item) => {
          const updatedType  = typeMapping[item.metadata_type_id.toString()] || '';
      return { ...item, 'equipment_type': updatedType }; 
 
  });
  
  return result;
};

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

run();

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