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

    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.

Reply
  • 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.

Children
No Data