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:
- Use the UFL interface but this doesn't work with Excel.
- 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.
- 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).
- 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.