Database Migration – Demographics

Preface

If you had the misfortune to start with a proprietary EMR that does not meet your needs or is no longer supported you want to move your data to OSCAR.

This is bread and butter for OSCAR Service Providers (OSP’s) who do this competently and usually charge about $1000 for the demographics portion.

Additional components are more work and more money than is described here.

Document Version History

• v2.0 – initial public release on oscargalaxy.org – Oct 10, 2023

This document is copyright by Peter Hutten-Czapski 2014-2023 ©

Prerequisites

It is assumed that you or the implementation person(s) will require

1. Some knowledge of data structures of your existing EMR
2. Knowledge of data structures of OSCAR and their relationships
3. a demographics file of your patients in CSV format
4. a thorough understanding of the MySQL command line
5. a TEST database to test your queries against
6. About 8 hours to write test and deploy the scripts

HOW TO CONVERT DEMOGRAPHICS

This is how to transfer demographics from another system to OSCAR via SQL

The work is initially done on a TEST database and when successful is done  (much quicker) again on the OSCAR production database.

Patient Demographic Tables

Export data to a Spreadsheet

Lets assume you used your previous program to export a report on your demographics and got the following fields

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 Calc is a suitable spread sheet

Remember

• 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.  As elsewhere in the manual 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='2023-10-01 00:00:00',
    -> patient_status_date='2023-10-01';
Query OK, 674 rows affected, 2 warnings (0.03 sec)
Records: 674  Deleted: 0  Skipped: 0  Warnings: 2

Notes

• 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, and copying the existing into entries in the Archive tables.

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`='';

Finishing Up

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;