File to HL7 Conversion

[Jon Melin]

Hello,

I have a payment file that seems to have fixed length fields but also some strange formatting. I was looking to see people had for ideas to get this type of a format into HL7 format. Would most of you try an FRL?

I’ve also attached a test document for review that is identical to the real thing.

Not to complicate things more but eventually I will need to filter each block as well based on if they have comments or not (that line is blank or not).

Thank you,

Jon

Topic

[James Cobane]

Jon,

Nothing like having to take a textual report and create actual records; always a challenge.  I think you will need to do pre-processing on the file (via tcl) before attempting to apply a record layout.  You might be able to work with an HRL to get it to straight from the file into records/messages, but it would probably take some patience and a lot of tinkering.  From the looks of it, it seems that every ‘record’ is delineated with multiple asterisks, and that every data field is prefixed with its description.  You might be able to split the file into multiple records based on the ‘***….’, then parse the data based on the field descriptions/names, but it will require some tight coding to insure the data is mined correctly.  I would first push the provider of the data to give you something better to work with (i.e. an actual file of records instead of a report file).  Just my $.02

Jim Cobane

Henry Ford Health

[Jon Melin]

Jim,

Thank you for the response. I like your idea about splitting it up based on each *****… section. I’ve been trying to do it using “split” but I am not getting anything back. Any ideas on how you’d approach this?

Ultimately I would love it in a formatted file, I’ve asked them to see if this was possible but haven’t heard back.

Thank you,

Jon

[Jon Melin]

Jim,

I was also rummaging around looking for a solution to the problem using VRL. I’ve seen a previous post that is similar that you had replied on, but didn’t quite lead me to a solution. When I try to configure a VRL it’s able to get the first line of the ****… but the next line is white space so it seems to terminate and I can’t seem to get to the next line down that contains valid data. I am new to the configuration of VRLs since I haven’t had to use them in the past but how do I get it to skip the white space line and move to the next line?

I don’t see a setting in the VRL to automatically CR/LF on white space.

I’ve tried setting my global settings to x0d, but that didn’t seem to help either.

Thank you in advance,

Jon

[Rob Lindsey]

I agree with James, write a pre-processor in TCL.  If you are set on using a VRL you could remove all blank lines and just leave yourself the actual data and then break up the data via the “*******” lines.

I have done something like this in my distant past and did everything in a Inbound TCL Proc.  It was a pain.

Of course the real big thing is … what do you do if they add new data somewhere you do not expect it without telling you.  You need to be able to know this without having to check it.  So you might want to build in some “error” checking and if you get some extra data that the VRL or TCL proc is not expecting that it would give you some type of notice (i.e. like an email).

Just my $.02

Rob

[Scott Folley]

I am in agreement with everyone else here regarding the first step.  What you have is a header followed by vertical records.  Eliminate the header since that is of little or no use to you (you may need the name of the hospital for other purposes but you could grab that as you are going through).  First I would split on a newline and then (probably in a foreach) go through each line in the file.  Ignore everything until you get to one that begins with a series of asterisks.  Beyond that, ignore every line that trims out to be blank.  Split the lines that are not blank on the colon and do:

set label [ lindex $linedata 0 ]

set value [ lindex $linedata 1 ]

Then do:

set $label $value

This will give you a variable such as Date that would contain its value for the current record.  Any new fields that are added would fall into that same situation, you would just have a new variable.  Then you can set up a variable length record using a subst command.

This is obviously more pseudo code that actual code but, hopefully, you get the idea.

Have a great day!

[Jon Melin]

Thank you all the for suggestions. I will see what I can put together. I’ve done a decent amount of work in TCL, but nothing quite like this with having to ignore white space lines and splitting on new lines. I do understand the logic you are talking about, but the syntax is the issue.  

So you’d start with something like this:

set msg [msgget $mh]

set segments [split $msg r]

for each segments

until = *****

set label [ lindex $linedata 0 ]

set value [ lindex $linedata 1 ]

Something like that?

When I just split on the newline I end up getting **** by itself and it stops, must be due to the whitespace?

Thank you,

Jon

[Scott Folley]

Couple of things:

1.  A split on a newline would actually be “n” though splitting on “r” should work fine too.  The thing to also understand about split is that it works on ONE character only (you can’t have it split on “******”).  What I will often do in that case is a regsub to get a series of characters down to one (and I use a bell “b” as the character I replace it with because you never see that otherwise).

2.  The foreach looks more like this:

Code:

set segments [ split $msg “n” ]
foreach line $segments {
set line [ string trimright $line “r” ]  # Just in case the line termination is rn
if  { [ string range $line 1 5 ] eq “*****” } {
continue
# This will go to the next iteration
}
set linedata [ split $line “:” ]
set label [ lindex $linedata 0 ]
set value [ lindex $linedata 1 ]
}

