• State Not Answered
  • Replies 14 replies
  • Subscribers 2 subscribers
  • Views 1136 views
  • Users 0 members are here
Options
Related

piPointList.InterpolatedValues Powershell export to csv, per column the values of the PIPoint

RalphKouwenhoven

We are trying to create a Powershell script to export data from PI into a csv with per column the values per PIPoint, so like below. We've made below script, but this is very slow. Does someone has a better idea? We think this should be easier to make. Thanks in advane.

 

 

TimestampTag 1Tag 21-10-2020 0:004,17.11-10-2020 0:054.56.21-10-2020 0:103.15.11-10-2020 0:156.54.7........

 

 

#Load the AF SDK assembly through reflection

[Reflection.Assembly]::LoadWithPartialName("OSIsoft.AFSDK") | Out-Null

# Connect to the default PI Data Archive
[OSIsoft.AF.PI.PIServers] $piSrvs = New-Object OSIsoft.AF.PI.PIServers
[OSIsoft.AF.PI.PIServer] $piSrv = $piSrvs.DefaultPIServer

# Define array of PI Point Names
# $piPointNames = @()
# $piPointNames = $piPointNames + "Tag 1" + "Tag 2"

# Define the time range for data retrieval
$pistarttime = "1-9-2020 0:00"
$piendtime = "1-10-2020 0:00"

# Define the time range for data retrieval
$tsYears = 0
$tsMonths = 0
$tsDays = 0
$tsHours = 0
$tsMinutes = 5
$tsSeconds = 0
$tsMillisends = 0

[OSIsoft.AF.Time.AFTimeRange] $timeRange = New-Object OSIsoft.AF.Time.AFTimeRange($pistarttime, $piendtime) 
[OSIsoft.AF.Time.AFTimeSpan] $timeSpan = New-Object OSIsoft.AF.Time.AFTimeSpan($tsYears, $tsMonths, $tsDays, $tsHours, $tsMinutes, $tsSeconds, $tsMillisends)

$OutputFolder = "C:\"
$Timestamp=get-date -uFormat "%m%d%Y%H%M%s" 
$NewFileName = "Export"+ $Timestamp +".csv" 
$myDestinationForData = "$($OutputFolder)$($NewFileName)"


[string[]]$namesArray = "Tag 1", "Tag 2"
[Collections.Generic.List[String]]$names = $namesArray 

[System.Type[]]$types = @('OSIsoft.AF.PI.PIServer', 'System.Collections.Generic.IEnumerable[String]','System.Collections.Generic.IEnumerable[String]') 
$m = [OSIsoft.AF.PI.PIPoint].GetMethod('FindPIPoints', $types) 
$params = @([OSIsoft.AF.PI.PIServer]$piSrv, [Collections.Generic.List[String]]$namesArray, $null) 
[System.Collections.Generic.IList[OSIsoft.AF.PI.PIPoint]]$tagList = $m.Invoke($null, $params)

[OSIsoft.AF.PI.PIPointList]$piPointList = New-Object OSIsoft.AF.PI.PIPointList 
$piPointList.AddRange($tagList);

[OSIsoft.AF.PI.PIPagingConfiguration] $config = New-Object OSIsoft.AF.PI.PIPagingConfiguration([OSIsoft.AF.PI.PIPageType]::TagCount, 100)
$resultsMap = @()

try
{
$start_time = Get-Date
[Collections.Generic.IEnumerable[OSIsoft.AF.Asset.AFValues]] $listResults = $piPointList.InterpolatedValues($timeRange, $timeSpan, "",$true, $config) 
Write-Output "Pi points - Time taken: $((Get-Date).Subtract($start_time).Seconds) second(s)"
$start_time = Get-Date
$listResultsarr = $listResults.toArray()
Write-Output "To Array - Time taken: $((Get-Date).Subtract($start_time).Seconds) second(s)"
$start_time = Get-Date
$overall_counter = 0
$loop_size = 0
$columncounter = 0
$loop_counter = 0

foreach ($result in $listResultsarr) {
if ($overall_counter -gt 0 -and (!($resultsMap | Get-Member | where { $_.membertype -eq "Noteproperty"} ).name.contains($result.PIPoint.Name))) {
$resultsMap | Add-Member -MemberType NoteProperty -Name "$($result.PIPoint.Name)" -Value $null
"Added column for $($result.PIPoint.Name)"
$columncounter++
$loop_counter = 0
if ($columncounter -eq 1) {
$loop_size = $overall_counter
} 
}
if ($columncounter -eq 0) {
$resultsMap += [PSCustomObject]@{"Timestamp"="$($result.Timestamp.LocalTime)";"$($result.PIPoint.Name)"="$($result.Value)";}
}
else {
$row = $loop_counter
$resultsMap[$row]."$($result.PIPoint.Name)"="$($result.Value)"
}
Write-Progress -Activity "Creating object with PI Values...." -Status "$([math]::Round(($overall_counter / $listResultsarr.count) * 100))% Complete:" -PercentComplete (($overall_counter / $listResultsarr.count) * 100)
$loop_counter++
$overall_counter++
}
Write-Output "Processed $($overall_counter) values in $((Get-Date).Subtract($start_time).Seconds) second(s)"
}

