Solved

Excel VBA HL7 Parser

Posted on 2012-03-21
5
2,046 Views
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.

MSH|^~\&|eCW|WestBorough|DoH|MA|20110802122112||VXU|20110802122112|P|2.3.1
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.
Hl7-Parser.xlsx
0
Comment
Question by:nainil
  • 3
5 Comments
 
LVL 3

Expert Comment

by:DaFranker
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.
0
 
LVL 3

Accepted Solution

by:
DaFranker earned 100 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

Const HL7_DELIMITER_FIELD = "|"
Const HL7_DELIMITER_SEGMENT = vbLf

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
        Else
            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
    Else
        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:

Hl7-Parser.xlsm
0
 

Author Comment

by:nainil
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?
0
 
LVL 3

Expert Comment

by:DaFranker
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.
0
 

Expert Comment

by:rcarp42
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:
MSH|^~\&|Test|Test|Test|Test|20141119154841||ORU^R01|20141119154841|P|2.3|
PID|1|14321076|14321076||Test^MEGAN||19880921|F|||7546 Old Spec Rd^^Peyton^CO^80831||6515834017||||||452111111|
PV1|1||||||123456789^Test^Peter|
ORC|1|142153|MOL14-010125|||||||||123456789^Test^Peter|
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|
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

A short article about problems I had with the new location API and permissions in Marshmallow
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

757 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now