Extract, Transform and Load; A PowerShell Story

A regional manager from a global call center approached me through Fiverr to request an assignment. The assignment was to determine the typical duration of a shift handover to the next shift. The company operates on a follow-the-sun model, which prompted senior management's interest in handover efficiency.

There has been some disagreement as to the scope of a handover. Is it limited to transferring the current state, or does it include following up on pending items? If a new ticket is involved, a knowledge transfer and question-and-answer session can last 10 to 15 minutes. Even without new cases, a handover can take longer if there has been a significant amount of activity on the tickets. Another factor that can extend a transfer beyond expectations is when a shift worker is starting their first day, and thus has to start on a group of unfamiliar cases.

I find problems involving timespans intriguing, and although the financial compensation for the job was limited, I accepted it solely for the experience and enjoyment it promised. The initial challenge was to determine the handover durations. According to the job poster, a shift summary report is sent and stored in a specific folder at the end of each handover. This information provided a starting point as the email includes a sent-on property and title.

Add-Type -assembly "Microsoft.office. Interop.outlook"
$outlook = New-Object -comobject outlook.Application
$namespace = $Outlook.GetNamespace("MAPI")
$recipient = "SharedMailbox@company.com"
$recipientAddressEntry = $namespace.CreateRecipient($recipient)
$recipientAddressEntry.Resolve()
$HOFolder = SsharedMailbox. Folders. Item("Shift Handover Reports")
$sharedMailbox = $namespace.GetsharedDefaultFolder($recipientAddressEntry, [Microsoft.office.Interop.outlook.01DefaultFolders]::olFolderInbox)
$HOFolder = $sharedMailbox.Folders.Item("Shift Handover Report")
$messages = $HOFolder.Items

$messageDetails = @()

foreach ($message in $messages) {
    $sendDateOne = $message.SentOn.ToString("MM-dd-yyyy HH:mm:ss")
    $sentence = $message.Subject
    $title = ''
    if ($sentence -match "India" -and $sentence -match "USA") {
        $title = 'USA To India'
    }
    elseif ($sentence -match "Dublin" -and $sentence -match "USA") {
        $title = 'Dublin To USA'
    }
    elseif ($sentence -match "India" -and $sentence -match "Dublin") {
        $title = 'India To Dublin'
    }
    else {
        $title = 'Invalid title'
    }
    §messageDetail = [PSCustomObject] @{
        "messageSubject" = $title
        "sentDate"       = $sendDateOne
    }
    $messageDetails += $messageDetail
}
$messageDetails | Export-Csv "C:\Users\userName\dev\tools\holoutlook.csv" -NoTypeInformation

Not bad, right? I also checked for invalid titles because by examining the data I learned that the handover folder contains unrelated emails. By checking for two given keywords, I can determine which shift is handing over to which. In general, Dublin hands over to the US, the US to India, and India to Dublin. The extracted data is stored in CSV and this concludes our data extraction. In the next step, I would like to message the data further to calculate the durations. One quick issue that I noticed was that the entries are not sorted by their date, here is a quick fix for that:

$messageDetails | Sort-Object { [DateTime]::ParseExact($_.sentDate,
'MM-dd-yyyy HH:mm:ss', $null) }

now, let's work on calculating the durations:

# Set the path to the CSV file
$csvPath = "C:\Users\userName\dev\tools\ho\fileName.csv"

# Import the CSV data
$messageDetails = Import-Csv -Path $csvPath

# Initialize the output array
$newMessageDetails = @()

# Loop through each message
foreach ($message in $messageDetails) {
    # Convert the message subject to lowercase
    $subject = $message.messageSubject.ToLower()

    # Calculate the duration and start time based on the message subject
    switch ($subject) {
        "us to india" {
            $endTime = [DateTime]::ParseExact($message.sentDate, 'MM-dd-vyyy HH:mm:ss', $null)
            $startTime = $endTime.Date.AddHours(22)
            $duration = (New-TimeSpan $startTime $endTime).Minutes
        }
        "india to dublin" {
            $endTime = [DateTime]::ParseExact($message.sentDate, 'MM-dd-yyyy HH:mm:ss', $null)
            $startTime = $endTime.Date.AddHours(4).AddMinutes(45)
            $duration = (New-TimeSpan $startTime $endTime).Minutes
        }
        "dublin to us" {
            $endTime = [DateTime]::ParseExact($message.sentDate, 'MM-dd-yyyy HH:mm:ss', $null)
            $startTime = $endTime.Date.AddHours(12).AddMinutes(30)
            $duration = (New-TimeSpan $startTime $endTime).Minutes
        }
        default {
            Write-Warning "Invalid message subject: $($message.messageSubject)"
            continue # skip to the next message
        }
    }

    # Convert the message title to title case
    $newTitle = (Get-Culture).TextInfo.ToTitleCase($message.Title)

    # Create a new object with the updated data
    $newMessageDetail = [PSCustomObject]@{
        Title     = $newTitle
        Date      = $endTime.ToString("MM-dd-yyyy")
        EndDate   = $message.sentDate
        StartTime = $startTime
        Duration  = $duration
    }

    # Add the new object to the output array
    $newMessageDetails += $newMessageDetail
}

