Topic
9 replies Latest Post - ‏2014-07-15T20:35:06Z by bportaluri
ZeyadElSherif
ZeyadElSherif
30 Posts
ACCEPTED ANSWER

Pinned topic Import/Upload Meter Readings through MIF (From an Excel Sheet)

‏2012-09-25T15:35:17Z |
Dear All,

I am using Maximo 7.5.0.1 and I want to import the meter readings for the assets already identified in the system. The meter readings are collected daily in an Excel Sheet that has the Asset ID and the New Meter Reading.

Can you please guide me on how to upload this data to Maximo through MIF? Which Object Structures to use and what columns that need to be included to achieve a successful upload?

Thanks,
Zeyad
Updated on 2012-10-15T20:32:21Z at 2012-10-15T20:32:21Z by CamSimpson
  • venkatapraveen
    venkatapraveen
    15 Posts
    ACCEPTED ANSWER

    Re: Import/Upload Meter Readings through MIF (From an Excel Sheet)

    ‏2012-09-26T13:10:27Z  in response to ZeyadElSherif
    Hi Zeyad,
    I hope this requirement is not possible from mea, as there would be no

    source of reading a file..

    May be java file can do that for you..

    Thanks ..
  • David_McCormick
    David_McCormick
    8 Posts
    ACCEPTED ANSWER

    Re: Import/Upload Meter Readings through MIF (From an Excel Sheet)

    ‏2012-09-30T02:17:20Z  in response to ZeyadElSherif
    Actually, I am working on the same thing right now. I'm using Visual Studio Tools for Office to write a module in C# that reads the worksheet and uploads it to MIF using SOAP. The appropriate Object Structure is MXMETERDATA. You also need to create a Web Service in Maximo based on MXMETERDATA and a Web Reference (such as MXMETERDATAWebRef) to it in Visual Studio. Here's the relevant section of my C# code:

    MXMETERDATA proxy = new MXMETERDATA();
    MyProj.MXMETERDATAWebRef.MXMETERDATA_METERDATAType[] data = new MXMETERDATAWebRef.MXMETERDATA_METERDATAType10;
    proxy.Credentials = new NetworkCredential(username, password);
    data[0] = new MaxWebRef.MXMETERDATA_METERDATAType();
    MyProj.MXMETERDATAWebRef.MXStringType assetNum = new MyProj.MXMETERDATAWebRef.MXStringType();
    assetNum.Value = "2733";
    data[0].ASSETNUM = assetNum;
    MyProj.MXMETERDATAWebRef.MXStringType changeBy = new MyProj.MXMETERDATAWebRef.MXStringType();
    changeBy.Value = "MAXADMIN";
    data[0].CHANGEBY = changeBy;
    MyProj.MXMETERDATAWebRef.MXDateTimeType changeDate = new MyProj.MXMETERDATAWebRef.MXDateTimeType();
    changeDate.Value = DateTime.Now;
    data[0].CHANGEDATE = changeDate;
    MyProj.MXMETERDATAWebRef.MXBooleanType doRollOver = new MyProj.MXMETERDATAWebRef.MXBooleanType();
    doRollOver.Value = false;
    data[0].DOROLLOVER = doRollOver; ;
    MyProj.MXMETERDATAWebRef.MXStringType inspector = new MyProj.MXMETERDATAWebRef.MXStringType();
    inspector.Value = "MAXADMIN";
    data[0].INSPECTOR = inspector;
    MyProj.MXMETERDATAWebRef.MXBooleanType isDelta = new MyProj.MXMETERDATAWebRef.MXBooleanType();
    isDelta.Value = false;
    data[0].ISDELTA = isDelta;
    MyProj.MXMETERDATAWebRef.MXDoubleType measurementValue = new MyProj.MXMETERDATAWebRef.MXDoubleType();
    measurementValue.Value = 456;
    data[0].MEASUREMENTVALUE = measurementValue;
    MyProj.MXMETERDATAWebRef.MXStringType meterName = new MyProj.MXMETERDATAWebRef.MXStringType();
    meterName.Value = "RUN";
    data[0].METERNAME = meterName;
    MyProj.MXMETERDATAWebRef.MXStringType newReading = new MyProj.MXMETERDATAWebRef.MXStringType();
    newReading.Value = "987";
    data[0].NEWREADING = newReading;
    MyProj.MXMETERDATAWebRef.MXDateTimeType newReadingDate = new MyProj.MXMETERDATAWebRef.MXDateTimeType();
    newReadingDate.Value = DateTime.Now;
    data[0].NEWREADINGDATE = newReadingDate;
    MyProj.MXMETERDATAWebRef.MXStringType orgID = new MyProj.MXMETERDATAWebRef.MXStringType();
    orgID.Value = "ENERGY";
    data[0].ORGID = orgID;
    MyProj.MXMETERDATAWebRef.MXStringType siteID = new MyProj.MXMETERDATAWebRef.MXStringType();
    siteID.Value = "CW";
    data[0].SITEID = siteID;

    DateTime creationDateTime = new DateTime();
    bool creationDateTimeSpecified = false;
    string baseLanguage = "EN";
    string transLanguage = "EN";
    string messageID = "NoDef";
    string maximoVersion = "7.5.0.1";

    proxy.SyncMXMETERDATA(data, ref creationDateTime, ref creationDateTimeSpecified, ref baseLanguage, ref transLanguage, ref messageID, ref maximoVersion);

    Does this help?

    David
    • ZeyadElSherif
      ZeyadElSherif
      30 Posts
      ACCEPTED ANSWER

      Re: Import/Upload Meter Readings through MIF (From an Excel Sheet)

      ‏2012-10-08T05:09:30Z  in response to David_McCormick
      Dear David,

      Thank you for your reply...

      I think what I wanted to know is the MXMETERDATA Object Structure. I will use this object to manually upload the csv file each day containing the new meter readings through the external system I created for uploading data to Maximo.

      Further down the line I want the process to be somehow automated so I will look into your code for possibly doing something similar.

      Many Thanks for you Help...

      Best Regards,
      Zeyad
  • CamSimpson
    CamSimpson
    2 Posts
    ACCEPTED ANSWER

    Re: Import/Upload Meter Readings through MIF (From an Excel Sheet)

    ‏2012-09-30T20:23:08Z  in response to ZeyadElSherif
    Hi,
    I have simply enabled MIF tables for meter readings and then inserted the readings into these, and Maximo does the reset (there is a cron task for how often Maximo checks the MIF tables). With MIF tables there is the table for the object you are importing, and a table for Maximo to control/monitor.

    Cameron.
    • ZeyadElSherif
      ZeyadElSherif
      30 Posts
      ACCEPTED ANSWER

      Re: Import/Upload Meter Readings through MIF (From an Excel Sheet)

      ‏2012-10-08T05:13:22Z  in response to CamSimpson
      Dear Cameron,

      Thank you for your reply...

      Can you please elaborate more on the MIF tables you mentioned and how would I automate the process of importing meter readings to Maximo. Right now they collect Meter Readings manually in Excel Sheets on a daily basis (there are about 1000 readings a day) and I want to upload those sheets on Maximo; if there is anyway to automate the process as you mentioned that would be great

      Appreciate your help...

      Best Regards,
      Zeyad
  • David_McCormick
    David_McCormick
    8 Posts
    ACCEPTED ANSWER

    Re: Import/Upload Meter Readings through MIF (From an Excel Sheet)

    ‏2012-10-08T14:27:32Z  in response to ZeyadElSherif
    Zeyad,

    I believe you will need the following columns from MXMETERDATA: ASSETNUM, CHANGEBY, CHANGEDATE, DOROLLOVER, INSPECTOR,ISDELTA, METERNAME, NEWREADING, NEWREADINGDATE, ORGID and SITEID. I spoke with a couple of IBM support reps about this and they couldn't identify what the remaining columns were for. Presumably something requested by large influential customers. I do know when you try to load the data through a SOAP client (like soapUI), you'll get an error message telling you if a required value is missing.

    David
  • CamSimpson
    CamSimpson
    2 Posts
    ACCEPTED ANSWER

    Re: Import/Upload Meter Readings through MIF (From an Excel Sheet)

    ‏2012-10-15T20:32:21Z  in response to ZeyadElSherif
    Hi,
    First you need to configure the integration objects in Maximo. Either use the out of the box meter reading object structure, or duplicate to use a copy of it. You need to tick the Support Flat Structure. Then you set up the Enterprise Service. Again you have the choice to use the out of the box one, or create one that uses the object structure you decded to use. On this you need to create a name for the interface table - is the database table name you will insert readings in for Maximo to pick up and insert into the "real" Maximo. Next you need an external system that uses the MXIFACETABLE end point. Again there is an existing out fo the box one or you can simply create one. You enable it, add your enterprise service and again enable that. If you have more than one meter on each asset, I suggest you do not tick the "Use Continuous Queue" as you will get warning/errors ("record updated by another user, refresh and try again"). In the External System, from the Select Action Menu you want to chose the Create Interface Tables option for Maximo to create the tables (it will take a while so just wait).
    Last step in Maximo is the cron task - this controls how often Maximo searches for new interface records in the tables (it then grabs the new records and processes them) . The cron task is called IFACETABLECONSUMER and you just need to create an cron task instance, set a schedule (say every minute), and activate it.
    If you look at the parameters on the cron task you will see a parameter called QUEUETABLE - the value in here is important. What happens is there is one queue table that gets used for all interface table interfaces you create. Maximo should have a default in there which you will use. So when you enter a meter reading into the interface table, you will also insert a reading into the queuetable.

    This is the maximo setup done - no restarts or anything needed

    In Excel what you do here really depends on you and your excel skills. In our case I let the user enter in a filter to select the assets and their meters they will download from maximo - this then queries the Maximo database directly, down loads those assets and their meters (so the user can see the last reading, and you can use Excel to check the new reading value is larger than the last. Then the user has an upload button and this does the inserting onto the interface tables. You need a sequence number to match between the queue table and the interface table. Here is a slice of my code in Excel.

    vSeqNum = Format(Now(), "yyMMddHhNnss") & Right(Format(Timer, "#0.00"), 2)

    vSQL = "Insert into FHMTRIMP_IFACE"
    vSQL = vSQL & " (ASSETNUM,IMPORTED,METERNAME,METERREADINGDATE,ORGID,READING,SITEID,TRANSID, TRANSSEQ,METERREASON)"
    vSQL = vSQL & " Values"
    vSQL = vSQL & " ('" & vAssetNum & "',0,'" & vMeterName & "','" & vMeterReadingDate & "','" & vOrgID & "','" & vReading & "','" & vSiteID & "','" & vSeqNum & "', 0,'READING')"

    vConn.Execute vSQL
    vSQL = "insert into mxin_inter_trans (ACTION, EXTSYSNAME,IFACENAME,IMPORTMESSAGE,TRANSID,TRANSLANGUAGE)"
    vSQL = vSQL & " values (null,'TABLES','FHMTRIMPEntServ',null,'" & vSeqNum & "',null)"
    vConn.Execute vSQL

    My latest version of the excel file is customised for our Transportation Maximo environment so whilst I am happy to include a copy of it here, you need to be confident around Maximo and Excel to reverse engineer it and learn from it for your Maximo environment. I should have a simplier version from my last job somewhere but not too sure where. Once you know about inserting the data into th Maximo interface tables and have tested that with Maximo, the Excel site is really about what you want to design - for example I have a custom Maximo property that holds a version number so if I change that, the excel tool stops working. This enables some level of control over what the users are running.
  • MdTauhidulIslam
    MdTauhidulIslam
    1 Post
    ACCEPTED ANSWER

    Re: Import/Upload Meter Readings through MIF (From an Excel Sheet)

    ‏2014-07-15T11:54:33Z  in response to ZeyadElSherif

    Hello David

    First I have created a object structure name personentry and created a webservice name personentry from object structure . And Deployed the websirvice and call service from C# Desktop application as a PersonReference .

    Now when I declare the object of Person Reference I get  CREATE/EDIT/DELETE PERSON REQUEST/RESPONSE .

    But I did not get  this object PERSONENTRY  like your MXMETERDATA proxy = new MXMETERDATA();

    I get as well PERSONENTRY _PERSONType . So How can I get this as like adjuctly web service name . If any steps I have mistaken please

    reply me .

    Thanks

    Tauhid

     

    Attachments

  • bportaluri
    bportaluri
    60 Posts
    ACCEPTED ANSWER

    Re: Import/Upload Meter Readings through MIF (From an Excel Sheet)

    ‏2014-07-15T20:35:06Z  in response to ZeyadElSherif

    You can import meter readings into Maximo from your Excel spreadsheet using MxLoader.

    Use the following template in the first two header rows.

    MXMETERDATA METERDATA Sync      
    ASSETNUM SITEID METERNAME NEWREADING NEWREADINGDATE INSPECTOR

    The NEWREADINGDATE and INSPECTOR columns are optional.

     

    Regards,

      Bruno