catch 
{
Write-Host $_
}

$start_time = Get-Date
$resultsMap | Export-Csv -Delimiter "," -Path $myDestinationForData -NoTypeInformation
Write-Output "Create CSV - Time taken: $((Get-Date).Subtract($start_time).Seconds) second(s)"

Parents
  • 0

    Some more information to maybe make it more clear. If I run below, as mentioned in this link .

     

    $output.GetEnumerator()

     

    I get this result in powershell;

     

    13 : {27,83895, 27,47229, 27,27062, 27,65563...}
12 : {0,0183332, 0,0183332, 0,0183332, 0,0183332...}
11 : {-0,0144444, -0,0144444, -0,0144444, -0,0144444...}
10 : {7,305275, 7,493052, 7,381108, 7,568886...}
9 : {0, 0, 0, 0...}
8 : {2,899999, 2,684999, 3,155994, 2,988257...}
7 : {55,44197, 55,93599, 55,41597, 55,38997...}
6 : {1015,44, 1015,44, 1015,44, 1015,44...}
5 : {43,46998, 43,66999, 43,98998, 44,18999...}
4 : {18,04999, 18,02999, 18,02999, 17,92997...}
3 : {-25,75318, -25,75318, -25,75318, -25,75318...}
2 : {-0,1025543, -0,1025543, -0,1025543, -0,1025543...}
1 : {99,97687, 99,97697, 99,97709, 99,9772...}
Timestamp : {1-6-2020 00:00:00, 1-6-2020 00:05:00, 1-6-2020 00:10:00, 1-6-2020 00:15:00...}

Reply
  • 0

    Some more information to maybe make it more clear. If I run below, as mentioned in this link .

     

    $output.GetEnumerator()

     

    I get this result in powershell;

     

    13 : {27,83895, 27,47229, 27,27062, 27,65563...}
12 : {0,0183332, 0,0183332, 0,0183332, 0,0183332...}
11 : {-0,0144444, -0,0144444, -0,0144444, -0,0144444...}
10 : {7,305275, 7,493052, 7,381108, 7,568886...}
9 : {0, 0, 0, 0...}
8 : {2,899999, 2,684999, 3,155994, 2,988257...}
7 : {55,44197, 55,93599, 55,41597, 55,38997...}
6 : {1015,44, 1015,44, 1015,44, 1015,44...}
5 : {43,46998, 43,66999, 43,98998, 44,18999...}
4 : {18,04999, 18,02999, 18,02999, 17,92997...}
3 : {-25,75318, -25,75318, -25,75318, -25,75318...}
2 : {-0,1025543, -0,1025543, -0,1025543, -0,1025543...}
1 : {99,97687, 99,97697, 99,97709, 99,9772...}
Timestamp : {1-6-2020 00:00:00, 1-6-2020 00:05:00, 1-6-2020 00:10:00, 1-6-2020 00:15:00...}

Children
  • 0 in reply to RalphKouwenhoven

    The GetEnumerator() is one way to do it.  An alternative is to use PowerShell's for ForEach loop, which calls the enumerator.

     

    Again, if order is not that critical, the simplest way to do it is to transform the output from InterpolatedValues from an IEnumerable[AFValues] to either a List[AFValues] or an AFValues[] array, i.e use a simple ToList or ToArray.  Each index of that list/array refers to one PIPoint, which you can get from the 0 index of the respective AFValues.

  • 0 in reply to RalphKouwenhoven

    You can play around with how you want the timestamp to appear, or later work on an order, but here's one way to jump dump everything to a file:

     

    x

    #Load the AF SDK assembly through reflection

[Reflection.Assembly]::LoadWithPartialName("OSIsoft.AFSDK") | Out-Null

# Connect to the default PI Data Archive
[OSIsoft.AF.PI.PIServers] $piSrvs = New-Object OSIsoft.AF.PI.PIServers
[OSIsoft.AF.PI.PIServer] $piSrv = $piSrvs.DefaultPIServer

# Define the time range for data retrieval
$startTimeString = "2020-10-13T00:00:00"
$endTimeString = "2020-10-14T00:00:00"
[OSIsoft.AF.Time.AFTime]$pistarttime = [OSIsoft.AF.Time.AFTime]::Parse($startTimeString)
[OSIsoft.AF.Time.AFTime]$piendtime = [OSIsoft.AF.Time.AFTime]::Parse($endTimeString)

# Define the time range and span interval for data retrieval
[OSIsoft.AF.Time.AFTimeRange] $timeRange = New-Object OSIsoft.AF.Time.AFTimeRange($pistarttime, $piendtime) 
[OSIsoft.AF.Time.AFTimeSpan] $timeSpan = New-Object OSIsoft.AF.Time.AFTimeSpan([TimeSpan]::FromMinutes(5))

[System.Diagnostics.Stopwatch]$stopWatch = New-Object System.Diagnostics.Stopwatch

$OutputFolder = "C:\Temp\"
$Timestamp=get-date -uFormat "%m%d%Y%H%M%s" 
$NewFileName = "Export"+ $Timestamp +".csv" 
$myDestinationForData = "$($OutputFolder)$($NewFileName)"

[string[]]$namesArray = "SINUSOID", "CDT158"

[void]$stopWatch.Restart()
[OSIsoft.AF.PI.PIPointList]$piPointList = New-Object OSIsoft.AF.PI.PIPointList 
$piPointList.AddRange( [OSIsoft.AF.PI.PIPoint]::FindPIPoints($piSrv, $namesArray, $null) );
[void]$stopWatch.Stop()
Write-Output "FindPIPoints took: $($stopWatch.Elapsed.TotalSeconds) second(s)"

[OSIsoft.AF.PI.PIPagingConfiguration] $config = New-Object OSIsoft.AF.PI.PIPagingConfiguration([OSIsoft.AF.PI.PIPageType]::TagCount, 100)

[String]$separator = ";" # could be a Comma, Semi-Colon, or Tab among other things
[System.Text.StringBuilder]$builder = New-Object System.Text.StringBuilder

try
{
 [void]$stopWatch.Restart()
 [OSIsoft.AF.Asset.AFValues[]] $listResults = ($piPointList.InterpolatedValues($timeRange, $timeSpan, "",$true, $config) ) 
 [void]$stopWatch.Stop()
 Write-Output "PI InterpolatedValues took: $($stopWatch.Elapsed.TotalSeconds) second(s)"

 $tagCount = $listResults.Length
 $eventCount = $listResults[0].Count # i.e. count of returned timestamps

 [void]$stopWatch.Restart()

 # Header row
 [void]$builder.Append("Timestamp")
 for ($i = 0; $i -lt $tagCount; $i++)
 {
 $tagName = $listResults[$i][0].PIPoint.Name
 [void]$builder.Append($separator + $tagName)
 }
 [void]$builder.AppendLine("")

 # Detail rows
 for ($eventIndex = 0; $eventIndex -lt $eventCount; $eventIndex++)
 {
 [void]$builder.Append($listResults[0][$eventIndex].Timestamp.LocalTime.ToString("yyyy-MM-ddTHH:mmK"))
 for ($i = 0; $i -lt $tagCount; $i++)
 {
 $piValue = $listResults[$i][$eventIndex].Value.ToString()
 [void]$builder.Append($separator + $piValue)
 } 
 [void]$builder.AppendLine("")
 }

 [void]$stopWatch.Stop()
 Write-Output "StringBuilder took: $($stopWatch.Elapsed.TotalSeconds) second(s)"
}
catch 
{
 Write-Host $_
}

[void]$stopWatch.Start()
[String]$builder | Out-File -FilePath $myDestinationForData 
[void]$stopWatch.Stop()

Write-Output "Create Out File took: $($stopWatch.Elapsed.TotalSeconds) second(s)"

     

    In the name of variety, I am doing a lot of things differently.

     

    • I use a Stopwatch object for timing calls.
    • My time strings follow an ISO-8601 format, and are first parsed into AFTime objects before moving onto the AFTimeRange.
    • I set the AFTimeSpan quickly with a TimeSpan.FromMinutes(5) call.
    • I combined the FindPIPoints and assign to a PIPointList into one call.
    • I combined the InterpolatedValues call with transforming it to an array in one call.
    • The $separator variable allows me to change the column delimiter from a comma to perhaps a tab or a semi-colon.
    • I do not output as a CSV now, but instead just perform a flat out file dump.