dpereira

How to add values to a Tag from an Excel file?

Blog Post created by dpereira Employee on Oct 9, 2019

Have you ever wonder if it is possible to add values to a tag from an Excel file? Yes! That is the correct answer. However, you must be thinking that it will be difficult, right? And that is not true. We can do it easily with the PI Powershell Utility!

 

The PI Powershell utility is an extension built on the Windows Powershell utility which can be used to access multiple aspects of the Asset Framework and PI Data Archive. It leverages the extensibility of the AFSDK library to provide structured access to the variety of data stored on the OSISoft PI System.

 

Here is an example of how to How to add values to a Tag from an Excel file. You must have installed:

  • Powershell Tools for the PI System.
  • Microsoft Excel - Version tested: Microsoft Excel for Office 365

 

 

#Select the PI Server Name - Do not use Collective name 
$myPI = Connect-PIDataArchive -PIDataArchiveMachineName <PIDataArchiveAddress>

#Select the tag/Pi Point name
$tagName = '<TagName>'

#Create an Excel Application Object
$ExcelObject = New-Object -COM "Excel.Application"

#Open the Excel File, enter the path for the Excel file
$Workbook = $ExcelObject.Workbooks.Open("C:\Users\<yourUser>\Desktop\<ExcelFile>.xlsx")

#Choose the worksheet, you can use the number or the name as "Sheet1"
$Worksheet = $wb.Sheets.Item(1)

#Select the value range from the Excel sheet
$values = $Worksheet.Range("A1:100").Value2

#Select the timestamp for each value since you have to define the timestamp. It is easy to do it from the Excel File
$date = $Worksheet.Range("B1:B100").Value2

#Convert to PowerShell date time
$TimeStamp = foreach ($d in $date) { ([datetime]::FromOADate($d))}

#Add each value to the PI Data Archive specified in $myPI
for ($i = 0 ;$i -le $value.Length-1 ;$i++){
          Add-PIValue -PointName $tagName -value $value[($i+1),1] -Connection $myPI -time $TimeStamp[$i] -WriteMode NoReplace
}

 

Just be sure to substitute:

  • The hostname of your PI Data Archive from <PIDataArchiveAddress> to MyPIHostname
  • The name of the tag from <TagName> to MyTag
  • The path to your Excel file

 

Be careful, after running this script you cannot undo the insertion without a backup of the archives that cover that time range!

 

 

Enjoy!

Outcomes