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

    I've changed the loop to this and used a Hash tables or Dictionaries. But now I only have the timestamps as a result, because I use $output.Timestamp, but I've no clue to add the values of the PIPoints here without getting these kind of values in the CSV; "System.Object[]" and "OSIsoft.AF.Asset.AFValues"

     

    $resultsMap = @{}

try
{
 [Collections.Generic.IEnumerable[OSIsoft.AF.Asset.AFValues]] $listResults = $piPointList.InterpolatedValues($timeRange, $timeSpan, "",$true, $config) 
 
 $i=0
 foreach ($pointResult in $listResults) {
 if ($i -eq 0) {
 $resultsMap.add("Timestamp", $pointresult.Timestamp)
 $i++
 }
 else {
 $resultsMap.add($i, $pointresult)
 $i++
 }
 }

 
}

catch 
{
 Write-Host $_
}
 

$output = @()
$output += New-Object PSObject -Property $resultsMap
$output.Timestamp | Select-object localtime | Export-Csv -Delimiter ";" -Path $myDestinationForData -NoTypeInformation

  • 0 in reply to RalphKouwenhoven

    Hi Ralph, from the code you have shared it seems as though you are not accessing the value for this PI Point. To do so you would need to call $pointresult.Value which contains the actual value for this point. You may find this link of use for this issue. Moreover, you might want to consider using a dictionary with a key being the PI Point such as:

     

    Dictionary<PIPoint, AFValues> resultsMap = new Dictionary<PIPoint, AFValues>();
IEnumerable<AFValues> listResults = piPoints.RecordedValues(timeRange, AFBoundaryType.Inside, null, false, config);
EventsRead = 0;
foreach (AFValues pointResults in listResults)
{ 
resultsMap[pointResults.PIPoint] = pointResults; 
EventsRead += pointResults.Count();
}

    This being said, you may also want to have a look at the following link for further insight on how to use listdatamethods.

Reply
  • 0 in reply to RalphKouwenhoven

    Hi Ralph, from the code you have shared it seems as though you are not accessing the value for this PI Point. To do so you would need to call $pointresult.Value which contains the actual value for this point. You may find this link of use for this issue. Moreover, you might want to consider using a dictionary with a key being the PI Point such as:

     

    Dictionary<PIPoint, AFValues> resultsMap = new Dictionary<PIPoint, AFValues>();
IEnumerable<AFValues> listResults = piPoints.RecordedValues(timeRange, AFBoundaryType.Inside, null, false, config);
EventsRead = 0;
foreach (AFValues pointResults in listResults)
{ 
resultsMap[pointResults.PIPoint] = pointResults; 
EventsRead += pointResults.Count();
}

    This being said, you may also want to have a look at the following link for further insight on how to use listdatamethods.

Children
No Data