Migrating Demographics to OSCAR
Shared by Peter Hutten-Czapski. Updated by Adrian Starzynski
Migrating demographics to OSCAR is easy-peasy but not quick and you have to have database access.
This is how to transfer demographics from another system to OSCAR via SQL. You can do it yourself if you are familiar with the command line.
When I do this type of work this is how I do it. These notes are from a older version of OSCAR and do not deal with the residential address vs postal address and the ISO formatting of provinces etc. that arose in OSCAR 19 from OntarioMD requirements. I’ll leave that to you as an exercise…
The work is initially done on a TEST database and when successful is done (much quicker) again on the OSCAR production database.
There are 4 main demographic tables.
- ‘demographic’ has most of the patient data and is essential.
- ‘demographiccust’ has the patient alert text and notes. OSCAR will allow 0 but better 1 entry for each record in ‘demographic’
- ‘demographicExt’ is key : key value paired and can accommodate any other type of demographic data
- Cellphone numbers and extensions that are shown by default in the OSCAR demographic GUI
- Other data including that from a foreign EMR that OSCAR demographic can be configured to show.
- ‘admission’ controls access to the patient data. There has to be 1 admission record for every active demographic record in any program.
Only the bare minimum, importing to the demographic and the admission tables, is described below. NOTE this assumes that there are no duplicates, that will require extra work
Export data to a Spreadsheet
Lets assume you used your previous program to export a report on your demographics and got the following fields
|File Number||First Name||Last Name||DOB||H/C||version||Home Phone||Address 1||Address 2||City||Province||Patient Rostered||Rostered Date||Sex|
|00031447-00||Abra||Cadabra||13/07/2001||1999999998||XP||(705) 647-1111||101 Farah Ave.||Box 22||New Liskard||ON||Patient||07/10/2009||F|
This is all very fine, but as per usual there are a number of problems with the file
- The length of the File Number (12) is larger than the chart_no in OSCAR (default 10), it’s a simple update to fix
- The Date of Birth is one field while OSCAR divides the year month and date of birth
- The Address is in two lines while OSCAR uses only one line
- New Liskeard is incorrectly spelled
- The indication of Roster status is not the same as in OSCAR (which uses RO)
- Various obligatory fields in OSCAR are missing.
Clean up Your Act
Now is a good time to use a spread sheet and sort and fix misspellings, missing data, and paste large numbers of OSCAR specific “RO” into place
Libre Office is a suitable spread sheet
- to save in CSV format.
- Windows line endings are \l\n
- Linux line endings are \n
- remove the heading line
Load Data Infile
Now use the power of MySQL to load the file and transform and add all the pieces that OSCAR expects into a MySQL table that resembles the demographics one
First make a temporary table for the data to test your work. replace ****** with your MySQL password
$ mysql -uroot -p****** oscar_15 < CREATE TABLE stacy LIKE demographic;
Now tweak it so that the demographic_no does not get auto filled (you will need to drop the key as well)
ALTER TABLE `stacy` CHANGE `demographic_no` `demographic_no` INT( 10 ) NOT NULL;
ALTER TABLE `stacy` DROP PRIMARY KEY;
Now for a CSV file that you have cleaned that has lines that look like the following
00031447-00,Abra,Cadabra,13/07/2001,1999999998,XP,(705) 647-1111,101 Farah Ave.,Box 22,New Liskeard,P0J 1P0,ON,RO,07/10/2009,F
Type something like
$ mysql -uroot -p****** oscar_15 mysql> LOAD DATA INFILE '/home/peter/Downloads/10092014B.csv' INTO TABLE `stacy` FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' -> ( `chart_no` , `first_name` , `last_name` , @DOB , `hin` , `ver` , `phone` , @Add1 , @Add2 , `city` , `postal` , `province` , `roster_status` , @roster_date, `sex` ) -> set -> roster_date=str_to_date(@roster_date,'%d/%m/%Y'), -> year_of_birth=DATE_FORMAT(@DOB,'%Y')), -> month_of_birth=DATE_FORMAT(@DOB,'%m')), -> date_of_birth=DATE_FORMAT(@DOB,'%d'), -> address=CONCAT_WS(', ',@Add1,@Add2), -> hc_type=province, -> provider_no='124', -> patient_status='AC', -> date_joined='2009-01-01', -> official_lang='English', -> family_doctor='<rdohip></rdohip><rd></rd>', -> country_of_origin='-1', -> newsletter='Unknown', -> lastUpdateUser='101', -> lastUpdateDate='2014-10-01 00:00:00', -> patient_status_date='2014-10-01'; Query OK, 674 rows affected, 2 warnings (0.03 sec) Records: 674 Deleted: 0 Skipped: 0 Warnings: 2
- there is an entry in the list of fields for every field in the CSV file in their natural order
- the str_to_date function converts the string in the CSV to MySQL date type
- the YEAR, MONTH and DATE functions are used to extract those specific fields from the combined DOB found in the other EMR
- the CONCAT_WS function is used to combine the two address fields with a ‘, ‘ between them into the OSCAR address field
- various defaults are set based on the data used NOTE that OSCAR wants zero padded date_of_birth and month_of_birth
- ANY warnings should worry you, review the output to see what you may still need to fix
- use SHOW WARNINGS to determine the warnings from the last executed statement
- Be particularly suspicious of SQL’s that take long to complete, your table joining syntax is probably off and so will be the data
- You HAVE TO test the data conversion for EACH table to ensure that the conversion is complete and correct
Determine Duplicates Already in OSCAR
You can skip this step if you are starting with an empty OSCAR. however if you are merging in a new list into an existing production database, you will not want to add duplicate entries!
First just grab the demographic_no from the OSCAR installation where the health insurance numbers match (requires unique hin’s to work)
mysql> UPDATE stacy s, demographic d SET s.demographic_no = d.demographic_no WHERE s.hin = d.hin AND s.`hin` <> '' Query OK, 125 rows affected (0.12 sec) Rows matched: 125 Changed: 125 Warnings: 0
Then if your import contains any files you want to transfer that have a blank hin you should try to match those by name and DOB
mysql> UPDATE stacy s, demographic d SET s.demographic_no = d.demographic_no -> WHERE s.`hin` = '' AND d.last_name=s.last_name AND d.first_name=s.first_name AND -> d.year_of_birth=s.year_of_birth AND d.month_of_birth=s.month_of_birth AND d.date_of_birth=s.date_of_birth; Query OK, 1 row affected (0.05 sec) Rows matched: 1 Changed: 1 Warnings: 0
Now the table `stacy` has a hundred twenty six demographics that match the production database identified by a non blank s.demographic_no.
ASSUMING that the incoming charts are more current lets overwrite the existing with the new.
mysql> UPDATE `demographic` d, `stacy` s SET -> d.`address`=s.`address`, -> d.`ver`=s.`ver`, -> d.`city`=s.`city`, -> d.`province`=s.`province`, -> d.`postal`=s.`postal`, -> d.`phone`=s.`phone`, -> d.`roster_status`=s.`roster_status`, -> d.`patient_status`=s.`patient_status`, -> d.`date_joined`=s.`date_joined`, -> d.`chart_no`=s.`chart_no`, -> d.`official_lang`=s.`official_lang`, -> d.`provider_no`=s.`provider_no`, -> d.`roster_date`=s.`roster_date` -> d.`patient_status_date`=s.`patient_status_date` -> WHERE d.`demographic_no`=s.`demographic_no` ; Query OK, 126 rows affected (0.93 sec) Rows matched: 126 Changed: 126 Warnings: 0
The above is a bit brutish of an approach however as mentioned it depends on the assumption that the incoming data is better than the old. Even if true in the aggregate, that may not be true in a specific. You risk overwriting a good work telephone number with a NULL, or worse still a version code with an empty string. Better would be a stored procedure that goes through the matching demographics one row at a time and does logic to effect the merge based on empty or NULL values and the dates.
Adding New Patients into OSCAR
Load the unique new entries into OSCAR
mysql> INSERT INTO `demographic` -> (`title`, `last_name`, `first_name`, `address`, `city`, `province`, `postal`, `phone`, `phone2`, `email`, `myOscarUserName`, `year_of_birth`, `month_of_birth`, `date_of_birth`, `hin`, `ver`, `roster_status`, `patient_status`, `date_joined`, `chart_no`, `official_lang`, `spoken_lang`, `provider_no`, `sex`, `end_date`, `eff_date`, `pcn_indicator`, `hc_type`, `hc_renew_date`, `family_doctor`, `alias`, `previousAddress`, `children`, `sourceOfIncome`, `citizenship`, `sin`, `country_of_origin`, `newsletter`, `anonymous`, `lastUpdateUser`, `lastUpdateDate`, `roster_date`, `patient_status_date`, `roster_termination_date`, `roster_termination_reason`) -> SELECT `title`, `last_name`, `first_name`, `address`, `city`, `province`, `postal`, `phone`, `phone2`, `email`, `myOscarUserName`, `year_of_birth`, `month_of_birth`, `date_of_birth`, `hin`, `ver`, `roster_status`, `patient_status`, `date_joined`, `chart_no`, `official_lang`, `spoken_lang`, `provider_no`, `sex`, `end_date`, `eff_date`, `pcn_indicator`, `hc_type`, `hc_renew_date`, `family_doctor`, `alias`, `previousAddress`, `children`, `sourceOfIncome`, `citizenship`, `sin`, `country_of_origin`, `newsletter`, `anonymous`, `lastUpdateUser`, `lastUpdateDate`, `roster_date`, `patient_status_date`, `roster_termination_date`, `roster_termination_reason` -> FROM `stacy` s -> WHERE s.`demographic_no`='' ; Query OK, 548 rows affected (1.14 sec) Records: 548 Duplicates: 0 Warnings: 0
Admit the New Patients
Now you need to load these patients into the admission table to the OSCAR program (10016) so that they can be accessible in OSCAR’s GUI
INSERT INTO `admission`(client_id,program_id,provider_no,admission_date,admission_status,team_id,temporary_admission_flag,lastUpdateDate) SELECT d.demographic_no,'10016',s.provider_no,'2014-10-01','current',0,0,'2014-10-01 00:00:00' FROM `stacy` s, `demographic` d WHERE s.`hin`=d.`hin` AND d.last_name=s.last_name AND d.first_name=s.first_name AND d.year_of_birth=s.year_of_birth AND d.month_of_birth=s.month_of_birth AND d.date_of_birth=s.date_of_birth AND s.`demographic_no`='';
If you are going to do any more data importation you will need a table that cross references the older EMR’s chart numbers with OSCAR’s demographic.
Add in the demographics of the new patients for those with a hin
mysql> UPDATE stacy s, demographic d SET s.demographic_no = d.demographic_no WHERE s.hin = d.hin AND s.`hin` <> ''
Then if your import contains any files you want to transfer that have a blank hin you should dry to match those by name and DOB
mysql> UPDATE stacy s, demographic d SET s.demographic_no = d.demographic_no -> WHERE s.`hin` = '' AND d.last_name=s.last_name AND d.first_name=s.first_name AND -> d.year_of_birth=s.year_of_birth AND d.month_of_birth=s.month_of_birth AND d.date_of_birth=s.date_of_birth;