Process text file

[twilkins]

I need to process a text file to extract (example below) patient name, medical record number (MRN), DOB (convert DOB to age), and gender. The file will have a variable amount of patients per day. I need to then paste these variables into a new text file. Any suggestions?

 

As of: 09/14/09 08:22 Page 1

 

S C H E D U L I N G M A N A G E M E N T

Non-worksheet Daily Summary

 

Resource: DOC

Date: 09/14/09

 

Time Dur Appointment Type State Requester

 

09:00 20 RETURN PATIENT VISIT Confirmed

Person: DOE, JOHN

Home Phone: (706) 788-8888 MRN: 001234567 DOB: 08/09/1965 Gender: Male

Reason: HEALTH MAINT

09:15 20 RETURN PATIENT VISIT Confirmed

Person: DOE, JANE

Home Phone: (803) 688-8888 MRN: 007654321 DOB: 12/22/1954 Gender: Female

Reason: health maint

09:20 20 RETURN PATIENT VISIT Confirmed

Person: DOE, BROTHER

Home Phone: (803) 666-6666 MRN: 001111111 DOB: 09/04/2007 Gender: Male

Reason: 6 MOS CHECK

09:30 20 RETURN PATIENT VISIT Confirmed

Person: DOE, SISTER

Home Phone: (706) 999-9999 MRN: 002222222 DOB: 09/27/2008 Gender: Female

Reason: HEALTH MAINT

*** End of 09/14/09 appointments ***

 

* ** E N D O F R E P O R T * * *

[stevecasper]

Most of this will be very simple to do using either a Text File Process command loop, or even a basic set of Copy/Delete Text File commands.

 

The hardest part is going to be converting DOB to actual age.

 

I'll take a stab at building a macro that does what I think you are trying to do. Even if mine isn't exact you ought to be able to extrapolate just exactly how to do it right based on picking my macro apart and... um... familiarizing yourself with the sample macros that come with Macro Express, as well as reading the help pages. Ahem. Hint-hint.

 

Give me a while to work on it, though. I'm at work, and they prefer when I do what I get paid to do.

[stevecasper]

Ok, this will do everything you specified, with the exception of calculating the patient's age. That kind of scripting is way too complex for me

to try to do while working (and I'm not 100% convinced it's even possible with MEP right now). If it is, it's way beyond my comprehension at

this point.

 

Anyway, you'll obviously want to change the location of the source file (this macro points to a .txt file I saved to my desk top), and the location

