Send HL7 Messages to Excel


Hello, and welcome to this tutorial about sending HL7 Messages to Excel.

We'll start by heading over to HL7 Soup, there is a free 30 day trial if you would like to follow along.

So we'll start by creating a new receiving workflow, and we can select out HL7 message to be sent in by either a directory scan, or from TCP. But first I'll give you a quick overview on how to work with workflows.

The green box up the top here represents the configuration of the receiving activity. But watch what happens when I click here. In drops an activity to follow the receiver. Click again, and here’s another. We can build a workflow that runs for each received message.

But before I get ahead of myself, let’s give you an overview of our receiver configuration screen.

Here, as you might have guessed is the workflow view panel. It helps you navigate your workflow with ease, click any activity and you jump straight to viewing its details. You can even jump directly into the activities filters or transformers by clicking on these buttons here, we’ll explain more about these shortly. Notice also that you can adjust the order of your activities with a simple drag and drop - easy.

This central section is the Activity details panel. Here you configure the properties of the receiver or its activities. It’s also the place to adjust what your activities do. It was going to send it to another TCP address, but now the message will be written to a file.

On the right we have the message logs. Here you can search for, and look at the messages that have been received by this workflow. We’ll come back here shortly and I’ll show you more, once I have sent a message.

Across the top here are the window controls. From right to left we firstly have a link for help, and then forward and back navigator buttons so I can traverse the workflow screens easier. Then the close window button, the Save and Close, and the Save.

So let’s quickly create a real workflow. I’ll make a pattern that receives an HL7 message, then extract the patient’s details and writes them into a CSV file that can be loaded into Excel for further analysis.

Firstly I navigate to my Receiving activity, and I am going to select it will come in from TCP, which is the HL7 standard. I’m happy to take the default server settings, and accept this message into port 22222. The message type will be HL7, but we could also accept XML or CSV. I don’t need the inbound message to be loaded into HL7 Soups main screen, so will uncheck here, and I just want the response message to be handled for me after all the activities have been processed.

Finally, I’m going to paste a message template here. This is a message that looks like what most of my inbound messages will be. This will help me later, and I’ll show you how soon.

Before that though, let’s configure how we will write this CSV file out. I’ll navigate to this activity, and we will change it to a file writer. Now I just give it a file location, say c:\temp\Patients.csv, and we will set the message type to CSV.

Once again I’m going to put in a Message Template, I could just bind this in from another activity like so, but as we are creating a new message, I will just type in my CSV messages structure. I want it to show the patients ID, First Name, Last Name, and the date of the message, and so I type them in.

Now comes the fun part. We are going to map the values from the inbound HL7 message to the CSV message, and we do that with Transformers. Let’s click here to edit them.

Transformers are an incredibly powerful feature, with capabilities well beyond the scope of this video, but if you click the link here you can watch our transformers video that goes into much greater depth.

For now though, notice we have a source tree and a destination tree that are generated from the Message Templates we placed into the activities. You can edit those Message templates here too, and the changes automatically reflect back on the activity properties.

The general goal of transformers is to take values from the source tree, and map them to values in the destination tree. The mappings will show here, and their details show here.

For example, we find the patients Family Name in the in the PID-5.1, and drag it across to the Last Name field in our destination tree. We’ve now created a mapping between the messages that joins the messages together.

Let’s now do the same for the patients First Name, and their ID – great. For the date of message, I’m going to do something a little different, I’m going to use a Variable directly in the destination message template. I simply select the placeholder text for the date field, and right click and insert the variable called CurrentDateTime. This message template will now show the current date each time a message is processed.

This CurrentDateTime variable is built in, but you can map your own ones here.

I’m now going to remove this additional activity that I don’t need. I can just disable it if it’s only a temporary removal. It is worth noting that if a workflow is saved with an activity that is not filed out correctly, that it will automatically be disabled. Enable it again like this once you have fixed the problem. But as I’m not going to need it at all, I will just delete it.

OK, one last thing before we run this. I want to create a filter so that we only process ADT messages. Filters can be added to any activity as a form of flow control, but in this case we want to add it to the receiver. Clicking the Filters icon takes us to the filters screen. Here I will add a filter and adjust the address to MSH-9.1 and where the value is equal to ADT.

Great, our workflow is written. Let’s save it, then I navigate across to the main HL7 Soup screen and we can test this out. We have to start this workflow running first, and we do that by clicking the Start Receiving button.

And now we can use our sender to start processing some messages. We could send individual messages by clicking here, but I’m going to click the play button and let the automatic sender run them through automatically. I’ll leave this to do its thing, and we will go back to the workflow editor and take a look at the logs.

I click the refresh button down here to refresh this list, and we can see the messages coming in. Refresh again, and there are more messages. Notice how the filtered ones show as grey so they are easily identifiable.

Each message has and ID, plus processing and completed dates. We can also expand the message to see the details of the activities too. Each of the activities are also expandable so you can view the source message, response message, and if we had any errors they would show and list the details here too.

So let’s go look at the file we have created. Because it is CSV it will load straight into excel ready for your analysis.

Let’s now go one step further, and have HL7 Soup sort the messages into different files. It could be by patient, or any other value, but in this sample I’m going to name the file for its event type. All we have to do is edit our receiver again, navigate to our transformers, and create an MSH-9.2 variable by dragging it from the Source Message into the transformers list. Now we have a variable, just navigate to the file writer, and place it in the file path by right clicking and selecting our newly created variable.

I’ll save this, then send a message through. And look, we have the message type in the file name – easy.

We hope you found this tutorial usefull. Please feel free to post any questions you might have into the comments and you would help us out greatly if you click 'like'. Also you can subscribe to this channel for more videos on the HL7 Subject.

Download 30 Day Free Trial of HL7 Soup