Hi guys. I'm thinking about building a class that can parse HL7 messages quickly and easily. If you don't know what HL7 is all about, basically it is an industry standard format for passing healthcare information from system to system.

An example of what a message may look like is:

MSH|^~\&|||||20120405084553|RISTECH|DFT^P03|2686|T|2.6|||||||||

EVN|P03|20120405084553|||RISTECH^RADIOLOGY^TECHNOLOGIST^^^^^^UCH^^^^^|

PID|1||E5876^^^^EPI~06207154^^^MRN^MRN||EPICTEST^UHNUCMEDICINEMRP||19820305|F||D
|1979 MILKY WAY^^VERONA^WI^53593^USA^P^^DANE|DANE|(608)271-9000^P^PH||EN|S|NOT|2000001890|280700154|||D||||||||N

PV1|1|O|UNUCMED^^^UHMR^^^^^^^DEPID||||u90194^UCHRADIOLOGIST^NUCMED^^^^^^EPIC^^^^
PROVID||||||||||||2000001890|SELF||||||||||||||||||||||^^^UHMR^^^^^^^||201204050
8
24||||||

FT1||55427||20120405|20120405|CG||||1|||UNUCMED^UH NUC MEDICINE MRP^DEPID|||^^^101007|||136.9^Unspecified infectious and parasitic diseases^ICD-9|u90194^UCHRADIOLOGIST^NUCMED^^^^^^EPIC^^^^PROVID|u90194^UCHRADIOLOGIST^NUCMED^
^^^^^EPIC^^^^PROVID||694908^55427^||78300^CHG BONE IMAGING, LIMITED AREA^C4|26^PROFESSIONAL COMPONENT^C4|||

Sorry about the formatting (the forum kind of munged it).

Anyway, it consists of segments, with each segment broken down into different fields and sub-fields based on a set of delimiters defined in the first segment (MSH). Right after the MSH you can see the delimiters of |^~\& . What that means is that each field can contain optional subfields and sub-subfields and sub-sub-subfields!!

What I want to do is read in the message, turn it into a class object, and then be able to refer to the elements easily, rather than have to parse out each component each time I want it.

In the end, I want to be able to refer to the different parts like this:

msg.PID.3.1.1

This would refer to the "PID" segment, the 3rd field, and the 1st sub-field within that field, the first component, ... so it would be "E5876" in the above example.

