Re: [Gnumed-devel] import patient data - basic procedure

[Busser, Jim]

On 2013-04-28, at 11:01 AM, Jerzy Luszawski <address@hidden> wrote:

> My situation:
> I had a simple list of patients with comments and dates of first and
> last contact, which I needed imported to bare Gnumed database as initial
> data.

Hi,

What I offer below is a series of use-at-own risk notes that I made while 
purging various kinds of test data. It includes some notes about Importing data 
from flat files e.g. CSV

Before importing patients, you may wish to do some combination of the following:

-- Remove test patients

run the script that you will find in the server package

        gm-remove_person.sh

as described at

        http://lists.gnu.org/archive/html/gnumed-devel/2009-08/msg00083.html



-- Get rid of demo clinical substance / drug data

TRUNCATE TABLE clin.substance_intake CASCADE ;
TRUNCATE TABLE clin.vaccination CASCADE;

-- Get rid of commercial vaccines (keeping pseudo-vaccines):
DELETE FROM clin.lnk_vaccine2inds WHERE fk_vaccine IN (SELECT pk FROM 
clin.vaccine WHERE fk_brand IN (SELECT pk FROM ref.branded_drug WHERE is_fake 
is FALSE) );
DELETE FROM clin.vaccine WHERE fk_brand IN (SELECT pk FROM ref.branded_drug 
WHERE is_fake is FALSE);
DELETE FROM ref.branded_drug WHERE is_fake is FALSE and pk IN (SELECT fk_brand 
FROM clin.vaccine) ;

-- Get rid of commercial drugs (but preserving vaccines)

BEGIN;
DELETE FROM ref.lnk_substance2brand ;
DELETE FROM ref.branded_drug WHERE is_fake is FALSE OR POSITION('J07' in 
atc_code)=0 ;
END ;

-- get rid of addresses entered as gm-dbo
-- delete from dem.address where user = 'gm-dbo' and id > 12;
-- ERROR:  update or delete on table "address" violates foreign key constraint 
"lnk_person_org_address_id_address_fkey" on table "lnk_person_org_address"
-- DETAIL:  Key (id)=(13) is still referenced from table 
"lnk_person_org_address".


-- Perform a variety of other cleanup:
-- -----------------------------------

delete from audit.log_substance_intake ;
delete from audit.log_substance_in_brand ;
delete from audit.log_active_substance ;
delete from audit.log_allergy ;
delete from audit.log_allergy_state ;
delete from audit.log_branded_drug ;
delete from audit.log_consumable_substance ;
delete from audit.log_consumed_substance ;
delete from audit.audit_fields ;
delete from audit.log_vaccine ;
delete from audit.log_vaccination_definition ;
delete from audit.log_vaccination_course ;


begin;
delete from ref.lnk_substance2brand;
delete from ref.consumable_substance ;
commit;


****************************************************
Look at what's already in GNUmed:

# the GNUmed universe of known consumable substances, which can exist without 
brands
ref.consumable_substance

# the GNUmed universe of known brands
# … which, if directly imported, require to be linked separately to component 
substances)
ref.branded_drug

# links to brands
ref.lnk_substance2brand

# what patients are already known to be taking
clin.substance_intake

represents a combination of
1) ref.consumable_substance items which are referenced directly (some of which 
may not have associated brands and some of which may not even be drugs at all) 
and
2) ref.consumable_substance items which are referenced indirectly through a 
link value which links the substance to a branded entity (in which case the 
patient is recognized to be receiving the multiple components of the branded 
product)

# to query all substances taken by all patients (non-distinct)
select *
from
        clin.substance_intake
--> generates 19 results


# to query all directly-referenced substances taken by all patients
select *
from
        clin.substance_intake
                inner join
        ref.consumable_substance
                on 
(clin.substance_intake.fk_substance=ref.consumable_substance.pk)
ORDER BY clin_when

