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"
- “CANCELLED (UNRETURNED ANNUAL)”
- “CANCELLED (SAFETY ISSUE)”
- “AUTHORIZATION BY INTERIM ORDER EXPIRED”
- “AUTHORIZATION BY INTERIM ORDER REVOKED”
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:
- bios_ap.txt QRYM_BIOSIMILARS
- 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:
- bios.txt QRYM_BIOSIMILARS
- 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:
- bios_ia.txt QRYM_BIOSIMILARS
- 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
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:
- bios_dr.txt QRYM_BIOSIMILARS
- 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
| Name | Null? | Type |
|---|---|---|
| DRUG_CODE | NOT NULL | NUMBER(8) |
| SI_DESC_E | - | VARCHAR2(2000) |
| SI_DESC_F | - | VARCHAR2(2000) |
| SI_CODE | - | NUMBER(38) |
| 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_F | - | VARCHAR2(100) |
| COUNTRY_F | - | VARCHAR2(100) |
| 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_F | - | VARCHAR2(80) |
| BRAND_NAME_F | - | VARCHAR2(300) |
| DESCRIPTOR_F | - | VARCHAR2(200) |
| Name | Null? | Type |
|---|---|---|
| DRUG_CODE | NOT NULL | NUMBER(8) |
| PHARM_FORM_CODE | - | NUMBER(7) |
| PHARMACEUTICAL_FORM | - | VARCHAR2(40) |
| PHARMACEUTICAL_FORM_F | - | VARCHAR2(80) |
| 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_F | - | VARCHAR2(400) |
| STRENGTH_UNIT_F | - | VARCHAR2(80) |
| STRENGTH_TYPE_F | - | VARCHAR2(80) |
| DOSAGE_UNIT_F | - | VARCHAR2(80) |
| Name | Null? | Type |
|---|---|---|
| DRUG_CODE | NOT NULL | NUMBER(8) |
| DRUG_IDENTIFICATION_NUMBER | - | VARCHAR2(29) |
| BRAND_NAME | - | VARCHAR2(200) |
| HISTORY_DATE | - | DATE |
| BRAND_NAME_F | - | VARCHAR2(300) |
| 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(4000) |
| PACKAGE_SIZE_UNIT_F | - | VARCHAR2(80) |
| PACKAGE_TYPE_F | - | VARCHAR2(80) |
| Name | Null? | Type |
|---|---|---|
| DRUG_CODE | NOT NULL | NUMBER(8) |
| PHARMACEUTICAL_STD | - | VARCHAR2(40) |
| Name | Null? | Type |
|---|---|---|
| DRUG_CODE | NOT NULL | NUMBER(8) |
| ROUTE_OF_ADMINISTRATION_CODE | - | NUMBER(6) |
| ROUTE_OF_ADMINISTRATION | - | VARCHAR2(40) |
| ROUTE_OF_ADMINISTRATION_F | - | VARCHAR2(80) |
| Name | Null? | Type |
|---|---|---|
| DRUG_CODE | NOT NULL | NUMBER(8) |
| SCHEDULE | - | VARCHAR2(40) |
| SCHEDULE_F | - | VARCHAR2(160) |
| Name | Null? | Type |
|---|---|---|
| DRUG_CODE | NOT NULL | NUMBER(8) |
| CURRENT_STATUS_FLAG | - | VARCHAR2(1) |
| STATUS | - | VARCHAR2(40) |
| HISTORY_DATE | - | DATE |
| STATUS_F | - | VARCHAR2(80) |
| LOT_NUMBER | - | VARCHAR2(50) |
| EXPIRATION_DATE | - | DATE |
| 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_F | - | VARCHAR2(240) |
| TC_AHFS_F | - | VARCHAR2(160) |
| Name | Null? | Type |
|---|---|---|
| DRUG_CODE | NOT NULL | NUMBER(8) |
| VET_SPECIES | - | VARCHAR2(80) |
| VET_SUB_SPECIES | - | VARCHAR2(80) |
| VET_SPECIES_F | - | VARCHAR2(160) |
DPD relationship diagram
DPD relationship diagram: Text description
A diagram outlining the relationship between the QRYM DPD extract files. All extract files are connected via the QRYM_DRUG_PRODUCT file.
Page details
- Date modified: