• State Not Answered
  • Replies 14 replies
  • Subscribers 2 subscribers
  • Views 1134 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...}

  • 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.
Reply
  • 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.
Children
No Data