docutek wrote on Thursday, October 30, 2014:
IMPORTING HL7 INTO patient_data table Using PHP MyAdmin
OPEN EMR
Tools you will need:
Textmechanic
Textmechanic.com/Remove-Lines-Containing.html
PSPAD: http://www.pspad.com/
MS Excel
Before you start
This tutorial is they way around of importing data from a HL7 file into Open EMR patient_data table. It can be very useful if you want to import patient data from other EMR technology or any Billing software that can export an HL7 file.
You will need to ask the other EMR or Billing company to export this file for you. In my case, I asked the provider to write a letter asking for the data. After a couple of days, the Billing company produced the file.
After you get the file, install Open EMR. Make a backup of the table patient_data (I know is empty) but trust me, you want to have a backup of the original. You can also rename it to patient_data_original.
During this procedure, you will not be able to search for patient data since Open EMR will need patient_data table to work.
It only took me one hour to finish it and the file had 13,567 lines after cleaning it of unwanted characters and unwanted lines.
Steps
#1 Open the hl7 file using PSPAD, copy & paste the content of the file.
#2 Open your browser and type http:// http://textmechanic.com/Remove-Lines-Containing.html
#3 Paste the content of the hl7 file into it. You might want to sort the file before you do this. Thanks to Stephen Waite for this observation.
Most HL7 will be exported not with lines that start with EVN, or PV1. In my case, the file came from ProClaim, a billing software.
#4 Go to the upper left corner of the website you just opened and inside the “Search lines for” type the name of the beginning of that line that you want to remove.
Example: EVN|A04|20141024084014|||||
As you can see above, the line starting with EVN is useless to Open EMR patient_data because it has no valuable imput.
#5 Get rid of the lines that you do not need. In my case, I only needed the PID line. Example:
PID|PUBID|PATIENT NAME|PATIENT LAST NAME|19351103|SEX|||STREET ^STATEPR^ZIPCODE||TEL|CEL||||DOB4||||RACE||||||||||||||||
That information is what I asked the billing company to report but, you can be more specific.
#6 After you have cleaned the hl7 file from all unwanted lines, it’s time to remove special characters. To remove special characters I used PSPAD, download a free copy using the link at the begging of this tutorial called “Tools you will need” and install it.
#7 Copy all the code that is still in http:// http://textmechanic.com/Remove-Lines-Containing.html
#9 Open PSPAD and past the content of the hl7 inside Textmechanic.com
#10 Using PSPAD go to the menu and select Search - Replace
#11 Look for the characters that you want to remove from the hl7. In my case I wanted to remove and replace ‘|’ and ‘^’ and replace for ‘,’.
#12 Now replace the word PID for nothing. Just don’t put anything under replace for. On step 11 you have replaced ‘|’ or ‘^’ for a comma. Replace PID and do not type anything to replace for.
#13 Depending on how big your file is, it might take a couple of minutes to finish. After you are done replacing, you will have a comma delimited value or csv BUT, still not complete.
#14 Right click over the code that you have in PSPAD and select “Select all” and copy it.
#15 Open Excel and create a blank sheet.
Now the magic starts.
#16 Paste the code from PSPAD into the first column in excel
#17 You now can see that all the data is in Excel but is still no ready. Go to Excel menu, select Data and hit the icon Text to Columns. Put the columns in the same order as the fields in patient_data. You can copy and paste an entire Excel column into another column or ad new columns and paste data.
The data is now separated into columns. It’s time to go back to PHP MyAdmin
#18 Open the your Open EMR database, select the renamed table patient_data_original. Print the content of it.
#19 Create a new table and name it patient_data. Add the fields starting with pubid, fname, lname,DOB, sex, city,zipcode,country,phone_home.
That is the only data I needed for this project. It help me to have all 13,567 patients at least initialize and accounting for. The rest of the data was added when the patient came back to the medical office for follow up. This method avoided frustration of having to add all patients again.
#20 Now after you have created the basic fields, add the other fields left from the table patiend_data_original with the exact specifications. If you miss a field, Open EMR won’t work. You need to add every single one exactly like they were.
#21 After you have recreated the table patient_data go back to Excel and save the file with the same order to created the new patient_data table. It should be pubid, fname, lname,DOB, sex, city,zipcode,country,phone_home
#22 Make sure you do a short test of one or two patients before importing the full csv file. Copy and paste the first Excel row into a new sheet, export it as a csv and name it test.csv
#23 To import the file, go to PHP MyAdmin, select the table you just created patient_data and select Import
#24 Browse for the test file test.csv
#25 Select for Format: CSV DATA LOAD and select Replace table data. Make sure the first replace options has a comma. Select DO NOT ABORT and click Continue.
#26 Go back to Open EMR and select Name under the lower search box at the end of the Main Menu.
#27 Congratulations. You just successfully imported an HL7 into Open EMR.