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

    Great! This script works perfect. I couldn't do this myself. Many, many thanks!

  • 0 in reply to RalphKouwenhoven

    Good to know, Ralph.  It works perfectly as long getting the PIPointList and InterpolatedValues is also perfect.  That is that the ordering of the CSV columns should match the same ordering as the $namesArray.  This ordering only works as long as (1) each tag name finds a valid PIPoint in the data archive, and (2) there are no duplicate tag names.  One would certainly hope this is always the case, which results in a PIPointList that is 1-to-1 and in the same desired order as $namesArray.  If this is not the case, then a little extra code would be needed.  I don't think you need this, but I want to include it for completeness and to close out this post.

     

     # The PIPointList should be in the desired order as long as 
 # (1) each name in $namesArray references a valid PIPoint, and
 # (2) each PIPoint is referenced exactly once, i.e. there were no duplicate names.
 [Int32[]]$orderedIndexes = New-Object System.Int32[] -ArgumentList $namesArray.Length
 for ($i = 0; $i -lt $namesArray.Length; $i++) {
 $index = -1
 for ($j = 0; $j -lt $tagCount; $j++) {
 if ($namesArray[$i] -eq $listResults[$j][0].PIPoint.Name) {
 $index = $j
 continue
 }
 }
 $orderedIndexes[$i] = $index
 } 

 [void]$stopWatch.Restart()

 # Header row
 [void]$builder.Append("Timestamp")
 foreach ($i in $orderedIndexes) {
 if ($i -ne -1) {
 $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"))
 foreach ($i in $orderedIndexes) {
 if ($i -ne -1) {
 $piValue = $listResults[$i][$eventIndex].Value.ToString()
 [void]$builder.Append($separator + $piValue)
 }
 } 
 [void]$builder.AppendLine("")
 }

Reply
  • 0 in reply to RalphKouwenhoven

    Good to know, Ralph.  It works perfectly as long getting the PIPointList and InterpolatedValues is also perfect.  That is that the ordering of the CSV columns should match the same ordering as the $namesArray.  This ordering only works as long as (1) each tag name finds a valid PIPoint in the data archive, and (2) there are no duplicate tag names.  One would certainly hope this is always the case, which results in a PIPointList that is 1-to-1 and in the same desired order as $namesArray.  If this is not the case, then a little extra code would be needed.  I don't think you need this, but I want to include it for completeness and to close out this post.

     

     # The PIPointList should be in the desired order as long as 
 # (1) each name in $namesArray references a valid PIPoint, and
 # (2) each PIPoint is referenced exactly once, i.e. there were no duplicate names.
 [Int32[]]$orderedIndexes = New-Object System.Int32[] -ArgumentList $namesArray.Length
 for ($i = 0; $i -lt $namesArray.Length; $i++) {
 $index = -1
 for ($j = 0; $j -lt $tagCount; $j++) {
 if ($namesArray[$i] -eq $listResults[$j][0].PIPoint.Name) {
 $index = $j
 continue
 }
 }
 $orderedIndexes[$i] = $index
 } 

 [void]$stopWatch.Restart()

 # Header row
 [void]$builder.Append("Timestamp")
 foreach ($i in $orderedIndexes) {
 if ($i -ne -1) {
 $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"))
 foreach ($i in $orderedIndexes) {
 if ($i -ne -1) {
 $piValue = $listResults[$i][$eventIndex].Value.ToString()
 [void]$builder.Append($separator + $piValue)
 }
 } 
 [void]$builder.AppendLine("")
 }

Children
No Data