Hope that is helpful.

[Jon Melin]

Yes, very much so. That is similar to what I was trying to project but I had forgot the each line. I will let you know what I come up with and post it if I get it working correctly.

I appreciate the help.

Thank you,

Jon

[Scott Folley]

One caveat, I wrote this from memory and I rarely code anymore so that “string range” command might be a tad off.  Other than that it looks right but your mileage may vary…LOL.

[Troy Morton]

One other thought.

[Jon Melin]

Great suggestions, I will definitely add some error checking once I have it working.

I’ve been working on trying to get this to work but I am still running into an issue where it only seems to see the ***** (first line) and stops after that. I’ve tried r n and xd but whatever I do it seems to do line 1, and stop.

Anyone have any suggestions?

Thank you,

Jon

[Jon Melin]

Hello,

I finally had a chance to take a look at your suggestion (Scott). I feel like it should be working but it appears to only ever do the first foreach (just the *****..). I’m using the TPS testing tool to test this and pointing it to a text document in the data folder (that I provided in a previous post), could that be why it’s not showing correctly?

I plan on manipulating everything and adding in some error checking and more robust features but I can’t get it to even display the values I am trying to obtain.

I’ve tried many different combinations of CR/LF (r, n, etc.)

Any other suggestions? I feel like I’m missing something simple here that’s right in front of my face.

Here is my output with echos in:

*************************************************************** – Display Line Item

Entered FOREACH

Entered IF

CONTINUE: ‘***************************************************************’

Thank you in advance,

Jon

Code:

proc separate_values2 { args } {
   keylget args MODE mode               ;# Fetch mode

   set dispList {} ;# Nothing to return

   switch -exact — $mode {
       start {
           # Perform special init functions
         # N.B.: there may or may not be a MSGID key in args
       }

       run {
 # ‘run’ mode always has a MSGID; fetch and process it
           keylget args MSGID mh
           set msg [msgget $mh]

set segments [split $msg n]
set count 0
echo “$segments – Display Line Item”

foreach line $segments {
echo “Entered FOREACH”
incr $count
set line [string trimright $line n]

if  {[ string range $line 1 5 ] eq “*****”} {
echo “Entered IF”

continue
}

echo “Obtain line values”
#split on : to separate label from value in each row
set linedata [ split $line “:” ]

#Obtain current label
set label [ lindex $linedata 0 ]
echo “$label – Current Label”

#Obtain current value
set value [ lindex $linedata 1 ]
echo “$value – Current Value”

}

lappend dispList “CONTINUE $mh”

       }
   shutdown {
       # new mode
   }

   default {
     error “Unknown mode ‘$mode’ in separate_values2”
       }
   }

   return $dispList
}

[Rob Lindsey]

If you are using the testing tool to test your proc be careful to choose the correct file ending.  If you have chosen newline then that is why you are only getting the “********” line.  The testing tool will just read up to the first newline character in the data file.  If you choose EOF then it should read in the entire file and it should work with the split on the newline.

Rob

[Charlie Bursell]

I guess I will throw my $0.02 worth in and that is probably overvalued  😀

The problem with regexp here is how do you get what is in between the sets of **************** without using look-ahead which is incredibly slow.

I would certainly not use FRL but VRL is a viable option.  This code assumes you have created a VRL for each field in a record separated by “|”

It assumes the whole file is read into a variable name inp.  I am not married to any of this so change or discard at will.

set vrlBuf “”

foreach rec [split $inp n] {

   # Make sure no leading lagging spaces

   set rec [string trim $rec]

   # Each set of ***** add previous, if there, to buffer

   if {[regexp — {^*} $rec]} {

       # Store buffer if exists

       if {[info exists vrl]} { lappend vrlBuf [join $vrl |] }

       # Create or clear record buffer then ignore line

       set vrl “”

       continue

   } elseif {![info exists vrl]} {

       # Ignore all before first set of records

       continue

   }

   # No blank lines

   if {$rec eq “”} {continue}

   # Split each record and store into array

   lappend vrl [string trim [lindex [split $rec :] 1]]

}

Now you have a buffer of VRL records with fields separated by |

From this point you could make it new-line delimited and write it to a file like:  write_file myfile [join $vrlBuf]

or process here like:  foreach rec $vrlBuf {do something}

[Jon Melin]

Charlie,

Finally had some time to work on this. I like this approach as well. Thank you for the input. I used what you listed here but I’m still not quite getting what I want. I see it in the buffer but it seems to be one large string.

