Add repeating HL7 values to CSV
Welcome to this tutorial, where we look at adding repeated OBX values into a CSV file and not just OBX. Any other repeated value that you want to get into a CSV, this tutorial will show you how to do it.
So, for example, let's have a look at some of the messages that come with HL7 Soup, and we can see here that we've got a selection of fields highlighted. These are the ones that we want to put into a CSV file. We have got the patients ID, first name, and last name, and then also we want to get into all the result values out of the OBX's. So, we can see here there is this number, and we want the OBX-5's from each of these OBX's, and you can see there is actually a number of OBX values here, and we want to get all of these values and put that into a CSV file that we can either load in another application or load into Excel and take a look at that there. The difference is that instead of transforming one HL7 message into one CSV record, we are actually taking one HL7 message and converting it into multiple rows of CSV. Effectively we are creating a single file of CSV that is going to have multiple rows depending on each of the OBX values.
So how do we do that?
We will start by loading Integration Host, and if you haven't already seen the introduction videos to Integration Host, I suggest that you do take a look at those at the end of this tutorial. I'm going to assume that you already know a thing or two about it, so I'm going to click on 'new' and begin. So, we have got a couple of options here, common ways of receiving an HS7 message for this type of thing. Often it could be as a file. We could use the directory scanner and actually scan for the directory. I have done that already in another tutorial, so I think in this one, we're going to get a TCP receiver instead. It's going to be called "add repeat values to CSV". I'm going to receive on port 22222, and then as always, we are going to add in a default message template. One of the sample messages will be used. The one selected has an OBX segment in it, and then we want to send that off to a file, so another activity is going to be added in, and it will be set as a file writer We are going to name it 'write CSV' and give it a file name. The file name is pasted into the 'file to write' section under the name, and then it is going to be called 'File.csv', and we want to make sure that it is unique for each HL7 message that comes.
To do this, we are going to drag in the message control ID and place that into the file name, and that will append the control ID to each file so that they will be uniquely labelled. Then we are going to select 'Text' from the 'Message Type' drop-down menu as opposed to CSV because we are doing multiple messages into one. The reason is that we are going to construct the entire message in one go. CSV would hinder us as it would be trying to enforce that single record policy. So, we are going to remove the text that is there in the message, and because it is not CSV, we can provide the header manually. We are going to have an ID, First name, Last name, and value (ID,Firstname,Lastname,Value). The ID, First Name and Last Name will be the same for every single record, it is the value that we are going to have change per OBX values, so this is just a simple example to show you the principles involved. Once the headers are in place, we will construct the lines.
To do this, we will click into the transformers, and the first thing that we are going to do is bring in those fields that we need and put them in as variables. We will select the patient's ID and drag this into the transformers list and notice that it automatically creates the patient's ID as a variable. We will do the same for the family name of the patient, the given name of the patient and the OBX-5 Observation value. Then we will add in a 'for each' because we want to loop over every single OBX value and drag that OBX value, and because of that, we are going to put the 'OBX Observation Value' inside the "for each", and that's because all of the other values are only needed to be set once, and only the observation value is going to repeat. Now we need to write out the line, and to do this, we select the transformers and add an 'Append Line' and into that, all we have to do is build up our line of text that represents CSV. The easiest way to do this is to change the source to point to our variables (select drop-down menu under 'Source' and select 'Variables') and notice that it lists all of the variables that we just created, and these can be dragged and dropped into the source path of our appended text. The Patient ID, Patient First Name, Patient Family Name, Observation name are all dragged and dropped in the text box under 'Append Line'.
You can see now that we have constructed a line with a comma separating all of these variables, and it is going to repeat for all of the 'For each' and write the file out. The only thing to add to this is that you may want to format some of these values, so the options are to click on your family name, and we can make sure that the casing of that is always correct by going to 'Format Text and Names' and give it the nickname casing. Obviously, if you had any dates and you were going to Excel, you are going to want to convert that to your local date format so that it shows correctly in Excel rather than the HL7 date format. It must be noted that if you have got commas that appear inside any of your values, they will corrupt this text.
In this example, we have a comma in the sample values. There is one in the observation values. What we have to do is right-click on that observation value variable and select 'Encode (Escape Characters)' and then select 'CSV Encode'. What that will do is it will look at the contents of that value, and if there is a comma inside it, it will put it inside of quotes which is the CSV format, and it will make sure that the comma is treated as text and not a structure in the CSV format and so with that, the workflow is completed so we will save and close that.
You will see that it is already running, so we are going to use the sample messages in HL7 Soup to try this out. The one selected for this example has a number of rows of OBX's, and we will send that through and use the same port 22222. We will then click send, and then we will get our response, and if we go across to the integrations here, this is HL7 soup view of the Integration Host it is the same thing as going to Integration Host, and we can see that it has worked and the logs can be refreshed. The values will then come in, and it indeed seems to have written it out correctly. If we have a look at the file system, indeed, the CSV has been created. If we open it up into Excel, it will load, and it has populated all of the fields. However, some of these fields still have the carets in them, and that is because the OBX 5.1 did still have components in it.
We will go back and edit the workflow to fix this from the integrations tab. We will load up that workflow pattern again, select 'Transformers' and select the 'Observation' value and we will make that choose the 5.1 value instead. That way, if there are components in there, we will only get the first item of the OBX. We will save and close that and try again with the other sample message and hit send. We have again got success, so head over to the file system and here is where the next file created will be. Note that the file name has been given a unique name as we designed, and this will be loaded into Excel. This is all laid out correctly. We will jump quickly to Integration Host to look inside the logs. We refresh the logs when we look at that first item and where it wrote it out. Now, we selected the right activity, and you will notice this line here. It has a comma inside of it, and this is the comma inside of the actual text, and it has put quotes around that particular row and has done the same in another row, and that's what encoding for CSV did for us with a simple right-click option.
As always, if these videos have helped you, why not consider returning the favour? Give us a like and subscribe to our video feed on YouTube. We have got constant videos coming out all of the time that will help make you an HL7 expert, and we'd love to hear any feedback you've got for us or suggestions for future videos.