Read Me File - Drug Product Database (DPD) Data Extract - Health Canada

The Drug Product Database (DPD) system captures information on Canadian human, veterinary, disinfectant, and radiopharmaceutical products approved for use by Health Canada.

This extract contains approved, marketed (active), cancelled (inactivated), and as of September 1, 2017, dormant products in separate files. Although the DPD is a relational database, there is a requirement to provide the data to users in a common format, therefore the data has been extracted into a flat file format. Data values in all files are separated by commas and delimited by double quotes. An entity relationship diagram can be found following this page. The data structure for the approved, marketed, cancelled, and dormant products is the same. The entity relationship diagram is provided at the bottom of this page.

It is recommended to load each set of product files into a separate database to keep the products distinct during querying (e.g., an approved database, a marketed database, a cancelled database, and a dormant database).

It is possible to load all of the product files (e.g., approved, marketed, cancelled and dormant) into one database. To distinguish between the approved, marketed, cancelled, and dormant products, the STATUS field may be used in queries. The approved products have a status "APPROVED", the marketed products have a status of "MARKETED", the dormant products have a status of "DORMANT", and cancelled products have one of the following "CANCELLED" statuses:

• "CANCELLED POST MARKET"
• "CANCELLED PRE MARKET"

Company Information

In the DPD, a product may have several companies associated with them. Each product will have a DIN Owner associated with it. If a DIN Owner has designated another company to receive their mailing, their name and address will also be included and can be identified with a "Y" flag in the ADDRESS_MAILING_FLAG field.

The company contact information is not included in the extract (ATTENTION_TO, LANGUAGE, TELEPHONE_NUMBER, FAX_NUMBER, EMAIL_ADDRESS). The contact information in the database is generally a representative from the Regulatory Affairs department. It has come to our attention that some users are contacting these individuals to make general inquiries, we would like to recommend not doing so.

Approved Products

The files included in the zipped file for approved products are as follows:

• comp_ap.txt QRYM_COMPANIES
• drug_ap.txt QRYM_DRUG_PRODUCT
• form_ap.txt QRYM_FORM
• ingred_ap.txt QRYM_ACTIVE_INGREDIENTS
• package_ap.txt QRYM_PACKAGING
• pharm_ap.txt QRYM_PHARMACEUTICAL_STD
• route_ap.txt QRYM_ROUTE
• schedule_ap.txt QRYM_SCHEDULE
• status_ap.txt QRYM_STATUS
• ther_ap.txt QRYM_THERAPEUTIC_CLASS
• vet_ap.txt QRYM_VETERINARY_SPECIES
• allfiles_ap.zip all files above

Marketed (active) Products

The files included in the zipped file for marketed (active) products are as follows:

• comp.txt QRYM_COMPANIES
• drug.txt QRYM_DRUG_PRODUCT
• form.txt QRYM_FORM
• ingred.txt QRYM_ACTIVE_INGREDIENTS
• package.txt QRYM_PACKAGING
• pharm.txt QRYM_PHARMACEUTICAL_STD
• route.txt QRYM_ROUTE
• schedule.txt QRYM_SCHEDULE
• status.txt QRYM_STATUS
• ther.txt QRYM_THERAPEUTIC_CLASS
• vet.txt QRYM_VETERINARY_SPECIES
• allfiles.zip all files above

Cancelled (inactivated) Products

Files containing the same types of information as for marketed (active) products have been added to the Data Extract to identify products that are no longer marketed in Canada. The files included in the zipped file containing information on discontinued (inactivated) products are as follows:

