Use PowerShell to Push Data to a Swagger API

We went over how to get authenticated using PowerShell and OAuth 2.0 in a previous article. It might be required to do that first!

In this scenario we are loading up SCADA data from CSV files on disk and then pushing the data to the remote API service. Here is the full code:

# Load the CSV Files 
$rawData = import-csv "data.csv" 

# Setup all the info for the subsequent API requests
$url = "http://api_url_here/api"

# Build up the header to include the custom added Authorization field and Bearer token. 
# $token and $tokenBearer comes from previous code in other article
$headers = @{
    accept = "*/*"
    Authorization = "$($tokenBearer) $($token)"
    }


foreach ($record in $rawData){
    
    "Processing record $($record.RecordId), $($record.EquipmentId)"

    # convert the RecordTime from the export to datetime so it can be converted to unix timestamp below
    $RecordTime = [datetime] $record.RecordTime

    # Build the hashtable of record info
    $JSON = @{
            recordId = $record.RecordId
            timestamp = (New-TimeSpan -Start (Get-Date "01/01/1970") -End $RecordTime).TotalSeconds
            accumulatedRuntime = [int] ($record.AccumulatedRunTime)
            voltage = [int] ($record.Voltage)
            totalCount = [int] ($record.TotalCount)
    }

    # Convert hashtable to JSON (DEBUG ONLY)
    #ConvertTo-Json $JSON | Out-File "$($record.RecordId).json"

    try {
        
        "Sending the API request for pump: $($record.RecordId)"
        # Send the API request
        $response = Invoke-WebRequest -Headers $headers -Uri $url -Method Post -ContentType "application/json" -Body (ConvertTo-Json $JSON)

        "  API data sent. Status code: $($response.StatusCode)"
    }

    catch {
            
        "Uh oh. Something went wrong. Could not send the API request."
    }
} 

The first bit sets us up for success later. Import the CSV file, set the URL we are publishing too, configure the headers for Bearer authentication using the token obtained from authentication.

# Load the CSV Files 
$rawData = import-csv "data.csv" 

# Setup all the info for the subsequent API requests
$url = "http://api_url_here/api"

$headers = @{
    accept = "*/*"
    Authorization = "$($tokenBearer) $($token)"
    }

Process each record and turn the data into a hash table that can be converted to JSON. In this case the record time needed to be a Unix timestamp.

foreach ($record in $rawData){
    
    "Processing record $($record.RecordId), $($record.EquipmentId)"

    # convert the RecordTime from the export to datetime so it can be converted to unix timestamp below
    $RecordTime = [datetime] $record.RecordTime

    # Build the hashtable of record info
    $JSON = @{
            recordId = $record.RecordId
            timestamp = (New-TimeSpan -Start (Get-Date "01/01/1970") -End $RecordTime).TotalSeconds
            accumulatedRuntime = [int] ($record.AccumulatedRunTime)
            voltage = [int] ($record.Voltage)
            totalCount = [int] ($record.TotalCount)
    }

Include a couple lines that write the JSON out to a file for debug (uncomment the second line).

    # Convert hashtable to JSON (DEBUG ONLY)
    #ConvertTo-Json $JSON | Out-File "$($record.RecordId).json"

Try the request and close the ForEach loop. Note how we included the headers from earlier on and the Hashtable converted to JSON.

We went with Invoke-WebRequest for this in order to get the status code back, but Invoke-RestMethod would work as well.

    try {
        
        "Sending the API request for pump: $($record.RecordId)"
        # Send the API request
        $response = Invoke-WebRequest -Headers $headers -Uri $url -Method Post -ContentType "application/json" -Body (ConvertTo-Json $JSON)

        "  API data sent. Status code: $($response.StatusCode)"
    }

    catch {
            
        "Uh oh. Something went wrong. Could not send the API request."
    }
}