# to query all indirectly-referenced (via brand) substances taken by all 
patients
select age(date_trunc('day',now()),date_trunc('day', intake.clin_when)), 
atc_code, brand.description AS brand, brand.preparation AS form, schedule, aim, 
duration, intake_is_approved_of, discontinued, narrative
from
        clin.substance_intake AS intake
                inner join
                ref.lnk_substance2brand
                        on (intake.fk_drug_component=ref.lnk_substance2brand.pk)
                inner join
                ref.branded_drug AS brand
                        on (brand.pk=ref.lnk_substance2brand.fk_brand)
ORDER BY clin_when
                
# some SQL to assess and decide whether to delete the following:

select *
from
        ref.lnk_substance2brand
                inner join
                ref.branded_drug AS brand
                        on (brand.pk=ref.lnk_substance2brand.fk_brand) ;

delete from
        ref.branded_drug
        where 
(select *
from
        ref.lnk_substance2brand
                inner join
                ref.branded_drug AS brand
                        on (brand.pk=ref.lnk_substance2brand.fk_brand)
);


SELECT * from ref.branded_drug WHERE POSITION('- generic vaccine' in 
ref.branded_drug.description)>0 ;
SELECT * from ref.branded_drug WHERE POSITION('J07' in atc_code)=1 ;

DELETE FROM ref.branded_drug WHERE is_fake is FALSE;




DELETE FROM clin.lnk_vaccine2inds WHERE fk_vaccine IN (SELECT pk FROM 
clin.vaccine WHERE fk_brand IN (SELECT pk FROM ref.branded_drug WHERE is_fake 
is FALSE) )

DELETE FROM ref.branded_drug WHERE is_fake is FALSE and pk IN (SELECT fk_brand 
FROM clin.vaccine) ;

DELETE FROM clin.vaccine WHERE fk_brand IN (SELECT pk FROM ref.branded_drug 
WHERE is_fake is FALSE);

BEGIN; 
  DELETE FROM ref.branded_drug WHERE pk IN (SELECT pk FROM clin.vaccine 
    WHERE clin.vaccine.pk IN ()); 
  DELETE FROM isuse WHERE reservation_id = reservation_to_delete; 
  DELETE FROM reservations WHERE reservation_id = reservation_to_delete; 
END; 

delete from clin.vaccine where ref.branded_drug.is_fake is TRUE 

select clin.vaccine where 

select ref.branded_drug.description from clin.vaccine
inner join
ref.branded_drug
on
(ref.branded_drug.pk=clin.vaccine.fk_brand)
where ref.branded_drug.is_fake is TRUE

inner join
clin.lnk_vaccine2inds
on
(clin.vaccine.pk=clin.lnk_vaccine2inds.fk_vaccine)

;

select *
from
        ref.branded_drug


**************************************************
Importing data from flat files e.g. CSV

http://www.postgresql.org/docs/9.0/static/tutorial-populate.html
http://www.postgresql.org/docs/9.0/static/sql-copy.html
http://www.postgresql.org/docs/9.0/static/sql-createtable.html

When we get a spreadsheet file, such as Excel or Open Office Spreadsheet from a 
client we first convert it to a cvs file by doing a "save as" csv. Next we 
prepare a staging table in the postgres target database which basically has the 
same number of columns, of type varchar, as there are fields in the csv file. 
Then we import the csv by running the sql statement

You will need to have insert privileges on the table you are inserting into. 
The command will stop on the first error and roll back. Once in the staging 
tables you can use normal sql to insert and manipulate the data into the final 
target tables.

# pg < 9

        COPY table_name FROM 'filename' WITH CSV [ HEADER ]

# pg 9

        COPY table_name FROM '/[...]/filename.csv' FORMAT csv

**************************************************
If encoding error, see
        http://www.postgresql.org/docs/8.4/static/multibyte.html
                \encoding win1250
        SET client_encoding to 'win1250';

        latin-1
        utf8
        http://linux.die.net/man/1/enca
        http://directory.fsf.org/project/recode/
**************************************************
-- Jim