• comp_ia.txt QRYM_COMPANIES
• drug_ia.txt QRYM_DRUG_PRODUCT
• form_ia.txt QRYM_FORM
• ingred_ia.txt QRYM_ACTIVE_INGREDIENTS
• package_ia.txt QRYM_PACKAGING
• pharm_ia.txt QRYM_PHARMACEUTICAL_STD
• route_ia.txt QRYM_ROUTE
• schedule_ia.txt QRYM_SCHEDULE
• status_ia.txt QRYM_STATUS
• ther_ia.txt QRYM_THERAPEUTIC_CLASS
• vet_ia.txt QRYM_VETERINARY_SPECIES
• allfiles_ia.zip all files above

The file "inactive.txt" which identifies products that are no longer approved or marketed (active) in Canada is still available. The following information fields are included in the file:

• DRUG_CODE NUMBER (8)
• DRUG_IDENTIFICATION_NUMBER VARCHAR2(29)
• BRAND_NAME VARCHAR2(200)
• HISTORY_DATE DATE

Dormant Products

Effective September 1, 2017: Files containing the same types of information as for marketed (active) products have been added to the data extract files to identify products that were previously marketed in Canada but for which the manufacturer has suspended sale for period of at least 12 months. The files included in the zipped file for dormant products are as follows:

• Comp_dr.txt QRYM_COMPANIES
• Drug_dr.txt QRYM_DRUG_PRODUCT
• Form_dr.txt QRYM_FORM
• ingred_dr.txt QRYM_ACTIVE_INGREDIENTS
• package_dr.txt QRYM_PACKAGING
• pharm_dr.txt QRYM_PHARMACEUTICAL_STD
• route_dr.txt QRYM_ROUTE
• schedule_dr.txt QRYM_SCHEDULE
• status_dr.txt QRYM_STATUS
• ther_dr.txt QRYM_THERAPEUTIC_CLASS
• vet_dr.txt QRYM_VETERINARY_SPECIES
• allfiles_dr.zip all files above

French Characters

The DPD database currently uses UTF-8 encoding.

Contact

If you require additional information regarding this data extraction, e-mail: hc.osip.sys-bppi.sc@canada.ca

Data Structure for all Products

QRYM_ACTIVE_INGREDIENTS

Name	Null?	Type
DRUG_CODE	NOT NULL	NUMBER(8)
ACTIVE_INGREDIENT_CODE		NUMBER(6)
INGREDIENT		VARCHAR2(240)
INGREDIENT_SUPPLIED_IND		VARCHAR2(1)
STRENGTH		VARCHAR2(20)
STRENGTH_UNIT		VARCHAR2(40)
STRENGTH_TYPE		VARCHAR2(40)
DOSAGE_VALUE		VARCHAR2(20)
BASE		VARCHAR2(1)
DOSAGE_UNIT		VARCHAR2(40)
NOTES		VARCHAR2(2000)
INGREDIENT_FFootnote *		VARCHAR2(400)
STRENGTH_UNIT_FFootnote *		VARCHAR2(80)
STRENGTH_TYPE_FFootnote *		VARCHAR2(80)
DOSAGE_UNIT_FFootnote *		VARCHAR2(80)

QRYM_COMPANIES

Name	Null?	Type
DRUG_CODE	NOT NULL	NUMBER(8)
MFR_CODE		VARCHAR2(5)
COMPANY_CODE		NUMBER(6)
COMPANY_NAME		VARCHAR2(80)
COMPANY_TYPE		VARCHAR2(40)
ADDRESS_MAILING_FLAG		VARCHAR2(1)
ADDRESS_BILLING_FLAG		VARCHAR2(1)
ADDRESS_NOTIFICATION_FLAG		VARCHAR2(1)
ADDRESS_OTHER		VARCHAR2(1)
SUITE_NUMBER		VARCHAR2(20)
STREET_NAME		VARCHAR2(80)
CITY_NAME		VARCHAR2(60)
PROVINCE		VARCHAR2(40)
COUNTRY		VARCHAR2(40)
POSTAL_CODE		VARCHAR2(20)
POST_OFFICE_BOX		VARCHAR2(15)
PROVINCE_FFootnote *		VARCHAR2(100)
COUNTRY_FFootnote *		VARCHAR2(100)

