Re: [Gnumed-devel] How to get your own drug database into GNUmed

[Sebastian Hilbert]

Hi,

 

In Pgadmin you would be looking at v18 database --> 'ref' - schema --> branded_drug - table

 

or for subtances at

 

v18 database --> 'ref' - schema --> consumable_subtance - table

 

However I would strongly advise against messing with data that way. You are asking for trouble and broken databases

 

Consider going the data-pack route. We can help you with that.

 

Open http://www.gnumed.de/downloads/data/drugs/ca-2011-branded-drugs.zip

 

You will find a file named install-data-pack.sql

 

When you look at it you will see that it carries the information that is needed to get the data into GNUmed.

 

I would approach this like this:

1) Get you CVS file reformated (more on this later)

2) Write a script which will read your CVS file and converts it into the sql file

3) import the sql file into GNUmed

 

The clear benefit would be that you can later change your CVS file and have it converted to sql automatically. You never need to worry that the database structure of GNUmed changes without you noticing it.

 

ad 1) Look at the attached file. There are different sections for branded_drugs etc. I have left in some examples. Look at each section and check for the Values () part. It will show you which data is expected and in what order. Build your CSV file accordingly.

 

E.g.

INSERT INTO staging.branded_drug (description, preparation, atc_code, is_fake, fk_data_source, external_code, external_code_type, drug_code) VALUES ('Tapazole 5mg Tablet', 'tablet', 'H03BB02', false, 0, '00015741', 'FreeDiams::CA_HCDPD::DIN', 9);

 

INSERT INTO staging.ingred (drug_code, ingred_code, description, supplied, strength, strength_unit, strength_type, dosage_value, base, dosage_unit, notes) VALUES (9518, 3182, 'OXYQUINOLINE', 'I', 0.5, '%', '', '', 'N', '', '8-HYDROXYQUINOLINE 0.4% CHG TO 0.5% AS PER VDD 2006.04.05');

 

INSERT INTO staging.consumable_substance (description, atc_code, amount, unit, drug_code) VALUES ('aldioxa', NULL, 0.2, '%', 1338);

 

INSERT INTO staging.vaccine (is_live, external_code, route) VALUES (NULL, '00075124', 'INTRADERMAL');

 

When you prepare the CSV I would produce seperate files for branded_drug, consumable_substance, ingred, vaccine

 

ad 2) Once we have a suitable Excel or CSV file we ca take a stab at the converter

 

Many people will thank you for that work since your CSV file could serve as a template for other drug databases.

 

Regards,

Sebastian

install-data-pack.sql
Description: Text Data