Wait a minute, ... before you get your hopes up too high, ... it gets better. <sarcasm>(Don't you just LOVE HL7!!)</sarcasm>

Not only can fields repeat, entire segments can repeat, so a message may contain 20 FT1 segments, each of them containing a distinct financial transaction (in case you're wondering what FT1 stood for. (IMG:style_emoticons/default/cool.gif) )

So, additionally, I want to optionally refer to fields with this format:

msg.FT1(0).2.1 (for the 1st FT1 segment)

or

msg.FT1(2).2.1 (for the 3rd FT1 segment)

... and the same thing for the subfields ...

msg.PID.3.1(1)

Kind of like referring to an element in a zero-based array, ... but for each component within the structure.

Sounds like fun, huh?? (IMG:style_emoticons/default/dazed.gif)

Anyway, I'd like to hear a little input from others as to the best way to accomplish this. Ideas are welcome. I'm not asking for someone to write a solution, but I'm more interested in high-level approaches. I have some ideas myself, but am not sure that it's the best way.

When it's all said and done, it will basically be a fully addressable, in-memory tree-view, that can be passed around as an object, ... if that makes it easier to conceptualize. (IMG:style_emoticons/default/big_grin.gif)

are you thinking of splitting and using the array? Most likely having to split more than once.

That would certainly be part of it, as far as the parsing goes, but then how to store it in memory, ... in Arrays? in Collections? in Strings? in Recordsets?

And then how to retrieve it based on the above described convention.

now you are getting outside what little comfort zone i have.

i might see if i could dynamically assign a variable based on the array element.

if i know the 3rd element of the array is PID and then use the sub array element numbers as variables as well, I would imagine you could get the value for pid.3.1.2

Yes, but the third element isn't always going to be PID.

Here's another more complex example of a message. This one contains insurance information:

MSH|^~\&|EPIC||ENSEMBLE||20120314135748|EDIREGO|ADT^A08|30722|P|2.5|||||||||
EVN|A08|20120314135748|||^INTERFACE^REGISTRATION^OUT^^^^^UCH^^^^^|
PID|1||00105509^^^MRN^MRN||TWINKIE^HOHO^A||19620712|F|HOHO^TWINKIE^Z^~TWINKIE^HO
HO^A^~TWINKIE^HOHO^Z^|O|123 WHOVILLE^^CINCINNATI^OH^45229^USA^P^^HAM|HAM|(513)555-4521^P^PH^^^513^5554521||UN|M||29941|000000000|||H||||||||N
PD1|||EHS MODEL CLINIC^^10501|142043^PRANIKOFF^JOEL^^^^^^EPIC^^^^PROVID||||||||||
NK1|1|CONTACT^NO^^||^^^^^USA|(000)000-0000^^^^^000^0000000||Emergency Contact 1|||||||||||||||||||||||||||
PV1|1|I|U9CP^9024^U9024^UHMH^R^^^^^^DEPID|UR|||140372^CARDI^MICHAEL^^^^^^EPIC^^^
^PROVID|||MED||||2|||140372^CARDI^MICHAEL^^^^^^EPIC^^^^PROVID||29941|COMM||||||||
||||||||||||||^^^UHMH^^^^^^^||201203102002|||4720|||
PV2||S||||||201203102002|||||||||||||n|N|||||||||||||||||||||||||||
ZPV|||||||||||||||
AL1|1|DRUG INGREDI|31617^YELLOW DYE^^^||N&V|20111230|||Allergy
AL1|2|DRUG|55715^SALICYLIC ACID IN COLLODION^^^|Intolerance|Hives~Dermatitis|20120106|||Allergy
AL1|3|DRUG INGREDI|32877^FACTOR IX COMPLEX (PCC) COMB.1^^^||Anxiety|20120111|||Intolerance
AL1|4|Drug Class|30472^D\T\C RED NO.27^^^|Intolerance|Dermatitis~TINITUS|20120111|||Unspecified
GT1|1|651|TWINKIE^HOHO^Z^||123 WHOVILLE^^CINCINNATI^OH^45229^USA^^^HAM|(513)555-4521^^^^^513^5554521||19620712|F|P/F|SLF|000000000|||||^^^^^USA|||None|||||||||||||||||||||||||||||
IN1|1|100101^AETNA HMO|1001|AETNA||||GR2458||||20100101||||TWINKIE^HOHO^Z^|Self|19620712|123 WHOVILLE^^CINCINNATI^OH^45229^USA^^^HAM|||1*|||YES|||||||||||12475||||||||^^^^^U
SA|||BOTH||
IN2||000000000|||Payor Plan|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
IN1|2|100202^HUMANA PREFERRED|1002|HUMANA||||GR4578||||20100101||||TWINKIE^HOHO^Z^|Self|19620712|123 WHOVILLE^^CINCINNATI^OH^45229^USA^^^HAM|||2*|||YES|||||||||||45874||||||||^^^^^U
SA|||BOTH||
IN2||000000000|||Payor Plan|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
ZMP|1|||||N||||^^^^|||^^^^|||||^^^^||||^^^^||2||||^^^^|||||^^^^||||^^^^|||||||||
|||||||||

2 questions:

1) wouldn't it be simpler to transform H7 into a set of temporary tables so you could then use SQL/Recordsets to query, filter and extract data?
2) Similarly, since you need to be able to find a piece of something and the semantics you've described is much more like XML than SQL, wouldn't it be better to transform the H7 into a XML document where you could then use XQuery to get whatever you need quickly and easily? Given that H7 is an industry standard, I'd be surprised if nobody already wrote a H7 -> XML converter. EDIT: Indeed, someone did!

Not that it is cool to invent something of your own, sometime it's better to find wheels lying around. (IMG:style_emoticons/default/smile.gif)