QRYM_DRUG_PRODUCT

Name	Null?	Type
DRUG_CODE	NOT NULL	NUMBER(8)
PRODUCT_CATEGORIZATION		VARCHAR2(80)
CLASS		VARCHAR2(40)
DRUG_IDENTIFICATION_NUMBER		VARCHAR2(29)
BRAND_NAME		VARCHAR2(200)
DESCRIPTOR		VARCHAR2(150)
PEDIATRIC_FLAG		VARCHAR2(1)
ACCESSION_NUMBER		VARCHAR2(5)
NUMBER_OF_AIS		VARCHAR2(10)
LAST_UPDATE_DATE		DATE
AI_GROUP_NO		VARCHAR2(10)
CLASS_FFootnote *		VARCHAR2(80)
BRAND_NAME_FFootnote *		VARCHAR2(300)
DESCRIPTOR_FFootnote *		VARCHAR2(200)

QRYM_STATUS

Name	Null?	Type
DRUG_CODE	NOT NULL	NUMBER(8)
CURRENT_STATUS_FLAG		VARCHAR2(1)
STATUS		VARCHAR2(40)
HISTORY_DATE		DATE
STATUS_FFootnote *		VARCHAR2(80)
LOT_NUMBERFootnote *		VARCHAR2(50)
EXPIRATION_DATEFootnote *		DATE

QRYM_FORM

Name	Null?	Type
DRUG_CODE	NOT NULL	NUMBER(8)
PHARM_FORM_CODE		NUMBER(7)
PHARMACEUTICAL_FORM		VARCHAR2(40)
PHARMACEUTICAL_FORM_FFootnote *		VARCHAR2(80)

QRYM_PACKAGING

Name	Null?	Type
DRUG_CODE	NOT NULL	NUMBER(8)
UPC		VARCHAR2(12)
PACKAGE_SIZE_UNIT		VARCHAR2(40)
PACKAGE_TYPE		VARCHAR2(40)
PACKAGE_SIZE		VARCHAR2(5)
PRODUCT_INFORMATION		VARCHAR2(80)
PACKAGE_SIZE_UNIT_FFootnote *		VARCHAR2(80)
PACKAGE_TYPE_FFootnote *		VARCHAR2(80)

QRYM_PHARMACEUTICAL_STD

Name	Null?	Type
DRUG_CODE	NOT NULL	NUMBER(8)
PHARMACEUTICAL_STD		VARCHAR2(40)

QRYM_ROUTE

Name	Null?	Type
DRUG_CODE	NOT NULL	NUMBER(8)
ROUTE_OF_ADMINISTRATION_CODE		NUMBER(6)
ROUTE_OF_ADMINISTRATION		VARCHAR2(40)
ROUTE_OF_ADMINISTRATION_FFootnote *		VARCHAR2(80)

QRYM_SCHEDULE

Name	Null?	Type
DRUG_CODE	NOT NULL	NUMBER(8)
SCHEDULE		VARCHAR2(40)
SCHEDULE_FFootnote *		VARCHAR2(160)

QRYM_THERAPEUTIC_CLASS

Name	Null?	Type
DRUG_CODE	NOT NULL	NUMBER(8)
TC_ATC_NUMBER		VARCHAR2(8)
TC_ATC		VARCHAR2(120)
TC_AHFS_NUMBER		VARCHAR2(20)
TC_AHFS		VARCHAR2(80)
TC_ATC_FFootnote *		VARCHAR2(240)
TC_AHFS_FFootnote *		VARCHAR2(160)

QRYM_VETERINARY_SPECIES

Name	Null?	Type
DRUG_CODE	NOT NULL	NUMBER(8)
VET_SPECIES		VARCHAR2(80)
VET_SUB_SPECIES		VARCHAR2(80)
VET_SPECIES_FFootnote *		VARCHAR2(160)