Getting data out of PI to a CSV

Hi Joel,

There are some off-the-shelf PI tools that you can use to do a CSV export of tag data (specifically, the PI Integrator for Business Analytics). We have also built simple, custom applications for our customers to accomplish this in the past. Let me know if you would like to discuss either of these options further. Happy to have a brief call and tell you more.

Regards,

-Vincent

Joel,

Whereas using Excel to automate this process is relatively easy, it's really a bit "Rube Goldberg" like.  There are a number of paths that you can go but in my opinion, the most robust and reliable method is via a custom application.  The AF-SDK makes this operation relatively simple and it wouldn't take a very large application.  You could even use the Windows Task Scheduler to run it as a command line application passing it input and output parameters.  The nice thing about the Task Scheduler is: if the program fails and crashes due to some unusual situation (i.e.: lost connectivity to the PI server), it will just try again at the next scheduled time.  When writing custom software, I've found that typically its 20% of the code to actually get the job done and 80% to handle all of the possible error conditions.  Using the Task Scheduler helps here in that you can live with crashes.  You can even have the code write "state" information so when it runs, it will pick up from where it left off, even if the server that it runs on is shut down for some time.

I hope that this helps,

David

Hi Joel

You can use PI Powershell to get snapshot/Archive data :

Sample Script :

#PI Server 
$piServerName = "Your_PISERVER" 
#Source File Path (Note : Input Tags separated by new line)
$SourceFile = "D:\InputTags.txt" 
#Output folder where result should be stored 
$OutputFolder = "D:\Output\" 
#Output file name
$NewFileName = "SnapshotData_"+ $Timestamp +".csv"


#Connection to PI Server 
$piConnection = Connect-PIDataArchive -PIDataArchiveMachineName $piServerName -AuthenticationMethod Windows
#Get points from InputFile i.e. $SourceFile
$Points = Get-Content $SourceFile 


#If file contains more than one point then enumerate all points and get data.
ForEach($piPoint in $Points){
 
 #Get point attributes like Name, Pointtype etc..
 $pt = Get-PIPoint -Name $piPoint -Connection $piConn -AllAttributes


 #Below line only for debugging to check point Points = Get.
 Write-Host Data Write started for Tag $pt.Point.Name


 $results = Get-PIValue -PIPoint $pt -Time (ConvertFrom-AFRelativeTime -RelativeTime "*") -ArchiveMode Previous | Select TimeStamp, Value


 #Below line only for debugging to check results count i.e number of events fetched within the time range..
 Write-Host $piPoint has @($results).Count events in the time range


 #Check if result is empty i.e no values then skip the piPoint and next the next piPoint..
 if ($results -ne $null){ 


 #Write to file
 $myDestinationForData = "$($OutputFolder)$($NewFileName)"
 $fs = New-Object System.IO.FileStream $myDestinationForData ,'Append','Write','Read' 
 $myStreamWriter = New-Object System.IO.StreamWriter($fs) 


 #Check if the point type is digital. If Yes then get digital set else it will only give state id. 
 if ($pt.Attributes.pointtype -eq "Digital"){
 $digStateSet= Get-PIDigitalStateSet -Name $pt.Attributes.digitalset -Connection $piConnection
 #Write the result to file.
 ForEach ($result in $results){ 
 
 $rtimeStamp = $result.TimeStamp 
 $rvalue = $digStateSet[$result.Value.State] 
 $myStreamWriter.WriteLine("$($pt.Point.Name),$rTimeStamp,$rvalue") 
 }
 
 } 
 #Check if the point type is not digital then write value to file.
 else {
 ForEach ($result in $results){ 
 $rtimeStamp = $result.TimeStamp 
 $rvalue = $result.Value 
 $myStreamWriter.WriteLine("$($pt.Point.Name),$rTimeStamp,$rvalue") 
 }
 }
 }
 #Below line only for debugging to check point name and completion for single point.
 Write-Host Data Write Completed for Tag $pt.Point.Name
 $myStreamWriter.Close() 
 
 }
 

If you are looking for compressed data then add two variables start , end time and change line 30

$Starttime = (get-date 8:00:00).AddDays(-200)

$Endtime=(get-date 9:00:00)

$results = Get-PIValue -PIPoint $pt -StartTime $Starttime -EndTime $Endtime | Select TimeStamp, Value

Input file :

Snapshot Output :

If your requirement is to generate the .csv file every day then you can specify start and end time.

Looks good!  I'm always happy to borrow/get example PowerShell scripts!

Small correction $piConn on line 21 should be piConnection.  I also had to setup another variable for $TimeStamp that you used for the destination file name suffix - I used (Get-Date -Format "yyyy-MM-dd hh-mm-ss").

Actually really simple, if you have a c# programmer. Would write a simple window service, accessing PI through AFSDK Data Pipes. Data Pipes can access either snapshot or archive data. Would have one thread retrieving data as soon as it comes in (snapshot) or as soon it is archived. Then place data on a thread safe queue. Have another thread continuously reading data from queue and writing to CSV file. Should not take more than a day to write and have up and running. Once running will work for as long as needed.

Just make sure your window service is running under a windows service account and has access to read from PI Archive PIPOINTS.

This worked perfect with Marcelino's suggested edits. There must be some limit to how much data it can pull, for 1700 tags grabbing all data points in the last 5 minutes it output roughly 50k lines in a CSV but cut off alot of the data. I broke it into 2 separate scripts with half the amount of points in each and have it running under Windows Scheduler. The application wants flat files, so that's what I'm trying to get done.

Now, to figure out how to give them 2 years of data across those 1700 tags....

Python script also can pull multiple tags with specified time range and intervals into a CSV file.