I think it was made to be cryptic to preserve the confidentiality of the information from prying eyes.

Yes i know. I was suggesting that you use the array element to get the name

when i look at your post I am guessing that the first array would have element 0 as MSH and then EVN and PID

then you can count how many are in the array and then get the value for it. Then using that value do another split where you would produce the subarray. Don't know if you need to drill down further with another split or not.

I doubt that's the intention. If it was really meant to be cryptic, it would have been encrypted. Right now, it's merely obscure and obscurity is not security. Apparently HL7 was invented long before there was anything like XML or similar standards.

RE: XML

Someone else mentioned that this may be a good fit. I have very little experience with XML. However, the class in your link is written in C# (I think), and I would like this to be accessible from VBA.

The way it works is these messages are sent via LLP to a HL7 engine that listens for them all day long. The engine then dumps the messages real-time into a SQL Server table for me to parse, reformat, and resend to others.

I'm up for exploring the XML route, but it would be a learning curve.

RE: Temp tables

I think this would get expensive rather quickly as I have thousands of messages a day to parse and re-route.

But like I said, I'm exploring all avenues right now.

Yes, it's in C# but it can be accessible to VBA w/ COM Interop. I've used a C# DLL for one of my Access project, with early binding and it worked out pretty good. If you're up for the learning, I made a demo about consuming .NET DLL years ago that may help get you started.

I would like to learn how to do that ... if you have a link to your demo, I'll check it out.

Let's just say that everything's wired up and now I have my message in XML format.

How would I address parts of it like I'd like to in the format mentioned above?

Don't have much time right now; want to get into this for sure but hopefully those two links will give you the idea of XPath's capability:

Using XPath with MSXML (for VBA syntax)
XPath syntax

and the demo:
Consuming .NET components within Access VBA

Walter,
if you have SQL Server already, you can use XQuery to parse the HL7 data (assuming it's well-formed XML). If this is something you need to do a lot of, and you already have SQL Server, it might be worth your time to check out Andy Leonard's website. (He's a SQL Server MVP that specializes in SSIS.) Sorry to sound like our old friend Aaron! This could be a super handy thread for me, because I know someone who is doing HL7 parsing right now... (Interviewed with him last week...)

Pieter

Poked around a little at XML ... I'm not sure that it will meet my needs for now, but am not writing it off completely.

I would like to see if I can get this part of the initial question addressed:

Is there a way that I can reference a variable like:

msg.FT1(0).2.1

...within Access VBA?

What kind of objects would need to be set up? ...or is it even possible at all?

Would this work?



or



To achieve the precise syntax asked:


would require that you create an object for each part, and probably add a default property referencing to another object.

e.g.

A class module named MyObject:


with a manual hand editing of the .cls (via export) to specify the Item property as a default property by seeing MemUserID = 0 (this is off my head and I may be misremembering the spelling here) and reimporting the modified .cls file back.

The problem with the first two syntaxes is that there may be, for example, multiple FT1 segments.

I need an easy way to say, "Return the 2nd repetition of the 5th element in the 3rd FT1 segment."

The message may be something like:

...
FT1|||||abc^123
FT1|||||def^456
FT1|||||ghi^789
FT1|||||xyz
....

...and I need "789".

Ideally, the first element would be implied if no index was passed.

So for:

msg.FT1(0).2.1

This would really be understood to be:

msg.FT1(0).2(0).1

The reason the last (0) is left off is because it should assume the entire component of the last part asked for, even if there were further repetitions within it.

That's why I suggested a class with a member of the same type, so as a part of parsing the HL7, you can instantiate a new object add to the parent's member and repeat the process as needed. This would then give you an unlimited depth after the parsing, and using VBA.Collection for the members of the same level, you can access the properties and descend a level via the object member.

The downside? LOT and LOT of memory....

NOTE: for a more complete example of an object containing a member of the same type, Linked List may help illustrate the idea.

I should also point out that you can add a VBA.Collection to a VBA.Collection which would give you basically the same result.