10 Replies Latest reply on Mar 19, 2018 1:41 PM by gregor

    How to publish excel file through power shell command

    newfunda

      Hi,

       

      I have excel file ready with me but now instead of opening the file and selecting the server and database, I want to do this using Power Shell command.

      Steps to do are:

      1. Import OSISoft PI SDK in order to call the PI commands

      2. Set variables for PI server and database

      3. Read the excel file

      4. Run Publish command in Create Only mode

      5. Save and report if any issue found in excel data

      6. Show success status to user

       

      Kindly help me achieve this task, I was searching the internet but couldn't find related topics, hope someone here can help here. Kudos! to real developers.

       

      Regards,

      Manish

        • Re: How to publish excel file through power shell command
          KenjiHashimoto

          Why do you need to use Powershell?

          If you want to keep reports as Excel format, then VBA can be used.

          I saw a user case that Excel is opened by task scheduler and it automatically update values/ save as a report/ close.

          (You are trying to use PI SDK from VBA, it is possible but PI DataLink function also can be used to get PI Data)

          If you want to get PI data by Powershell, then OSIsoft is already provided tool for it.

          Getting started with the new PowerShell Tools for the PI System

          https://techsupport.osisoft.com/Documentation/PI-Powershell/title.html

            • Re: How to publish excel file through power shell command
              newfunda

              Thanks for quick response.

               

              VBA should be fine, please let me know the VBA code sample to publish the file.

               

              I don't want to get the data, I have the data, only need to publish without manually selecting server and clicking publish.

               

              Please help.

               

              Cheers!!

              Manish

                • Re: How to publish excel file through power shell command
                  KenjiHashimoto

                  OK. Then open the VBA and go to ThisWorkbook > select Open from pulldown list, it creates Workbook_Open().

                  If you write code here, this code will run if you open it manually or windows task scheduler opens it.

                  If you want to use PI SDK, then following code should work for you. I previously made it as retrieve data and save it as a CSV file.

                  Dim myPIServer As PISDK.Server  
                  Dim pt As PIPoint  
                  Dim pvsstarttime As PITime  
                  Dim pvsendtime As PITime  
                  Dim pvs As PIValues  
                  Dim contents As String  
                  Dim val As PIValue  
                  Set myPIServer = PISDK.Servers.DefaultServer  
                  Set pt = myPIServer.PIPoints("cdt158")  
                  Set pvsendtime = myPIServer.ServerTime  
                  Set pvstarttime = pvsendtime.Clone  
                  '1 hour data  
                  pvstarttime = pvsendtime - 3600  
                  Set pvs = pt.Data.RecordedValues(pvstarttime, pvsendtime, btAuto)  
                  For Each val In pvs  
                      contents = contents + CStr(val.TimeStamp.LocalDate) + "," + CStr(val.Value) + vbCrLf  
                  Next  
                    
                  Open "C:\tmp\Data.csv" For Output As #1  
                          Print #1, contents  
                  Close #1 
                  
                  

                  Don't forget to reference PI SDK 1.3 type library before running the code.

                  Visual Basic Editor > Tools > References > PISDK 1.3 Type Library, PITimeServer 1.0 Type Library

                   

                  This is quit old technology and I don't recommend it. (Because sometimes Excel has problem to open or run VBA code)

                  If I need to implement it, I will go to AFSDK or powershell tool to do that but it also should work for you.

                  3 of 3 people found this helpful
                    • Re: How to publish excel file through power shell command
                      newfunda

                      Hi,

                      Instead of manually clicking publish I want command to do the same either in VBA or Power Shell.

                      I need to save data to server, not to get the data. In your code you are not doing check-in to server.Below is screenshot for what the question was(a command which will publish the excel I have

                        • Re: How to publish excel file through power shell command
                          KenjiHashimoto

                          Ah, you want to use PI Builder.

                          A lot of people use AFSDK + C# to achieve it. (Without Excel)

                          If you want to connect AF, then PI SDK 1.3  Type Library could not achieve it.

                          However, I know that Excel can call PI Web API.

                          Announcing PI Web API Wrapper for VBA

                          It is a little bit tricky to code it by VBA but it allows you to update AF information.

                          • Re: How to publish excel file through power shell command
                            gregor

                            Hello Manish,

                             

                            PI Builder is a COM Add-in for Microsoft Excel and I am pretty certain that it is executed in its own application domain which means that you don't have programmatic access to its controls.

                            If I understand your question properly, you like to publish either PI Point configuration or an Asset structure by script rather than having users use the PI Builder add-in. Can you please clarify?

                            Can you please also share some insights about your use case?

                              • Re: How to publish excel file through power shell command
                                newfunda

                                Dear Gregor,

                                Correct I want to use script rather than doing manually publish, tools I can use is Power Shell, VBA, Excel.

                                My case is I already prepared final sheet of equipment to load to server, just I need to call publish using some script not manually, because I created a Powershell tool where everything is done using tool only , so don't want at the end to let user select server and click publish, instead I will ask user to select server/db and then I will through script will call publish that's all.

                                Please share the script to call publish that's all.

                                 

                                Regards,

                                Manish

                                  • Re: How to publish excel file through power shell command
                                    gregor

                                    Hello Manish,

                                     

                                    Manish Kumar wrote:

                                     

                                    Please share the script to call publish that's all.

                                    There is no such "the script" but maybe the following example based on OSIsoft.PowerShell gets you started.

                                     

                                    $AFServerName = "MyAFServer"
                                    $AFDatabaseName = "Sandbox"
                                    $NewElementName = "MyNewElement"
                                    $NewAttributeName = "MyNewAttribute"
                                    $AFServer = Get-AFServer -Name $AFServerName
                                    $AFConnection = Connect-AFServer -AFServer $AFServer
                                    $AFDatabase = Get-AFDatabase -AFServer $AFConnection -Name $AFDatabaseName
                                    # Try loading an Element with the given name
                                    $NewAFElement = Get-AFElement -AFDatabase $AFDatabase -Name $NewElementName
                                    # In case the Element does not exist .. 
                                    if ($NewAFElement -eq $null)
                                    {
                                        # Create the new Element
                                        Add-AFElement -AFDatabase $AFDatabase -Name $NewElementName -CheckIn 
                                    }
                                    $NewAFElement = Get-AFElement -AFDatabase $AFDatabase -Name $NewElementName
                                    # Try loading Attribute with the given name
                                    $NewAttribute = Get-AFAttribute -AFElement $NewElementName -Name $NewAttributeName
                                    # If the Attribute was not found .. 
                                    if ($NewAttribute -eq $null)
                                    {
                                        # Create the Attribute
                                        Add-AFAttribute -AFElement $NewAFElement -Name $NewAttributeName -CheckIn
                                    }
                                    
                                • Re: How to publish excel file through power shell command
                                  gachen

                                  It sounds like basically you have a flat-file list of points and attributes and you want to be able to publish this to the server without needing to use Excel. Traditionally, users would do this via piconfig, but it sounds like you would prefer to use PowerShell. If this is the case, it shouldn't be too difficult...

                                  • For convenience in reading the file, you may want to save it in CSV format from Excel
                                  • Read the file in PowerShell using a cmdlet like Import-Csv
                                  • Use the PowerShell Tools for PI (as referenced by Kenji above) to manipulate the data and 'publish' to the PI Data Archive server
                                  • Display return status or save to a log file