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)"
