14 Replies Latest reply on Jan 25, 2019 5:22 PM by Rick Davin

    PI Powershell: Extract data from multiple PI tags into csv with Tag names.

    Kata

      I'm building a report with Powershell for multiple PI tags so I need the Tag names in my csv file as well. I'm unable to capture the tag names properly as they cannot be selected as attributes.

      Could somebody help. I'm stuck. It would be a much easier solution than using PI-SDK or AF-SDK.

       

      Thanks is advance.

      K.

        • Re: PI Powershell: Extract data from multiple PI tags into csv with Tag names.
          tramachandran

          You should be able to implement this in either PI PowerShell or PIConfig without the need for the SDKs.

           

          Example Script:

          $piServerName = "PIDATrainingSRV"
          $sourceFile = "C:\Users\LocalAdmin\AppData\Local\Temp\2\source_tags.txt"              #~ Directory and file name for the list of tags
          $destinationFolder = "C:\Users\LocalAdmin\AppData\Local\Temp\2\"                      #~ Directory where the csv files will be written
          $dataStartTime = Get-Date("21-Jan-2019 00:00:00")                  #~ Specify data extration start time
          $dataEndTime = Get-Date("22-Jan-2019 00:00:00")                    #~ Specify data extration end time
          $fileName = "output_events"
          $suffix = ".csv"
          
          #~ Connect to the target PI Server:
          $piConn = Connect-PIDataArchive -PIDataArchiveMachineName $piServerName
          
          #~ Get your pi points one by one and write the csv:
          $piPoints = Get-Content $sourceFile
          
          ForEach($piPoint in $piPoints)
          {  
              $pt = Get-PIPoint -Name $piPoint -Connection $piConn # check for NULL if necessary
              Select-Object -Property TimeStamp, Value  
          
              $results = Get-PIValue -PIPoint $pt -StartTime $dataStartTime -EndTime $dataEndTime | Select TimeStamp, Value 
          
              $myDestinationForData = "$($destinationFolder)$($fileName)$($suffix)" # change filename for each point if necessary
              $fs = New-Object System.IO.FileStream $myDestinationForData ,'Append','Write','Read'
              $myStreamWriter =  New-Object System.IO.StreamWriter($fs)
          
              ForEach ($result in $results)
              {
                  $comma = ","
                  $rtimeStamp = $result.TimeStamp
                  $rvalue = $result.Value
                  $myStreamWriter.WriteLine("$($pt.Point.Name),$rTimeStamp,$rvalue")
              }
              $myStreamWriter.Close()
          }
          

           

           

          Additional References

          PowerShell

          OSIsoft.PowerShell Script To Output A Bunch Of CSV Files For A List Of PI Points

          Is there a process available that I can use to delete specific archive records?

          2 of 2 people found this helpful
            • Re: PI Powershell: Extract data from multiple PI tags into csv with Tag names.
              Kata

              Hi Thyagarajan,

               

              I have tried with a few different methods, but sadly yours doesn't work for me either. It will only put the data for the last tag on the list into the output file.

               

              I would need time and value for the previous day for 20 tags together with the Tag name in the same csv file. It would be easy to have 20 different files as an output, however my client only wants one file as it needs to be sent to a 3rd party via e-mail and he doesn't want to send 20 files daily.

               

              As I mentioned I didn't want to go into programming as PowerShell seemed easier, but the  problem here is that the TagName is not a selectable attribute in PI PowerShell.

               

              Thanks for your input anyway.

               

              K

                • Re: PI Powershell: Extract data from multiple PI tags into csv with Tag names.
                  tramachandran

                  Hi Katalin, the output should all be appended to a single csv file in the format

                  <Tagname>,<Timestamp>,<Value>.

                  Are you looking for a different output format?

                   

                  If you look at the example both sinusoid & cdt158 are in the same file.

                  Could you copy-paste the script again and try it out after changing the appropriate parameters.

                    • Re: PI Powershell: Extract data from multiple PI tags into csv with Tag names.
                      tramachandran

                      May be the #comments for changing filename and checking for NULL is causing the confusion?

                        • Re: PI Powershell: Extract data from multiple PI tags into csv with Tag names.
                          Kata

                          Hi Thyagarajan,

                           

                          No. I'm not checking for null and using a timestamp for the new filename.

                          Like this:

                          $PIServerHost = "PiServer" 

                          $SourceFile = "C:\!Shared\report\Points.txt"

                          $OutputFolder = "C:\!Shared\report"

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

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

                          $Timestamp=get-date -uFormat "%m%d%Y%H%M"

                          $NewFileName = "Weather_Report_"+ $Timestamp +".csv"

                           

                           

                          $piConnection = Connect-PIDataArchive -PIDataArchiveMachineName $PIServerHost -AuthenticationMethod Windows

                          $Points = Get-Content $SourceFile 

                           

                          ForEach($Points in $Points){

                          $pt = Get-PIPoint -Name $Point -Connection $piConnection 

                              Select-Object -Property TimeStamp, Value  

                             

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

                             

                              $myDestinationForData = "$($OutputFolder)$($NewFileName)"

                             

                              $fs = New-Object System.IO.FileStream $myDestinationForData ,'Append','Write','Read' 

                              $myStreamWriter =  New-Object System.IO.StreamWriter($fs) 

                             

                              ForEach ($result in $results){ 

                                  $comma = "," 

                                  $rtimeStamp = $result.TimeStamp 

                                  $rvalue = $result.Value 

                                  $myStreamWriter.WriteLine("$($pt.Point.Name),$rTimeStamp,$rvalue") 

                              } 

                             

                              $myStreamWriter.Close() 

                             

                             

                          }

                           

                          I only get data to csv for the very last tag in my text. I tried a similar method earlier and I only got to this sadly.

                            • Re: PI Powershell: Extract data from multiple PI tags into csv with Tag names.
                              Lal_Babu_Shaik

                              Hi Katalin

                               

                              I have modified your code and would request you to use below.

                               $piServerName = “PISERVER”  
                              $SourceFile ="C:\!Shared\report\Points.txt"           #~ Directory and file name for the list of tags  
                              $OutputFolder = "C:\!Shared\report"
                              $Starttime = (get-date 8:00:00).AddDays(-1)
                              $Endtime=(get-date 9:00:00)
                              $Timestamp=get-date -uFormat "%m%d%Y%H%M"
                              $NewFileName = "Weather_Report_"+ $Timestamp +".csv"
                              
                              
                              $piConnection = Connect-PIDataArchive -PIDataArchiveMachineName $piServerName -AuthenticationMethod Windows
                              $Points = Get-Content $SourceFile 
                              
                              ForEach($piPoint in $Points){
                                  
                                  $pt = Get-PIPoint -Name $piPoint -Connection $piConn -AllAttributes
                                  Write-Host Data Write started for Tag $pt.Point.Name
                                  $results = Get-PIValue -PIPoint $pt -StartTime $Starttime -EndTime $Endtime | Select TimeStamp, Value 
                                 
                                  $myDestinationForData = "$($OutputFolder)$($NewFileName)"
                                 
                                  $fs = New-Object System.IO.FileStream $myDestinationForData ,'Append','Write','Read' 
                              
                                  $myStreamWriter =  New-Object System.IO.StreamWriter($fs) 
                                 
                                  ForEach ($result in $results){ 
                                      $comma = "," 
                                      $rtimeStamp = $result.TimeStamp 
                                      $rvalue = $result.Value 
                                      $myStreamWriter.WriteLine("$($pt.Point.Name),$rTimeStamp,$rvalue") 
                                  } 
                                  Write-Host Data Write Completed for Tag $pt.Point.Name
                                  $myStreamWriter.Close() 
                                 
                                 } 
                              
                              
                                • Re: PI Powershell: Extract data from multiple PI tags into csv with Tag names.
                                  Kata

                                  Perfect. This works. Thanks very much!

                                    • Re: PI Powershell: Extract data from multiple PI tags into csv with Tag names.
                                      Lal_Babu_Shaik

                                      Hi Katalin

                                       

                                      Modified the script to include digital pointtype check and null results.

                                       

                                      #PI Server Name  
                                      $piServerName = "PISERVER"    
                                      #Source File Path (Note : Input Tags separated by new line)  
                                      $SourceFile = "D:\InputTags.txt"   
                                      #Start Time and End Time to get Archive Data                        
                                      $Starttime = (get-date 8:00:00).AddDays(-1)   
                                      $Endtime=(get-date 9:00:00)  
                                      #TimeStamp format  
                                      $Timestamp=get-date -uFormat "%m%d%Y%H%M"  
                                      #Output folder where result should be stored              
                                      $OutputFolder = "D:\Output\"   
                                      #Output file name  
                                      $NewFileName = "ArchiveData_"+ $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 name...  
                                          Write-Host Data Write started for Tag $pt.Point.Name  
                                        
                                        
                                          $results = Get-PIValue -PIPoint $pt -StartTime $Starttime -EndTime $Endtime | 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()   
                                           
                                         }  
                                      
                                      
                                      1 of 1 people found this helpful
                                    • Re: PI Powershell: Extract data from multiple PI tags into csv with Tag names.
                                      tramachandran

                                      Lal Babu Shaik was the original script creating output for the last tag only? I did not see this behavior on my side.

                          • Re: PI Powershell: Extract data from multiple PI tags into csv with Tag names.
                            Kata

                            Thanks very much. I will try out.