of the results file (again, I pointed it at my desk top.

 

You'll make those changes on the Text File Begin Process command and on the very very last command: Variable Modify String

 

Any other changes you may need to make for your specific needs will be between the "Begin Process" and "End Process" commands, more than likely.

 

<COMMENT Value="Create a carriage return variable"/>
<VARIABLE SET TO ASCII CHAR Value="13" Destination="%CRLF%"/>
<VARIABLE SET TO ASCII CHAR Value="10" Destination="%T[1]%"/>
<VARIABLE MODIFY STRING Option="\x07" Destination="%CRLF%" Variable="%T[1]%"/>
<TEXT FILE BEGIN PROCESS Filename="C:\\Documents and Settings\\z051940\\Desktop\\medrecord.txt" Start_Record="1" Process_All="TRUE" Records="1" Variable="%FileProcess[1]%"/>
<IF VARIABLE Variable="%FileProcess[1]%" Condition="\x06" Value="Person" IgnoreCase="FALSE"/>
<VARIABLE MODIFY STRING Option="\x0F" Destination="%FileProcess[1]%" ToReplace="Person: " All="FALSE" IgnoreCase="FALSE"/>
<VARIABLE MODIFY STRING Option="\x00" Destination="%FileProcess[1]%"/>
<VARIABLE MODIFY STRING Option="\x07" Destination="%NewMedRecord%" Variable="%FileProcess[1]%%CRLF%"/>
<END IF/>
<IF VARIABLE Variable="%FileProcess[1]%" Condition="\x06" Value="MRN" IgnoreCase="FALSE"/>
<COMMENT Value="Set MRN"/>
<VARIABLE SET INTEGER Option="\x0E" Destination="%Position[1]%" Text_Variable="%FileProcess[1]%" Text=" MRN" Ignore_Case="FALSE"/>
<VARIABLE MODIFY STRING Option="\x0A" Destination="%FileProcess[1]%" Start="1" Count="%Position[1]%"/>
<VARIABLE MODIFY STRING Option="\x0F" Destination="%FileProcess[1]%" ToReplace="MRN:" All="FALSE" IgnoreCase="FALSE"/>
<VARIABLE SET INTEGER Option="\x0E" Destination="%Position[2]%" Text_Variable="%FileProcess[1]%" Text=" DOB" Ignore_Case="FALSE"/>
<VARIABLE MODIFY STRING Option="\x09" Destination="%MRN%" Variable="%FileProcess[1]%" Start="1" Count="%Position[2]%"/>
<VARIABLE MODIFY STRING Option="\x00" Destination="%MRN%"/>
<COMMENT Value="Set DOB"/>
<VARIABLE MODIFY STRING Option="\x0A" Destination="%FileProcess[1]%" Start="1" Count="%Position[2]%"/>
<VARIABLE MODIFY STRING Option="\x0F" Destination="%FileProcess[1]%" ToReplace="DOB:" All="FALSE" IgnoreCase="FALSE"/>
<VARIABLE MODIFY STRING Option="\x00" Destination="%FileProcess[1]%"/>
<VARIABLE MODIFY STRING Option="\x09" Destination="%DOB%" Variable="%FileProcess[1]%" Start="1" Count="10"/>
<VARIABLE MODIFY STRING Option="\x00" Destination="%DOB%"/>
<COMMENT Value="Set Gender"/>
<VARIABLE SET INTEGER Option="\x0E" Destination="%Position[3]%" Text_Variable="%FileProcess[1]%" Text=" Gender" Ignore_Case="FALSE"/>
<VARIABLE MODIFY STRING Option="\x0A" Destination="%FileProcess[1]%" Start="1" Count="%Position[3]%"/>
<VARIABLE MODIFY STRING Option="\x0F" Destination="%FileProcess[1]%" ToReplace="Gender:" All="FALSE" IgnoreCase="FALSE"/>
<VARIABLE MODIFY STRING Option="\x00" Destination="%FileProcess[1]%"/>
<VARIABLE SET STRING Option="\x00" Destination="%Gender%" Value="%FileProcess[1]%"/>
<VARIABLE MODIFY STRING Option="\x07" Destination="%NewMedRecord%" Variable="%MRN%"/>
<VARIABLE MODIFY STRING Option="\x07" Destination="%NewMedRecord%" Variable="%CRLF%"/>
<VARIABLE MODIFY STRING Option="\x07" Destination="%NewMedRecord%" Variable="%DOB%"/>
<VARIABLE MODIFY STRING Option="\x07" Destination="%NewMedRecord%" Variable="%CRLF%"/>
<VARIABLE MODIFY STRING Option="\x07" Destination="%NewMedRecord%" Variable="%Gender%"/>
<VARIABLE MODIFY STRING Option="\x07" Destination="%NewMedRecord%" Variable="%CRLF%"/>
<VARIABLE MODIFY STRING Option="\x07" Destination="%NewMedRecord%" Variable="%CRLF%"/>
<END IF/>
<TEXT FILE END PROCESS/>
<VARIABLE MODIFY STRING Option="\x11" Destination="%NewMedRecord%" Filename="C:\\Documents and Settings\\z051940\\Desktop\\New Medical Record.txt" CRLF="FALSE"/>

 

It will look something like this, in the script editor:

 

// Create a carriage return variable
Variable Set to ASCII Char 13 to %CRLF%
Variable Set to ASCII Char 10 to %T[1]%
Variable Modify String %CRLF%: Append Text String Variable (%T[1]%)
Text File Begin Process: C:\Documents and Settings\z051940\Desktop\medrecord.txt
 If Variable %FileProcess[1]% Contains "Person"
Variable Modify String: Replace "Person: " in %FileProcess[1]% with ""
Variable Modify String %FileProcess[1]%: Trim
Variable Modify String %NewMedRecord%: Append Text String Variable (%FileProcess[1]%%CRLF%)
 End If
 If Variable %FileProcess[1]% Contains "MRN"
 // Set MRN
Variable Set Integer %Position[1]% to the position of " MRN" in %FileProcess[1]%
Variable Modify String %FileProcess[1]%: Delete a substring starting at 1 and %Position[1]% characters long
Variable Modify String: Replace "MRN:" in %FileProcess[1]% with ""
Variable Set Integer %Position[2]% to the position of " DOB" in %FileProcess[1]%
Variable Modify String: Copy a substring in %FileProcess[1]%, starting at 1 and %Position[2]% characters long to %MRN%
Variable Modify String %MRN%: Trim
// Set DOB
Variable Modify String %FileProcess[1]%: Delete a substring starting at 1 and %Position[2]% characters long
Variable Modify String: Replace "DOB:" in %FileProcess[1]% with ""
Variable Modify String %FileProcess[1]%: Trim
Variable Modify String: Copy a substring in %FileProcess[1]%, starting at 1 and 10 characters long to %DOB%
Variable Modify String %DOB%: Trim
// Set Gender
Variable Set Integer %Position[3]% to the position of " Gender" in %FileProcess[1]%
Variable Modify String %FileProcess[1]%: Delete a substring starting at 1 and %Position[3]% characters long
Variable Modify String: Replace "Gender:" in %FileProcess[1]% with ""
Variable Modify String %FileProcess[1]%: Trim
Variable Set String %Gender% to "%FileProcess[1]%"
Variable Modify String %NewMedRecord%: Append Text String Variable (%MRN%)
Variable Modify String %NewMedRecord%: Append Text String Variable (%CRLF%)
Variable Modify String %NewMedRecord%: Append Text String Variable (%DOB%)
Variable Modify String %NewMedRecord%: Append Text String Variable (%CRLF%)
Variable Modify String %NewMedRecord%: Append Text String Variable (%Gender%)
Variable Modify String %NewMedRecord%: Append Text String Variable (%CRLF%)
Variable Modify String %NewMedRecord%: Append Text String Variable (%CRLF%)
 End If
Text File End Process
Variable Modify String: Save %NewMedRecord% to "C:\Documents and Settings\z051940\Desktop\New Medical Record.txt"

[twilkins]

Thanks for your suggestions. Everything works. I will keep looking for a way to compute (in MEP) age based on date of birth.

[stevecasper]

Thanks for your suggestions. Everything works. I will keep looking for a way to compute (in MEP) age based on date of birth.

Cory is probably the best person to ask for help with that particular aspect. He grasps the way dates are handled in MEP and Excel and has posted a number of times hints and suggestions on using the date features of MEP. Some of that might help you. I just have a hard time wrapping my head around date/time functions as decimals.

[twilkins]

this is the logic to compute the age. Today's date minus date of birth = xxxx days divided by 365.25 (accounts for leap year) to calculate age in years. Any suggestions on how to make this work in MEP?

[twilkins]

See attached sample macro. It prompts the user for DOB, then calculates age and displays in notepad.

calculate_age_from_DOB.mex

[stevecasper]

this is the logic to compute the age. Today's date minus date of birth = xxxx days divided by 365.25 (accounts for leap year) to calculate age in years. Any suggestions on how to make this work in MEP?

Yes, that will work - I don't know what I was thinking when I said you can't do it.

 

I guess what I meant was that there is no way to convert a text variable in the format 03/15/1980 into a date/time variable directly. What I believe you'll need to do is parse each segment into a different text variable, eg. %Mm%, %Day%, %Yr%; then convert each text variable into an integer or decimal and do whatever math needs to be done in order to create the date/time variable.

 

Having thought about it a little today, I'm now sure that it can be done with some patience, but I don't know that I'll have a chance to figure it out today.

[Cory]

For an approximation your method will work fine but your system doesn't take into account leap days. It is very possible that for dates near 2/28 you will have problems. Don't forget that leap days are not every 4 years but rather every 4 except 100, unless 400.

 

Your bigger problem is that you need to convert text to a time variable. I have a very cool macro that just just that calculating literally from day one. Let me know if you're interested and I'll dig it out.

[twilkins]

Cory,

 

I am interested in this macro if you can find it.

 

Thanks

[Cory]

I've been wanting to write it up and put it on my web page so I think I'll do that later this afternoon. I'll post back when done.