Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


Excel VBA HL7 Parser

Posted on 2012-03-21
Medium Priority
Last Modified: 2016-02-11
I am trying to write a crude parser for HL7 for our internal testing.

What happens is the user pastes the HL7 message in the Data Sheet.
Each segment is preceded by a set of identifiers like MSH, PID and many more (which are standard and do not change). There are sheets created for each of these segments.
In each segment, all fields are SEPARATED by a "|" (pipe).

Upon click of a button or as soon as the message is pasted, I would like to split each of the segment and paste the data (pipe separated) into the corresponding sheets. I am not sure if we can use VBA / Macros to accomplish the task.

Sample Hl7 Message.

PID||123456|123456^^^^MR||Test^Sam^||20100101||||M||1002-5^American Indian or Alaska Native^HL70005|2 Dr^^Houston^TX^01321|US|332-508-5080|||Single|||999999999|||2135-2^Hispanic or Latino|||||||||||||||||

Attached is the excel file containing the prototype and how the fields should be parsed.

Any help is appreciated.
Question by:nainil
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3

Expert Comment

by:Frank White
ID: 37747183
Perhaps it would help if you read: this page on the Split() function and an enhanced version for parsing.

The example function lets you split a string with a specific delimiter.

After that, the most obvious solution is to loop through the resulting array, comparing for your list of keywords and creating a new sheet if one is found, and then sending each element of the array to the right cell, also setting the current keyword in the first column and the value of an iteration counter for the second column (going by what I see ni the example workbook you posted).

If you want "empty fields" (e.g. |20110802122112||VXU| being three fields, with an empty one in the middle encased by two pipe characters) to also be counted, that makes the code even lighter.

Bit short on free time, so I won't be providing sample code right away, but if this question's still open in a few hours I'd be glad to give it a shot.

EDIT: If your pasted messages are always going to be HL7-compliant, then they're always going to have a newline between each segment, right? If so, you could run a first split pass using the newline character(s) as the delimiter, and then each element of that split array would be one segment, which can be assigned its own worksheet and *then* split and blasted. This would further simplify resulting code.

Accepted Solution

Frank White earned 400 total points
ID: 37748935
Alright, I think I've got something that works.

The code below below should be put in a VBA module in the workbook.

Option Explicit


Sub DoHL7Parsing(sMessage As String)
    Dim vSegments As Variant, vCurSeg As Variant
    Dim vFields As Variant, rCurField As Range, iIter As Integer
    Dim wsSeg As Worksheet
    vSegments = VBA.Split(sMessage, HL7_DELIMITER_SEGMENT)
    For Each vCurSeg In vSegments
        vFields = VBA.Split(vCurSeg, HL7_DELIMITER_FIELD)
        If WorksheetExists(vFields(0), ThisWorkbook) Then
            For iIter = 1 To UBound(vFields)
                Set rCurField = ThisWorkbook.Worksheets(vFields(0)).Range("A65536").End(xlUp).Offset(1, 0)
                rCurField.Value = vFields(0)
                rCurField.Offset(0, 1).Value = (rCurField.Row - 1)
                rCurField.Offset(0, 2).NumberFormat = "@"
                rCurField.Offset(0, 2).Value = vFields(iIter)
            Next iIter
            MsgBox "Invalid or unkown segment: " & vFields(0)
        End If
    Next vCurSeg
End Sub

Public Function WorksheetExists(ByVal WorksheetName As String, Optional InWorkbook As Workbook) As Boolean
    Dim Sht As Worksheet
    WorksheetExists = False
    If Not InWorkbook Is Nothing Then
        For Each Sht In InWorkbook.Worksheets
            If Sht.Name = WorksheetName Then WorksheetExists = True
        Next Sht
        For Each Sht In ActiveWorkbook.Worksheets
            If Sht.Name = WorksheetName Then WorksheetExists = True
        Next Sht
    End If
End Function

Open in new window

To use it, add this code to the Data sheet's VBA code (right click tab -> View Code):
Private Sub Worksheet_Change(ByVal Target As Range)
    If Not (Application.Intersect(Target, [PasteField]) Is Nothing) Then
        DoHL7Parsing [PasteField].Value
    End If
End Sub

Open in new window

Each time the cell is changed, its contents will be parsed and distributed to the relevant worksheets. The sheets have to already exist and have the same exact name as the segment name, and the segment name must be the first field in a newline segment. I'm assuming that this is already how HL7 standards work (based on what little I've read of them), which is why I felt unnecessary to make more code to handle different situations.

If some of my assumptions were incorrect or you want to have another method to decide when to parse the message, don't hesitate to say so.

Also, here's the code put into the prototype book, though since I'm using Excel 2002 at the moment it's not guaranteed to open properly. I've had to change it to a macro-enabled format for it to save right in the first place:


Author Comment

ID: 37796674
Apologies for getting back so late. This works perfectly. I am only concerned about one thing: Carriage Returns. There will be times when there will be no carriage returns available in the message. How or what can we do about that?

Expert Comment

by:Frank White
ID: 37798556
Well, if you're talking about the Carriage Return character specifically ("CR", or vbCR in VBA constants), no issues there, since this code detects the Line Feed character (which is after the Carriage Return, either way).

If you're talking about newlines in general, if there's going to be a replacement character for newlines, you can change the line "Const HL7_DELIMITER_SEGMENT = vbLf", changing vbLF for whichever character will act as separation between segments.

If there's going to be no actual replacement and the only thing between segments will be another pipe character (|), then the line "vSegments = VBA.Split(sMessage, HL7_DELIMITER_SEGMENT)" will have to be replaced with something more complex that draws from a list of segment IDs and separates the string each time it encounters one.

An alternative to that would be to have another sub written and run before this one that would do the same thing and replace detected identifiers with 'vbCRLF & identifier' before running this sub.

Expert Comment

ID: 40684903
This VBA script is great! It's going to save me so much time. I realize this is a fairly old thread, but I'm hoping you all can help me.

Often I am working with HL7 messages with multiple segments of the same type. Is it possible to create a new tab for each new line, instead of looking for a tab that already has the same name?
For example, here is one with two OBX segments:
PID|1|14321076|14321076||Test^MEGAN||19880921|F|||7546 Old Spec Rd^^Peyton^CO^80831||6515834017||||||452111111|
OBR|1|142153|MOL14-010125|3020^CT/GC Combo|||20141117144800||||||||||||||||||F||||
OBX|1|ST|3023^Chlamydia trachomatis||Negative||Negative|
OBX|2|ST|3026^Neisseria gonorrhoeae||Negative||Negative|

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are times when I have encountered the need to decompress a response from a PHP request. This is how it's done, but you must have control of the request and you can set the Accept-Encoding header.
Q&A with Course Creator, Mark Lassoff, on the importance of HTML5 in the career of a modern-day developer.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

604 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question