I’ve added a for each thinking I could send each one out with continue but it seems to want to send the whole buffer out. Can you lead me in the correct direction?

I think my ultimate goal is to use a route to consume the file. Then I think I’ll either pass it through another TCL building an HL7 from the pipe delimiters or pass it into an xlate so I can do more filtering / translation to the data. I leaning towards the latter because I’m going to be doing a lot of manipulation based on triggers.

I’m not sure about the last part you are doing to append the values to an array VRL, It seemed to give me one value and a time, and I couldn’t get the file writer to work correctly. Where is the default creation location, the site root?

Here is the output I get:

{20140802||POS Payment – Not Requested|XXX-XXXXX|00.00|TEST PERSON|Visa – XXXXXXXXXXXXXXXX|06/2015|N/A|XXXXXXXXX Approved XXXXXXXXXX|08/02/2014|TESTDUDEL|HHHH XXXX-XXX-X

[Charlie Bursell]

First, the buffer inp is alreadt a list do you don’t meed the code: foreach rec [split $inp n]

All you need is: foreach rec $inp

Next you are dong a msgset with each record which would overwrite.

Before the loop:  set dispList

Change to:

set nmh [msgcopy $mh]

msgset $nmh $rec

lappend dispList “CONTINUE $nmh”

After the loop simply return $dispList

I did not evaluate all the code so there may be something else

[Jon Melin]

Charlie,

Your suggestion seems to send a line at a time based on each line of the document. I was looking for almost what I had with my previous configuration (with your help) where I used the vrlbuff and appended everything so I get each ****** section separated by pipes and sent. I’d like each section to go into an Xlate (or a file where I’ll run another TCL) so I can manipulate it further. So similar to the output format my last solution did but without it being one large line in { } – If I could get this (below) to be a line each, without the { } surrounding the set I would be a happy person. Maybe I’m making it more difficult trying to get it in that format though.

{20140802||POS Payment – Not Requested|XXX-XXXXX|00.00|TEST PERSON|Visa – XXXXXXXXXXXXXXXX|06/2015|N/A|XXXXXXXXX Approved XXXXXXXXXX|08/02/2014|TESTDUDEL|HHHH XXXX-XXX-X  8/2/14|Clinic||TEST PERSON} {20140802||POS Payment – Not Requested|XXX-XXXXX|00.00|TEST PERSON|Visa – XXXXXXXXXXXXXXXX|01/2016|N/A|XXXXXXXXXXXXXX Approved XXXXXXXXXX|08/02/2014|TESTDUDEL|XXX XXXX-XXX-X  8/2/14|Clinic||TEST PERSONTWO}

When I made the edits you suggested it seemed to send each line separately.

CONTINUE: ‘Date:                       20140802’

CONTINUE: ‘Guarantor Number:’

CONTINUE: ‘Guarantor SSN:              POS Payment – Not Requested’

CONTINUE: ‘PatientAccounts:            XXX-XXXXX’

CONTINUE: ‘Total Amount Paid:          00.00’

CONTINUE: ‘Name on Credit Card:        TEST PERSON’

CONTINUE: ‘Card Number:                Visa – XXXXXXXXXXXXXXXX’

CONTINUE: ‘Expiration:                 06/2015’

CONTINUE: ‘Facility:                   N/A’

CONTINUE: ‘Web Tracking Number:        XXXXXXXXX Approved XXXXXXXXXX’

CONTINUE: ‘Service Date:               08/02/2014’

CONTINUE: ‘Entered By:                 TESTDUDEL’

CONTINUE: ‘Comments:                   HHHH XXXX-XXX-X  8/2/14’

CONTINUE: ‘Service Type:               Clinic’

CONTINUE: ‘E-mail Address:’

CONTINUE: ‘Patient Name:               TEST PERSON’

Here is what I did with your suggestions, maybe I put it together wrong:

Code:

proc create_formatted_file2 { args } {
   keylget args MODE mode               ;# Fetch mode

   set dispList {} ;# Nothing to return

   switch -exact — $mode {
       start {
           # Perform special init functions
         # N.B.: there may or may not be a MSGID key in args
       }

       run {
 # ‘run’ mode always has a MSGID; fetch and process it
           keylget args MSGID mh

  set vrlBuf “”
set dispList [list “KILL $mh”]
set inp [msgget $mh]

    foreach rec [split $inp n] {

#echo “$rec REC”

   # Make sure no leading lagging spaces
   set rec [string trim $rec]

   # Each set of ***** add previous, if there, to buffer
   if {[regexp — {^*} $rec]} {

       # Store buffer if exists
       if {[info exists vrl]} { lappend vrlBuf [join $vrl |] }

#set nmh [msgcopy $mh]
#msgset $nmh $rec
#lappend dispList “CONTINUE $nmh”

       # Create or clear record buffer then ignore line
       set vrl “”
       continue
   } elseif {![info exists vrl]} {
       # Ignore all before first set of records
       continue
   }

   # No blank lines
   if {$rec eq “”} {continue}

   # Split each record and store into array
   lappend vrl [string trim [lindex [split $rec :] 1]]

set nmh [msgcopy $mh]
msgset $nmh $rec
lappend dispList “CONTINUE $nmh”

}

       }
   shutdown {
       # new mode
   }

   default {
     error “Unknown mode ‘$mode’ in create_formatted_file2”
       }
   }

   return $dispList
}

[Jon Melin]

Charlie,

I think I am thinking about this wrong.

What I have is working but when you said:

“Now you have a buffer of VRL records with fields separated by |

From this point you could make it new-line delimited and write it to a file like:  write_file myfile [join $vrlBuf]

or process here like:  foreach rec $vrlBuf {do something}”

How would I make each $vrlBuf end up on a new line? Whenever I try it splits it before or sends the appended list (with all the records)

To elaborate, I know when you return dispList the tcl is finished but essentially all I would like to add is for each vrlBuf to send separately. If I do this loop below I get the first one separately but the TCL is finish, how do I make it send each one without terminating. I know what I’m trying to do logically I just can’t get the syntax to work.

Code:

  foreach rec $vrlBuf {
  msgset $mh $vrlBuf
  lappend dispList “CONTINUE $mh”
  return $dispList }

[Charlie Bursell]

Evidently you did not raed my last response.  You create a separate message handle for each and return $dispList *AFTER* the loop

set dispList

foreach rec $inp{

   set nmh [msgcopy $mh]

  msgset $nmh $rec

  lappend dispList “CONTINUE $nmh”

}

return $dispList

This assumes $inp is already a list which appears to be the fact based on your echo results

[Jon Melin]

Charlie,

My apologies if you thought I didn’t read your last post. I did in fact read it I was just trying alternative ways of finding my solution when I couldn’t get it working. I did try the $inp without the split and it doesn’t return any data (I believe because the inbound unformatted text), and I have been returning dispList after the loop at the end every time. Where in the previous code are you saying I should place this inside of the for loop?

set nmg [msgcopy $mh]

msgset $nmh $rec

lappend dispList “CONTINUE $nmh”

Within the vrlBuf loop or outside of that? I’d still need the previous code to help split after the : and add to the buff. Does this look like what you are suggesting?

Code:

proc create_formatted_file2 { args } {
   keylget args MODE mode               ;# Fetch mode

   set dispList {} ;# Nothing to return

   switch -exact — $mode {
       start {
           # Perform special init functions
         # N.B.: there may or may not be a MSGID key in args
       }

       run {
 # ‘run’ mode always has a MSGID; fetch and process it
           keylget args MSGID mh

  set vrlBuf “”
set dispList [list “KILL $mh”]
set inp [msgget $mh]

   foreach rec $inp {

   # Make sure no leading lagging spaces
   set rec [string trim $rec]

   # Each set of ***** add previous, if there, to buffer
   if {[regexp — {^*} $rec]} {

       # Store buffer if exists
       if {[info exists vrl]} { lappend vrlBuf [join $vrl |] }

set nmh [msgcopy $mh]
  msgset $nmh $rec
  lappend dispList “CONTINUE $nmh”

       # Create or clear record buffer then ignore line
       set vrl “”
       continue

   } elseif {![info exists vrl]} {
       # Ignore all before first set of records
       continue
   }

   # No blank lines
   if {$rec eq “”} {continue}

   # Split each record and store into array
   lappend vrl [string trim [lindex [split $rec :] 1]]

}

  [color=orange]   return $dispList[/color]
       }
   shutdown {
       # new mode
   }

   default {
     error “Unknown mode ‘$mode’ in create_formatted_file2”
       }
   }

  #return $dispList
}

This is the result I get

CONTINUE: ‘***************************************************************’

CONTINUE: ‘***************************************************************’

CONTINUE: ‘***************************************************************

Ultimately If I use vrlBuf rather than rec in that loop I get closer to what I need it just comes in one large list again separated by { } { } which is fine but I need them on separate lines if I am to use them correctly.

Thank you again,

Jon

[Jon Melin]

Charlie,

I believe I have figured out a solution. You’ve been a huge help and I appreciate the assistance. I will keep everyone updated on the progress.

Thank you everyone for your advice.

Thank you,

Jon

[Author]

Replies