April 29, 2013

Maximo and Excel Integration

This article is outdated! Checkout MxLoader tool.

After having written the two articles on importing and exporting data in Maximo, this morning I woke up with an insane idea.
Is it Excel capable of parsing an XML structures and issue an HTTP calls to Maximo Integration Framework?
Did you already got my idea? I switched on my tablet and did few searches. Yeeesss! I have all the pieces of the puzzle!

The idea is to integrate Maximo and Excel using HTTP calls to a MIF Object Service. This allows to avoid passing through the generation and conversion of CSV files to exchange data between Maximo and Excel.

Let's see how to do it.


Maximo Object Structure

Open Integration > Object Structure application and create a new entry.
  • Object Structure: D-PERSON
  • Description: Demo Person Object Structure
  • Consumed By: INTEGRATION
  • Support Flat Structure: True
  • Source Objects: PERSON


Remove unwanted attributes from the Object Structure. This will simplify the exchanged data and will greatly improve performances of the integration.
Select the Include/Exclude Fields menu and check all the Exclude check-boxes except the following:
  • PERSONID
  • FIRSTNAME
  • LASTNAME
  • DISPLAYNAME

Excel

Download the sample Excel spreadsheet. Open it and enable macros if prompted.
Open the Visual Basic editor. On Excel 2007 and 2010 you may need to enable the "Developer" tab as described here (Excel 2007) or here (Excel 2010).


Open the "Module 1" and modify the following row to match you Maximo environment.

Const URL = "http://MAXIMOHOST/meaweb/os/"

Now go back to the "Query" Excel spreadsheet and click on the "Query" button. If everything goes fine your spreadsheet will be automatically filled with all the people whose name starts with the letter A.


Isn't it really cool?

If you want even more 'cooling' you can take a look at the followup article about synching data.

17 comments:

  1. Bruno,
    when I tried this , am getting Error-404:SRV0190E: file not dound :/osDPERSON. anyidea why it is happneing.
    one more concern - do I need to create External system or publish channel for DPERSON object

    ReplyDelete
    Replies
    1. also when i tried to paste the url("http://MAXIMOHOST:9080/meaweb/os/") in browser

      I get this message -
      Servlet is running. Please use HTTP POST to post data

      Delete
    2. Please check if you have included the final slash '/' in the URL constant. Check also the name of the Object Service, it must be exactly 'D-PERSON'.
      You don't have to create a external system or publish channel.
      Cheers

      Delete
    3. yes that was missing also , i have to pass the credential as websphere is currently configured to use App security.

      Delete
    4. Bruno, I am also getting the same error - "Servlet is running... HTTP POST to post data"
      Do you know what else could be a possible issue?

      Delete
    5. I basically get the following pop-up error in MS Excel -
      Run-time error: Object variable or With block variable not set

      I believe my MAXIMOHOST is supposed to be one the VM's (i.e. VMMAXAPPDEV) and I get the HTTP POST message when I try to put that weblink in the web browser too.

      Could this be a security issue?

      Thanks for your help (as usual).

      Delete
  2. Bruno,

    Could you please let me know if it is different from REST ? is it supported in Maximo 7(where REST was not available if I am right).

    Thanks in advance,
    Faisal

    ReplyDelete
    Replies
    1. Yes. It is different from REST... but very similar.
      Available in Maximo 7.

      Delete
  3. Cool Bruno... :D this really great..
    This also giving me an idea to interfacing maximo data with SUNAccounting using this feature.
    Thanks a lot Bruno.

    ReplyDelete
  4. Bruno nice post, but i´m getting an error at:

    "Set objSet = objXML.ChildNodes(1).ChildNodes(0)"

    Any idea?

    Run-Time Error '91': Object variable or With Block variable not set (Using Excel 2010).

    ReplyDelete
    Replies
    1. I´m testing it and looks like objXML.LoadXML(objHTTP.responseText) is false.

      Could it be because we have Active Directory validation?

      Delete
    2. I got the same failure as Yerman,
      Brune, have You got any idea what I did wrong?
      Thanks a lot bruno,

      Delete
  5. I have finally published MxLoader on developerWorks.
    Please join the MxLoader community and give it a try.

    ReplyDelete
    Replies
    1. Brilliant piece of work. Well done!

      Delete
  6. Hi Bruno,

    Do you have any idea about Maximo Integrating with DMS(Doc Management System) System ?. If yes ...Could you please let me know the steps that i have to follow .

    ReplyDelete
  7. Is there a way to get xml of MXINCIDENT and MXSR with associated WORKLOG and COMMLOG without changing maximo configuration?

    ReplyDelete