# Export the updated data to the same CSV file
$newMessageDetails | Export-Csv -Path $csvPath -NoTypeInformation

We have the end time which is the sent date of the email, next depending on which shift is doing the handover, we have to calculate the start time and then the duration. The starting time for all 3 handovers was supplied by the job poster. Now I have an object that contains the title(shift HO name), date, start time, end time, and duration. Now we can shift gears and start working on our data loading.

"devDependencies": {
"buffer": "^5.7.1",
"parcel": "^2.6.2"
},
"dependencies": {
"@cubejs-client/core": "^0.31.0"
"axios": "^1.4.0",
"chart. js": "^4.3.0"
"chartjs-adapter-date-fns": "^3.0.0",
"cors": "^2.8.5",
"csv-parser": "^3.0.0"
"date-fns": "^2.30.0",
"express": "^4.18.2"
}

I am going to set up a simple endpoint for making a get request and for retrieving the data. Once the data is received, I'm going to use chart.js to create a line chart and display the handover duration across the 3 regions. In what comes next, we setup our server, define our get request and read the CSV file as response:

const fs = require ("fs");
const csvParser = require ("csv-parser");
const express = require("express");
const exp = require("constants");
const cors = require("cors");
const app = express () ;
app.use (cors ()) ;
const port = 3000;

const readCSV = async (filePath) => {
  return new Promise((resolve, reject) => {
    const data = [];
    fs.createReadStream(filePath)
      .pipe(csvParser())
      .on("data", (row) => {
        data.push(row);
      })
      .on("end", () => {
        resolve(data);
      })
      .on("error", (error) => {
        reject(error);
      });
  });
};

app.get("/data", async (req, res) => {
  try {
    const filePath = "book.csv";
    const data = await readCSV(filePath);
    res.json(data);
  } catch (error) {
    console.error(`Error reaping the cv file ${error}`);
    res.status(500).send("Error reading the data source");
  }
});
app.listen(port, () => {
    console.log(`App is running on port ${port}`);
});

Now, let's work on our chart.js file, we'll make a get request to our API, set our labels, set our durations and finally graph the lines.

import Chart from "chart.js/auto";
import axios from "axios";
import "chartjs-adapter-date-fns";

const fetchData = async () => {
  try {
    const response = await axios.get("http://localhost:3000/data");
    return response.data;
  } catch (error) {
    console.error("Error fetching data:", error);
    return [];
  }
};

const filterDataByTitle = (data, title) => {
  return data
    .filter((row) => row.Title === title)
    .map((row) => row.Duration);
};

(async function () {
  const data = await fetchData();
  const labels = [...new Set(data.map((row) => row.ShiftDate))];

  const USDurations = filterDataByTitle(data, "US To India");
  const IndiaDurations = filterDataByTitle(data, "India To London");
  const DublinDurations = filterDataByTitle(data, "Dublin To US");

  new Chart(document.getElementById("chart"), {
    type: "line",
    data: {
      labels: labels,
      datasets: [
        {
          label: "US To India",
          data: USDurations,
        },
        {
          label: "Dublin To US",
          data: DublinDurations,
        },
        {
          label: "India To Dublin",
          data: IndiaDurations,
        },
      ],
    },
    options: {
      plugins: {
        title: {
          display: true,
          text: "HO Stats",
        },
      },
    },
  });
})();

In conclusion, I found this assignment to be enjoyable due to the various challenges and intricacies involved. I hope that you share the same sentiment. I welcome your feedback and thoughts, particularly regarding my use of PowerShell for the ETL process. I would appreciate knowing if you think that I may have gone too far in using it.

Did you find this article valuable?

Support Application Support by becoming a sponsor. Any amount is appreciated!