Converting CSV to HL7
This tutorial will take you through the steps with Integration Host for mapping values from a CSV file (Excel) to an HL7 message.
We use custom formatting, lookup tables, and HL7 encoding to create a file directory that is constantly monitored for new CSV files.
Here I have a basic CSV message with each row representing a single record, and I need to convert this across to HL7. For those of you not particularly familiar with HL7, I have loaded up the HL7 editor so that we can see the HL7 message in all of its glory.
The HL7 soup editor comes with this list of sample HL7 messages that you can base it on, and it provides you with a great place to navigate your message. It shows you where you are clicking it, the list of all the fields and what they represent.
I will base my structure on message “4” of the HL7 soup editors’ samples, and I will take the sample data from Excel and map those fields one at a time into an HL7 message. This could be done with HL7 receivers for a one-off process, but I really want this to be an always running process, so I am going to take advantage of HL7 Soup’s Integration Host.
HL7 soup Integration Host and HL7 soup editor are both available to download from the website, and both have trials, so you are welcome to follow along and try this out for yourself.
In this scenario, I have got my CSV to HL7 directories, and I am hoping to process any files that get dropped in here, which are CSV’s, and I have already got a sample file. I have created a couple of directories as well, which are empty. The backup directory is there in case I need reprocessing. There is also an ‘Out’ directory, and this is the directory that I hope to fill up with my HL7 files.
The next step is to go back into Integration Host and to do this start by clicking on ‘New workflow’. Straight away, it is asking where I wish to receive my data from and because I want to pick it up from the file system, I am going to select a directory scanner, and in the name field, I will enter ‘Get CSV’. Then I will take my directory, and I am going to use that as the directory that I am going to be scanning for the files. Then change the ‘File Filter’ across to ‘CSV’, and I will have it set on ‘Keep waiting for more files to be added’ I do not want it to stop once it has been processed. This is going to be a forever running process. Integration host does run as a windows service.
I will change the message type I am picking up to ‘CSVs’, and now it will ask for a message template. This indicates what the CSV will look like, and I will use that structure to map the CSV to an HL7 message.
I am going to load up my CSV in notepad and copy out the header row of the message and use that to paste in the ‘Message Template’ field. It has detected all of the fields, and it has listed them all on the right-hand side for me to use my mappings. As I do have message headers, I do not want it to process the first rows, it is comma-separated, of course, and I was going to move it to a directory once I finished processing. I will then copy the file path from our backup directory. Now I have defined the CSV file. I am going to define where it is going to be sent to in the HL7 message. So, to do that, I add another step into the workflow and set where it will go. Often with HL7 messages, they are sent off as TCP. In this case, I will be writing it out as another file, so I want to call this ‘Write HL7’, and I am going to put it into the output directory that I created. I will give it a file name, ‘MYHL7.HL7’, so I have specified that it will write out every time there is the same file name, and that will add them all into the same file. I have given it this name so that it knows where to write it out too. If it was left as it is, it would be written into a big batch file called ‘MYHL7’, but I want to separate my CSV records into separate files. I need to change this file name for every single row in the CSV, and the easiest way to do that is to right-click in here and select ‘Insert Variable’ and then select ‘Workflow Instance ID’. The Workflow instance ID is just a number that increments with every new row on the CSV. Granted, it would have also been possible if I had a unique value in the actual CSV file I wish to use. I could take that value and drag that into my file name and used that. Suppose you have another system that is going to be picking up this file immediately as this process is running. In that case, it is probably worth then set up a ‘Move File’ to another directory after processing. That will allow the file to be picked up only once it is finished processing but, in this example, I will leave this unchecked and choose the message type as HL7.
Now I need to provide a template for It. As I said, you will probably already have an HL7 message that you can use, but if not, there is a couple of sample ones that do come with integration host, and these have some data in them, these are the empty messages, and I will choose the register the patient one. Here, you can see it placed in an empty HL7 message for me with some of the key fields populated. It has our MSH line, our current date added in the appropriate format. Our workflow instance ID is used as the control. A couple of segments have some information about the patient, some information about their next of kin, and some information about a visit, which could be a great way of building up a message. I will delete the text in the ‘Message Template’ field and pretend that I have just been given one sample HL7 message that I need to populate, which is pretty standard.
As I move my mouse across an HL7 message, I get this floating window that tells me where I am in the HL7 message, and this is going to be particularly handy for populating the data. The incoming data is pretty basic, and yours will probably be pretty basic, given that it is CSV. Let’s focus on the things that I need to add. I will alter the date of the message by double-clicking on it, right-clicking on it and selecting insert a variable and then add the current date/time. In the ‘Message Template’ field, I will find the message control ID, the incrementing number, and I will insert the variable workflow instance ID. I want to ignore the other values and leave it as a ‘2.5.1’, and I will cut the end of it to remove some noise. The current date/time will come through in a standard machine time format, and I want this in the HL7 format, so I am going to right-click on it and choose ‘format data and numbers and then ‘HL7 format’ in seconds.
Now I want to populate the patient’s information. There is no next of kin information or visit information in this CSV, so I will remove those and I will try and simplify the message a little bit. If I take a closer look at the data brought in, there is the patient’s ID, first name, last name, date of birth, gender, and address line 1. A bit more information has been provided in this message, so I will tidy this up as I go along and only put in the values I need. I am creating a template that will be reused with the values stamped into their place. I will replace the values in the sample with the values in my CSV, and this is done quite simply by dragging them from the bindings list and dropping them into the appropriate part of the message. The ‘First Name’ will be dragged from bindings into the ‘Given Name’ field in the message template, the ‘Last Name’ will be dragged into the ‘Surname’ field, and I do not have any of the other records, so I will truncate this down to make it simpler. There is a date of birth field, so this will be dropped in, and this needs to be converted to an HL7 date format (HL7 Date) as there is no need for the time of birth in the messages. Then I have the sex or the gender, and this will be dragged into the message template and the same for address line 1. All of these other records are not required for this sample.
This has now been fully populated, and generally speaking, that is all I need to do to get this up and running, so I will test this now. I will hit save and close, and it has populated integration host, and it has started straight away, but now it has come up with an error. There is a log list of every message that has been processed by the Integration Host, so if this is expanded out, I can look at this and see what the error is. In this case, I could not deal with the CSV file as it was being locked by Excel. I will quickly close that to continue and clear the list and then restart the process. This time it has processed ten messages, and I can refresh my log list, and I can see it has been picking up the single lines of CSV one at a time, and it has been converting those into HL7 messages. It has populated the dates appropriately with the right format, and it has their name, their date of birth, their gender and their address, but looking at this, there are two problems with the date.
The first problem is that in HL7, the word ‘Male’ is not valid. There are a few recognisable errors if I look at this message, so I will copy this and load it up into HL7 to show you. Firstly ‘Male’ is not the appropriate value for the gender in an HL7 message. It should be one of the values in the lookup. Another error is the ‘Cnr High & Main’ as it contains an ampersand that is part of the HL7 message structure, so I have divided up ‘Cnr High & Main’ into two different fields, and it is not valid in HL7. To rectify this, I will have to escape the ampersand and replace the word ‘Male’ with the appropriate value inside HL7.
In Excel, I have a list of all the values that I would use inside my CSV file, and I have the list of the associated HL7 fields that I would like to use. I will create a lookup table inside of integration host that will automatically swap out the values from the CSV and replace them with the appropriate HL7 values. I will copy this list and use this in integration host and click on the HL7 Soup icon to bring up the menu, and I will go across to ‘Lookup Tables’. I do not have any at the moment, so I am just going to add a new one and call it gender. It then lists the values that it goes from and then the values it should be sent to. I will paste my Excel list into there, and here I can see all the values and how they should be mapped. There is one more that I want to deal with, and this is when I do not have a value, I will put that in as ‘U’ as I want this to be the unknown value. Now I can go back to our designer to look at the message. I am double-clicking on it, and it reloads that window and then head down to where it places it into the HL7 message and navigate down to ‘Gender’ and right-click on it and select ‘Truncate and Replace’. Then choose ‘Lookup Tables’, and inside here, I will see my new ‘Gender’ lookup table that I created. By clicking that, I have now been told to use that lookup table to replace the values. I also wanted to escape the values in ‘Address Line 1’, and to do that, all I have to do is right-click on ‘Address’ then go to ‘Encoding’ and select which type of HL7 encoding I want. Now, if the address is missing, it will put in a double quote and also, it is going to escape out that ampersand value with the appropriate HL7 escape characters. I will then save and close.
Now I will delete the files I created last time for testing in the ‘Out Directory’. I will then go up a directory to’ Backup Directory’ and copy it and go back and paste this in again, and it will be an always running process. I will then refresh the list, and the messages have been processed, and if I look this time, I will see that ‘Gender’ has been replaced with ‘Female’. On the other message, I can see that ‘Male’ has been replaced ‘M’, and I can see now that the address ‘Cnr High\T\Main that is the escape character for the ampersand in an HL7 message. Now I have got valid HL7 coming out whenever I drop a CSV file into my directory.
There are some great getting started videos to get you up and running with integration host and HL7 Soup available on the HL7 website under tutorials.
If you found this video helpful, please consider giving us a like or even subscribe to our channel. If you have any questions about getting going with HL7, please drop us a line at the HL7 Soup support site.