9 Replies Latest reply on Dec 18, 2017 6:30 AM by yihan

    PI Excel interface

    mhalhead

      I have an interesting problem which I would like to pose to the community and see if anyone has a interesting solution; like most things there are many ways to skin this cat but I would like to see what others would recommend (I don't know everything).

       

      We have a vendor that does maintenance on some equipment every two weeks. After each maintenance then send us a report via email of the maintenance. One section of the report is the wear on the liners and discs which has the latest measurements in millimetres and the position plus the previous measurements. These measurements are extremely useful for monitoring the equipment; if analysed correctly they tells us whether we're operating the equipment efficiently. As a new set of discs and liners costs about half a million USD per unit and we have 20 units this can get expensive quickly. If you don't manage the operation you can easily destroy the discs and liners.

       

      Therefore I would like to implement an interface that automatically loads the latest wear measurements into a central PI server. The report is available by default from the vendor in two formats, PDF and Excel. The pdf option is obvious a no go. So that leaves me with the Excel option. The excel option is not my preferred option but I want to see if it’s possible. The vendor has promised that the report format will not change (famous last words) and that the wear measurements are in a named range. The file is send via email; so I can specify an address.

       

      I've thought of the following options:

      1. Use the UFL interface but this doesn't work with Excel.
      2. Have a person manually load the data from excel; I don't like relying on people for this type of thing. Especially as I'll land up using a qualified engineer as a data clerk.
      3. Write custom interface that reads the email messages and saves the excel file to a specific location. I could then have another spreadsheet with macros read the report and pushes the data into PI; alternatively I could use a VSTO to access the excel file (I think I can do this but I've never tried).
      4. Variation on the custom interface; but this time use something like SSIS to treat the excel file as a database and then push the data to PI.

       

      I don't like 3 & 4 as this means that I must maintain a custom interface; something I always try and avoid. The SSIS route works quite well for low data rates; I've done this before. Does anyone have a better solution that I haven't thought of?

       

      If not I think I'll go the 900 pound gorilla route and bully the vendor into sending a csv file with the maintenance data.

        • Re: PI Excel interface

          Have you looked at PI-UFL with the SMTP plugin?

           

          Otherwise my vote would go with either :
          - The vendor sends a CSV file (but do they know your PI tag names?) and you load via PI-UFL.
          or
          - You have a simple interface to extract the relevant values from the named range(s) and then create a csv for PI-UFL.

           

           

            • Re: PI Excel interface
              jlakumb

              Interestingly, myself and Chris Coen (Interfaces PM) had an e-mail exchange about this recently.  To make it simple, I will share what I wrote here -

               

              *****

               

              I have been noticing a customer need to pull in data from Excel into PI. Oftentimes, they have forecast data or manually entered data in spreadsheets.  It occurs to me there are multiple ways to accomplish this, depending on the file format (XLS or XLSX).

               

               

               

              For XLS files, customers can use Microsoft ACE OLEDB or JET OLEDB providers with our OLEDB COM Connector.  Of course this does not store the data in PI.  In future we may have a better story if/when the RDBMS Interface can also support OLEDB.

               

               

               

              For XLSX files, these are essentially XML so customers can use XML Interface or UFL Interface.  We may need to provide documentation (or XSLT files) to help them read the spreadsheets properly.

               

               

               

              For both XLS and XLSX files, another possibility is to manually open the spreadsheet and use the DataLink PIPutVal macro to write the data.

               

               

               

              Of course we also have ways to bring Excel data into our Client products using ODBC or OLEDB datasets.

               

              *****

               

              Now that I think about this, the easiest solution is probably RDBMS Interface.  For older Excel versions, there may be an Excel ODBC Driver available - this should work directly with RDBMS Interface.  For newer Excel versions, it seems there is only ACE or JET OLEDB Drivers.  In that case, you could use SQL Server as an ODBC-OLEDB bridge (with ACE/JET OLEDB as linked server).  Finally, the key is knowing the SQL syntax to query Excel spreadsheets.  Some Bing searches should help - here are a couple that might be useful:

               

              http://support.microsoft.com/kb/321686

               

              http://www.connectionstrings.com/excel (this is one of my favorite sites especially for information on accessing relational data sources in PI WebParts).

               

              To be honest I have not fully researched or tried the techniques mentioned above.  I am interested to hear if any of these works for you and how well.  We may collect ideas and share them through a vCampus whitepaper, KB Article or Tech Tip.  Hope this helps!  Thanks in advance for any feedback...

                • Re: PI Excel interface

                  Seems like we were both writing an answer at the same time

                   

                  Jay Lakumb

                  For XLSX files, these are essentially XML so customers can use XML Interface or UFL Interface
                  The Microsoft Excel ODBC Driver that's on my machine can read into XLSX files directly. I have Microsoft Office 2010 installed so I can't tell for Office 2007, but I would think it does and hence you wouldn't have to use the XML or UFL interface.

              • Re: PI Excel interface

                You could definitely use the PI Universal File Loader (UFL) Interface in conjunction with a little application that takes an Excel file and saves it as a CSV file.

                 

                 

                 

                Another (maybe simpler) option is to use the "Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)" ODBC driver to define an ODBC data source (DSN) that points to the Excel spreadsheet, and then use the PI Relational Database Management System (PI RDBMS) Interface to read data from it. Note that this ODBC Driver is only available in 32-bit, so you will have to run the 32-bit ODBC Data Source Administrator tool (c:\windows\SysWOW64\odbcad32.exe) if you are running on a 64-bit Windows machine.

                 

                In order for the ODBC Driver to recognize data it can read from, the data in Excel has to be part of a "named range" - this is the part where you may need to contact those who provide these Excel files... if they do not already do that, they should define a named range that includes all the relevant data within the spreadsheet.

                 

                Questions about PI Interfaces would normally be answered by regular Technical Support (as a matter of fact, neither the PI RDBMS Interface nor the PI UFL Interface are provided in the vCampus PI Products Kit), but this discussion is really on the verge of "PI Integration" so please do not hesitate to let us know if you need further assistance with this

                  • Re: PI Excel interface
                    jlakumb

                    Thanks for chiming in, Steve!  Looks like you and I were writing at about the same time, and had similar solutions.  I just want to clarify one thing you wrote about named ranges being required in the SQL SELECT query.  Actually, I dug up an old e-mail and found some sample queries that might help:

                     

                    select * from [Sheet1$]

                     

                    Selects everything from the first sheet

                     

                     

                     

                    select * from [Sheet1$A1:B10]

                     

                    Selects a range of cells from Sheet1

                     

                     

                     

                    select * from Table1

                     

                    Selects a named range called Table1

                      • Re: PI Excel interface

                        Thanks for sharing this "whole sheet" approach, Jay. This means the provider of the spreadsheet may not have to modify how they build it and define named ranges...

                         

                        For the benefit of readers that are new to the PI RDBMS Interface, I would like to clarify a little something regarding the "SELECT *" approach though... As you can read in the PI RDBMS Interface manual, the SELECT query is generally expected to provide a result-set consisting of the following columns: [timestamp], value, status, [annotation]. So unless your spreadsheet contains just that, you most likely won't be able to use a "SELECT *" type of query.

                         

                        In my case, my RDBMS PI Point is using the following as its query (in the ExDesc attribute):

                        /SQL="SELECT Timestamp, Value, 0 FROM DataTable WHERE Timestamp >?;" P1=TS
                        where DataTable is a named range defined my spreadsheet, with 2 columns Timestamp and Value.

                          • Re: PI Excel interface
                            mhalhead

                            Thank you to everyone for their suggestions. I think I'm going to try the UFL route initially; i.e. push the vendor to supply a csv file (there are advantageous to being the client).

                             

                            Another option is to use SSIS to process the files and then a RDBMS interface to get the data into PI. A couple of years ago I wrote a SSIS package that polls a number of shared drives (including a sharepoint list) looking for new versions of spreadsheets. I copied the files to a local "cache" where I extracted the data from the files into a "temporary table" (it was not a true SQL temp table) using open rowset queries. I then used a data flow task in SSIS to extract the updates and new items from the temp table into a database with a bit structure (i.e. normalised). Once the data is in a database it is quite trivial to get it into PI. This worked surprisingly well; I would get an alert maybe once a month (usually because someone changed a column heading or moved something). The advantage of using SSIS is that it is quite easy to build the extras, e.g. emails alerts when it failed, logs, ... Plus SSIS is quite nice for creating data processing workflows. SSIS can be a bit of a processor hog; so I won't recommend it for processing tens of thousands of records at a high frequency.

                             

                            The SSIS approach is a plan B for my current problem. SSIS can be setup to monitor an email account.

                             

                            I would be so bold as to suggest that a white paper on using SSIS to get data into PI might not be a bad idea coupled with the RDBMS interface you have a very power setup. I'm more than willing to donate my existing SSIS project (I would just need to santies it to make sure that there are no passwords, usernames or system names in the package); I won't win any prices for efficiency or prettiness but it worked well.

                             

                            On another topic are the any plans to move RDBMS to OLE-DB? I have not immediate needs for OLE-DB but with the current 64bit only stance of Microsoft ODBC drivers are going to become an issue in the not to distant future; as far as I'm aware most ODBC drivers are 32bit only.

                      • Re: PI Excel interface
                        proberts

                        I have placed an updated Excel spreadsheet version of one originally available in the OSIsoft library.  This one handles windows security and currently, all types of PI tags (including blobs).  The spreadsheet is provided as an attachment in the PDF document.  Feel free to use this; comments are welcome.

                         

                        https://dl.dropboxusercontent.com/u/74684355/piupdater701.zip