Announcement

Collapse
No announcement yet.

Transformer mappings from a spreadsheet?

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Transformer mappings from a spreadsheet?

    I'm using Mirth Connect v3.0.1.7051 for Windows 7.

    I have two XML files with ID numbers in them, where I want to map an ID in the input file to an ID in the output file.

    So this file:
    Code:
    <in>
        <id>2</id>
        <id>5</id>
        <id>11</id>
    <in>
    ...would be transformed to this file:
    Code:
    <out>
        <id>9</id>
        <id>14</id>
        <id>23</id>
    <out>
    This is easy enough with regular mapping steps ("out[0] <-- in[0]", "out[1] <-- in[1]", etc.). There are going to be thousands of IDs per file though, and it should be easy for non-developers to create new ID mappings.

    Is it possible for Mirth to import mappings from a spreadsheet, either CSV or Excel? Maintaining a spreadsheet, where there'd be two columns to match the input and output IDs, would be easier than going into Mirth and editing mapping steps by hand.

  • #2
    Creating mappings between XML formats representing lists

    I'm trying to map two XML formats representing lists. For example, if my input XML looks like this:
    Code:
    <in>
        <a>1</a>
        <a>2</a>
        <a>3</a>
        ...
    </in>
    I'd want my output XML to look like this:
    Code:
    <out>
        <b>450</b>
        <b>600</b>
        <b>785</b>
        ....
    </out>
    Where the value of 1 gets mapped to the value of 450, 2 gets mapped to 600, etc. And it is mapped into a differently named XML element in the output XML. There could be one, many or no values within the <in> element.

    The transformation editor only seems to let me make mappings between fixed static nodes though. How do I do mappings for XML formats representing lists of things?

    In my case, the XML I am working with represents lists of drugs. I'd have a list of multiple drugs given to a patient that is using our internal code IDs. I want to have a transformation that will use a global mapping(?) where I specify our internal code that is associated with a specific code and have it iterate through all those looking for a match and when it finds when it creates the resulting XML output. All I've managed to do so far is create a global mapping by loading a CSV file through JavaScript (is there a better way though?).

    Comment


    • #3
      You can do that with E4X. For example, just create an outbound XML template of <out/>, and then manually append nodes:

      Code:
      var node = <{nodeName}>{nodeValue}</{nodeName}>;
      tmp.appendChild(node);
      In that code, nodeName and nodeValue are variables that you populate beforehand, in whatever way you want (selecting from a database, iterating through a properties file, etc.).

      I don't understand where or how that data is being stored, so I don't know what would be the best way of iterating through it. If you can elaborate on that more, maybe I could give some suggestions.

      More info on E4X here: http://wso2.com/project/mashup/0.2/d...uickstart.html
      Last edited by narupley; 02-03-2014, 09:28 AM.
      Step 1: JAVA CACHE...DID YOU CLEAR ...wait, ding dong the witch is dead?

      Nicholas Rupley
      Work: 949-237-6069
      Always include what Mirth Connect version you're working with. Also include (if applicable) the code you're using and full stacktraces for errors (use CODE tags). Posting your entire channel is helpful as well; make sure to scrub any PHI/passwords first.


      - How do I foo?
      - You just bar.

      Comment


      • #4
        Thanks very much for the prompt reply. I apologize if my first posting wasn't clear so I'll attempt to be more specific.

        The intent is to have an XML file come in as an input to a web service in the channel. There will then be an output file in XML that will go to a different destination web service.

        We want to be able to have non-developers create mapping documents that are like a key-value pair for these large mapping instances. The key would be the source ID for a field option value and the value would be the target ID for a field option value.

        As an example, in our product the Drug XML element (let's say it is <Drugs>) may have a source ID of 15 for Morphine and a source ID of 20 for Aspirin. In the target system they have a Drug XML element (let's say it is <Treatments>) where we want to map source of 15 to target of 4000 and source of 20 to 5000. So we would have a file that would have two lines in it (or could be JSON formatted or something else)
        15, 4000
        20, 5000

        Right now I created a global mapping by loading in a CSV file through the channel's Deploy script. So I am storing the mapping data inside a dictionary in globalChannelMap, where the key is the source ID and the value is the target ID. If you have any thoughts or suggestions on better ways to store the mapping files/data or access them that would be greatly appreciated.

        Our actual implementation will be that the incoming message is our custom XML and the outgoing message will be an HL7 V3 message. So does the node creation and appendChild suggestion you have work OK in that situation or are there special methods for writing into HL7 V3 from Javascript?
        Last edited by AaronJM; 02-04-2014, 03:53 AM. Reason: Grammar

        Comment


        • #5
          Gotcha. Well, loading from a properties file is certainly a valid method. So is loading from a database, which you can do either once on deploy, or on-the-fly.

          If users change those mappings and then expect new messages coming into the interface to use those new mappings immediately without any other intervention, then it would make sense to update that information on-the-fly for every message. That can be done either with a properties file or with a database table.

          If on the other hand you intentionally don't want users' changes to be updated in real time without intervention from a channel engineer who would have to redeploy the channel, then it makes more sense to load them into memory once on deploy, and use them thereafter.

          If using a properties file, how will the users update that file? Do you have a GUI with which to do that, or are they expected to manually edit a CSV (probably a bad idea)? If using a database to store that information, you would certainly need some sort of user interface for it, unless all your users are SQL savvy or something (and even then, KISS). In either case, do you have a backup plan? Assuming that wherever you're storing that data (be it in a flat file or in a database) is on some clustered NAS then it's probably okay either way, but that's something to consider.

          There are other options as well. Perhaps the mappings are stored on a completely separate machine, and instead of having Mirth Connect talk straight to that external database, you put an instance of Mirth Connect on that machine as well, and then prop up a SOAP or RESTful web service with a well-defined interface (like some JSON or something). In this case, you've now abstracted the communication/transport layer from the data retrieval layer, and you can swap out the latter layer from a flat file to a database table without affecting the HTTP interface at all.

          So really, it all depends.
          Step 1: JAVA CACHE...DID YOU CLEAR ...wait, ding dong the witch is dead?

          Nicholas Rupley
          Work: 949-237-6069
          Always include what Mirth Connect version you're working with. Also include (if applicable) the code you're using and full stacktraces for errors (use CODE tags). Posting your entire channel is helpful as well; make sure to scrub any PHI/passwords first.


          - How do I foo?
          - You just bar.

          Comment


          • #6
            Originally posted by AaronJM View Post
            Our actual implementation will be that the incoming message is our custom XML and the outgoing message will be an HL7 V3 message. So does the node creation and appendChild suggestion you have work OK in that situation or are there special methods for writing into HL7 V3 from Javascript?
            It won't be the exact same code because obviously HL7v3 has a more complex structure than the XML you posted. But yes, that can all be done rather easily. As noted before you can find a useful primer on E4X here: http://wso2.com/project/mashup/0.2/d...uickstart.html
            Step 1: JAVA CACHE...DID YOU CLEAR ...wait, ding dong the witch is dead?

            Nicholas Rupley
            Work: 949-237-6069
            Always include what Mirth Connect version you're working with. Also include (if applicable) the code you're using and full stacktraces for errors (use CODE tags). Posting your entire channel is helpful as well; make sure to scrub any PHI/passwords first.


            - How do I foo?
            - You just bar.

            Comment


            • #7
              I am new to Mirth and javascript, and i am trying to load csv file to the globalmap or global channel map. Is there a way you could post the javascript code you used in the Deploy tab???

              Comment


              • #8
                I ended up using Apache POI to import Excel spreadsheets. I said I was using CSV files, but they start out as Excel files and Apache POI allows me to skip converting them to CSV.

                Reading my Excel files looks like this:
                Code:
                var captionID = [email protected]()
                
                var mappingFile = new Packages.java.io.FileInputStream(new Packages.java.io.File('C:\\mappings.xlsx'))
                var mappingsBook = new Packages.org.apache.poi.xssf.usermodel.XSSFWorkbook(mappingFile)
                var mappings = mappingsBook.getSheetAt(0)
                
                var rowIt = mappings.iterator()
                while (rowIt.hasNext())
                {
                	var row = rowIt.next()
                
                	var cellCaptionID = row.getCell(1).getNumericCellValue().toString()
                	if (captionID.equals(cellCaptionID))
                	{
                		typeIdentifier = row.getCell(3).getStringCellValue()
                		code = row.getCell(2).getNumericCellValue().toString()
                		break;
                	}
                }
                
                tmp['CODE'] = code
                Here I'm not populating any global map. Instead I'm reading my Excel file to transform a field from my input format before writing it to a file in my output format.

                Comment


                • #9
                  Hi Aaron,

                  Do you have example of channel and excel file? It would be great if you post channel.

                  Thank you in advance.

                  Vijay

                  Comment

                  Working...
                  X