|
|
Rank: Newbie
Groups: Member
Joined: 9/30/2008
Posts: 1
Location: Loudon, TN USA
|
Does anyone use Excel to add data to PI for manual input? I would like to convert to using Excel to do this but really don't have the time to design it.
|
|
|
|
|
OSIsoft vCampus is a subscription-based, online offering that consists of providing everything people need to develop applications on the PI System. We invite you to take a "tour" of the OSIsoft Virtual Campus - also feel free to consult the FAQ or contact OSIsoft vCampus for more details.
|
|
|
Rank: Administration
Groups: Administration
Joined: 6/20/2008
Posts: 617
Location: Cheshire, United Kingdom.
|
Hello cebrooks,
Indeed, I have worked with a few clients where both API and SDK have been used to populate PI from Excel. Typically a list of PI tags, with value and timestamps (or no timestamp to indicate snapshot), using SDK PIPoint.UpdateValue method.
It literally takes minutes to set this up but you need to ensure your security for your PIPoints is set up correctly to prevent misuse of your spreadsheet for updating PI tags (or accidental misuse). For example, you may only want to update PIPoints belonging to a certain PointSource or create a PI user "ManualInput" and set security on the PIPoints so ManualInput can only update those tags.
There are also examples from OSI for manual input from Excel.
If you just want straightforward list of Tag, Value, Timestamp I can upload an SDK example :-)
Principal Consultant
Real-Time Data Management @ Wipro Technologies
|
|
|
Rank: Administration
Groups: Administration
Joined: 6/20/2008
Posts: 617
Location: Cheshire, United Kingdom.
|
SDK example attached - excuse the code as I just knocked it up from memory but should do the basic job.
Feel free to expand as you wish to meet your specifics.
Example code:
Code:Private PIServer As PISDK.Server
Private Const PIServerName As String = "piserver"
Private Const PIUserName As String = "piusername"
Private Const PIUserPass As String = "piuserpassword"
Public Function ConnectToPI() As Boolean
Dim bRes As Boolean: bRes = False
' Check to ensure object is created
If TypeName(PIServer) = "Nothing" Then
On Error Resume Next
Err.Clear
Set PIServer = PISDK.Servers(PIServerName)
If Err.Number <> 0 Then
bRes = False
Exit Function
End If
On Error GoTo 0
End If
' Check connection status
If Not PIServer.Connected Then
On Error Resume Next
Err.Clear
Call PIServer.Open("UID=" & PIUserName & ";PWD=" & PIUserPass)
If Err.Number <> 0 Then
bRes = False
Exit Function
End If
On Error GoTo 0
End If
' Final check of connection status - this is returned to calling method
bRes = PIServer.Connected
ConnectToPI = bRes
End Function
Public Sub UpdatePIPoints()
If ConnectToPI() Then
' Cycle through Cells starting at row 2
Dim i As Integer
For i = 2 To 10000
' If blank cell encountered then do not continue
If Range("A" & i).Value = "" Then Exit For
' Check PI tag exists
On Error Resume Next
Err.Clear
If PIServer.PIPoints(Range("A" & i).Value).Name = Range("A" & i).Value Then
If Err.Number = 0 Then
' Get the PIPoint
Dim PITag As PISDK.PIPoint
Set PITag = PIServer.PIPoints(Range("A" & i).Value)
Err.Clear
Call PITag.Data.UpdateValue(Range("B" & i).Value, Range("C" & i).Value)
If Err.Number = 0 Then
Range("D" & i).Value = "Value written to PI."
Else
Range("D" & i).Value = Err.Description
End If
Set PITag = Nothing
Else
Range("D" & i).Value = Err.Description
End If
Else
Range("D" & i).Value = "Tag does not exist."
End If
On Error GoTo 0
Next i
Else
End If
End Sub
With the PointSource example I gave earlier you would then add a conditional check based on the PIPoint.PointAttribute("pointsource").Value against a constant to ensure the tag you are writing to is allowed - of course PI security will then kick once you try to write the value.
Principal Consultant
Real-Time Data Management @ Wipro Technologies
|
|
|
Rank: Member
Groups: Member
Joined: 9/23/2008
Posts: 15
Location: Pennsylvania, USA
|
There is a spreadsheet that I have from OSISoft. It is named PI-DES.xls It allows you to insert and delete data from tags using Datalink. Try and find it on OSISoft's site; if you can't let me know.
There looks to be an older version on OSIDN (Since no one seems to use that site anymore including OSI). The version I have is from January 16th 2007.
|
|
|
Rank: Newbie
Groups: Member
Joined: 12/13/2008
Posts: 1
Location: Mexico
|
Heres an example from OSIsoft (remeber to include pipc32 module in your project) Code:'Copyright © 1995,1996 OSI Software, Inc. ALL RIGHTS RESERVED
'This is the VBA code associated with the sheet "PutVal".
'It demonstrates how to use PI-DataLink's PIPutVal() macro to
'send data from Excel to PI.
Option Explicit
'This subroutine is called by the <Send above values> button on the
'left hand side of the "PutVal" worksheet
Sub put_data1()
Dim i As Integer
Dim numoftags As Integer
'The following four variables are arguments to PIPutVal()
Dim sTagname As String 'Tagname
Dim stime As String 'Timestamp
Dim sServer As String 'PI server name
Dim valueCell As Range 'Cell reference containing value to be written
Dim resultCell As Range 'Cell reference to hold result
Dim apierr As Long
Dim rowstr As String
Dim buf As String
Dim macroResult As Variant
Dim timeCell As Range 'Cell reference to hold time of putval
i = 0
numoftags = 3 'we have three tags, from cell B4 to B6
'server is in E8
sServer = Worksheets("PutVal").Cells(8, 5).Text
While i < numoftags
'resultCell is in column 5 (E)
Set resultCell = Worksheets("PutVal").Cells(i + 4, 5)
'valueCell is in column 4 (D)
Set valueCell = Worksheets("PutVal").Cells(i + 4, 4)
'tagname is in column 3 (C)
sTagname = Worksheets("PutVal").Cells(i + 4, 3).Text
'timestamp is in column 2 (B)
stime = Worksheets("PutVal").Cells(i + 4, 2).Text
'TimeCell is in column 7 (G)
Set timeCell = Worksheets("PutVal").Cells(i + 4, 7)
'Call PIxltime to translate time
macroResult = Application.Run("PIxlTime", stime, sServer, timeCell)
' use result of PIxlTime
stime = Worksheets("PutVal").Cells(i + 4, 7).Text
'Call the PIPutVal() macro function
'Note that we pass just a , for the PIServer argument; i.e., we
'are using the default PIServer
macroResult = Application.Run("PIPutVal", sTagname, valueCell, stime, sServer, resultCell)
'move down to the row number
i = i + 1
Wend
apierr = pitm_delay(1000)
i = 0
While i < numoftags
rowstr = Format(i + 4)
buf = "H" & rowstr
Range(buf).Select
buf = "=PIExTimeVal($C$" & rowstr & ",$G" & rowstr & ",""" & sServer & """)"
Selection.FormulaArray = buf
i = i + 1
Wend
End Sub
'This subroutine is called by the <Send above values> button on the
'bottom half of the "PutVal" worksheet to send to PI 3
Sub put_data2()
Dim i As Integer
Dim numoftags As Integer
'The following four variables are arguments to PIPutVal()
Dim sTagname As String 'Tagname
Dim stime As String 'Timestamp
Dim valueCell As Range 'Cell reference containing value to be written
Dim resultCell As Range 'Cell reference to hold result
Dim sServer3 As String 'PI3 server name
Dim apierr As Long
Dim rowstr As String
Dim buf As String
Dim timeval As Double
Dim macroResult As Variant
Dim timeCell As Range 'Cell reference to hold time of putval
i = 0
numoftags = 6 'we have three tags, from cell C14 to C19
'timestamp is in row 14, column 2 (cell B14)
If (IsNumeric(Worksheets("PutVal").Cells(14, 2))) Then
timeval = Worksheets("PutVal").Cells(14, 2).Value
stime = Format(timeval)
Else
stime = Worksheets("PutVal").Cells(14, 2).Text
End If
'server is in E21
sServer3 = Worksheets("PutVal").Cells(21, 5).Text
While i < numoftags
'TimeCell is in column 7 (G)
Set timeCell = Worksheets("PutVal").Cells(i + 14, 7)
'Call PIxltime to translate time
macroResult = Application.Run("PIxlTime", stime, sServer3, timeCell)
i = i + 1
Wend
i = 0
While i < numoftags
'resultCell is in column 5 (E)
Set resultCell = Worksheets("PutVal").Cells(i + 14, 5)
'valueCell is in column 4 (D)
Set valueCell = Worksheets("PutVal").Cells(i + 14, 4)
'tagname is in column 3 (C)
sTagname = Worksheets("PutVal").Cells(i + 14, 3).Text
'use the timestamp output from PIxltime (columne G)
timeval = Worksheets("PutVal").Cells(i + 14, 7).Value
stime = Format(timeval)
'Call the PIPutVal() macro function
'Note that we pass just a , for the PIServer argument; i.e., we
'are using the default PIServer
macroResult = Application.Run("PIPutValx", sTagname, valueCell, stime, sServer3, resultCell)
'move down to the row number
i = i + 1
Wend
' read back from server
apierr = pitm_delay(1000)
i = 0
While i < numoftags
rowstr = Format(i + 14)
buf = "H" & rowstr
Range(buf).Select
buf = "=PIExTimeVal($C$" & rowstr & ",$G" & rowstr & ",""" & sServer3 & """)"
Selection.FormulaArray = buf
i = i + 1
Wend
End Sub
|
|
|
Rank: Newbie
Groups: Member
Joined: 1/8/2009
Posts: 2
|
Hi there..
I have tried above example, but macroResult return "Argument is not a string or cell reference", what does it this mean?
|
|
|
Rank: Newbie
Groups: Member
Joined: 1/8/2009
Posts: 2
|
Okey,.. I get it...  I found that, this problem happen when I use PIDatalink version 3.0.1, and after I reinstalled PIDatalink using version 3.1, problem is gone...
|
|
|
Rank: Member
Groups: Member
Joined: 7/30/2009
Posts: 11
Location: Rochester, NY, USA
|
Thanks again, RJK! 
|
|
|
Rank: Advanced Member
Groups: Member
Joined: 7/16/2008
Posts: 35
Location: UK
|
Hello, thought I would ressurect this old one to ask if anyone writing to PI from Excel has worked out now how to write to our collectives aswell? I used the example code from OSI (the outdate PIPUTVAL) but it just writes to the primary server each time. Would the SDK work? Are there ways to switch member from SDK? Who ate all the PIs?
|
|
|
Rank: Administration
Groups: Administration
Joined: 6/20/2008
Posts: 617
Location: Cheshire, United Kingdom.
|
You can switch members using PISDK but unfortunately it is an expensive call to make, so will slow down your application. Until PISDK 2010 is released (imminent) where PISDK can make use of the fanning mechanism of PI Buffer Subsystem, then the preferred approach is to write to the primary member and use PItoPI to replicate the data amongst the collective (or create a CSV file and pass it to a UFL instance that will fan the data amongst the collective - it is PIAPI based). Principal Consultant
Real-Time Data Management @ Wipro Technologies
|
|
|
|
Guest
|
