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)
Footnote 1

These fields are available in the new DPD Data Extract files.

Return to footnote * referrer

DPD Relationship Diagram

This extract relationship diagram shows that all files are linked to the <abbr>QRYM</abbr>_DRUG_PRODUCT file.

Page details

Date modified: