Health Canada
Symbol of the Government of Canada
Food and Nutrition

Canadian Nutrient File

Compilation Of Canadian Food Composition Data

2010 Database Structure

Nutrition Research Division
Health Products and Food Branch
Health Canada

Help on accessing alternative formats, such as Portable Document Format (PDF), Microsoft Word and PowerPoint (PPT) files, can be obtained in the alternate format help section.

Files Structure and Table of Contents

Introduction

The downloadable files comprising the Canadian Nutrient File database are unaccompanied by software. They are relational files available in three formats: delimited ASCII text files, DBF files and CSV files. In order to view and utilize these files one must both own and be familiar with a database management software program. Examples on the market include Microsoft Access, DBASE, FoxPro, Lotus Approach and many others. Spreadsheet programs such as Excel are not large enough to open most of these files and files become truncated.

To save space and speed up downloads; the files are zipped into one large file with a file name ending with '.exe'. This is the file that is downloaded to whatever directory the user specifies. After downloading, go to that directory and double click on the exe file; this will extract the many files comprising the CNF database.

Some database management programs require you to "Open" these files into their programs while others require them to be "Imported". Once they are imported, the corresponding tables can be joined using the common fields indicated in the File Relationships section below.

New for this Edition

1- New Food Code

Over time, CNF users tend to get used to the numeric code associated to certain foods, and start to refer to these foods using their code. Since 2001, the only published numeric identifier was a number used internally by our database as the primary key that uniquely identifies foods and links them to related data fields (FD_C in the FOOD_NM table).

However, in 2008, it was necessary to merge the CNF database with another internal Health Canada database, which caused many of the primary keys to change. We will no longer use the primary key as the unique CNF food code.

This prompted us to modify the name of the FD_C field. It is now the primary key and all primary key fields are ending with _ID (FC_ID) in the CNF database in order to be easily identified. We also introduced a new field that we name the FD_CODE, this being the unique food code that is not the primary key.

New Field Structure and Roles
Field Description of Field New role in the database Will appear in the web Search application
FD_C is now FD_ID Food ID field or food identifier This is the primary key and remains the link for joining related tables No
FD_CODE Food code This will be the new user food code Yes

As a convenience and whenever possible for previously-published foods, the old food code FD_C (in the 2007b database) and the new food code FD_CODE (in >2010 versions) will not be different. They will just be stored in a different field which is no longer the primary key.

In order to facilitate the ease of use of the tables, all primary key field names are ending with _ID

It is recommended that the food ID and primary key (now the FD_ID) be used only to link tables and that the food code FD_CODE be displayed.

Remember that all of the nutrient data is stored per 100 g of the food. If you want to manipulate the data to display weights per portion, this can be done through some sophisticated calculated field programming.

If you are not familiar with these terms you may want to buy a nutrient analysis software program which features 2010 CNF data.

Note: ASCII text files are delimited by a $ sign

2- Withdrawal of Eating Well with Canada's Food Guide Groupings and Serving Sizes

Portion size measures which relate Canada's Food Guide to Healthy Eating (CFGHE)Footnote 1 food grouping principles to each of the CNF foods were added to the 2005 version of the database. Some of these assignments are in the process of being updated to reflect revisions recently published under the new title Eating Well with Canada's Food Guide (CFG)Footnote 2 Therefore, for the present we are not publishing these assignments in the 2010 version of the CNF.

Interested parties are encouraged to go to the website of the Office of Nutrition Policy and Promotion at Health Canada. They have developed a new website where all of the information regarding the Food Guide can be located.

2010 File Relationships

Update Files
Add   Change   Delete
  • Food_Add
  • FD_ID
  • FD_CODE
  • FD_GRP_ID
  • FD_SRC_ID
  • A_FD_NME
  • A_FD_NMF
  • L_FD_NME
  • L_FD_NMF
  • COUNTRY_C
  • FD_DT_ENT
  • FD_DT_PUB
  • SCI_NM
  • MEASU_A
  • MSR_ID
  • MSR_NME
  • MSR_NMF
  • FOOD_CHG
  • FD_ID
  • FD_CODE
  • FD_GRP_ID
  • FD_SRC_ID
  • A_FD_NME
  • A_FD_NMF
  • L_FD_NME
  • L_FD_NMF
  • COUNTRY_C
  • FD_DT_ENT
  • FD_DT_PUB
  • SCI_NM
  • CF_CHG
  • FD_ID
  • MSR_ID
  • CONV_FAC
  • CF_DT_ENT
  • FOOD_DEL
  • FD_ID
  • A_FD_NME
  • A_FD_NMF
  • REFU_ADD
  • FD_ID
  • REFUSE_ID
  • REFUSE_AMT
  • CF_DT_ENT
  • NT_AMT_D
  • FD_ID
  • NT_ID
  • NT_COD
  • NT_NM_A
  • NT_ID
  • NT_COD
  • NT_SYM
  • UNIT
  • NT_NME
  • NT_NMF
  • TAGNAME
  • NT_DEC
  • YLD_ADD
  • FD_ID
  • YLD_ID
  • YLD_AMT
  • CF_DT_ENT
  • NT_AMT_C
  • FD_ID
  • NT_ID
  • NT_VALUE
  • STD_ERR
  • NUM_OBS
  • NT_SRC_ID
  • NT_DT_ENT
  • NT_TR
  • YLD_CHG
  • FD_ID
  • YLD_ID
  • YLD_AMT
  • CF_DT_ENT
  • REFU_DEL
  • FD_ID
  • REFUSE_ID
  • NT_AMT_A
  • FD_ID
  • NT_ID
  • NT_VALUE
  • STD_ERR
  • NUM_OBS
  • NT_SRC_ID
  • NT_DT_ENT
  • NT_TR
  • YLD_NM_A
  • YLD_ID
  • YLD_NME
  • YLD_NMF
  • NT_NM_C
  • NT_ID
  • NT_COD
  • NT_SYM
  • UNIT
  • NT_NME
  • NT_NMF
  • TAGNAME
  • NT_DEC
  • REFU_CHG
  • FD_ID
  • REFUSE_ID
  • REFUSE_AMT
  • CF_DT_ENT
  • YLD_DEL
  • FD_ID
  • YLD_ID
  • CF_ADD
  • FD_ID
  • MSR_ID
  • CONV_FAC
  • CF_DT_ENT
  • REFU_NM_A
  • REFUSE_ID
  • REFUSE_NME
  • REFUSE_NMF
  • MEASU_CHG
  • MSR_ID
  • MSR_NME
  • MSR_NMF
 
  • CF_DEL
  • FD_ID
  • MSR_ID
  • NT_SRC_A
  • NT_SRC_ID
  • NT_SRC_COD
  • NT_SRC_NME
  • NT_SRC_NMF
  • FD_SRC_A
  • FD_SRC_ID
  • FD_SRC_COD
  • FD_SRC_NME
  • FD_SRC_NMF
   
  • MEASU_DEL
  • MSR_ID
  • MSR_NME
  • MSR_NMF
       
  • NT_NM_D
  • NT_ID
  • NT_COD
  • NT_SYM
Description of Files
Name File Name File Description
Food Name FOOD_NM This is a principal file. It stores information about each food in the database. It contains a description of each food in English and French as well as dates and comments.
Nutrient Amount NT_AMT This is the main file. It uses information (by linking) from the FOOD_ NM table (among others) to identify which nutrients and amounts are recorded for that food.
Conversion Factor CONV_FAC This is a principal file. This file contains portion size conversion factors. The conversion factors are food specific multipliers by which the nutrient values for each food may be multiplied to give the nutrients in described portions.
Refuse Amount REFUSE This is a principal file. This file contains the percent of refuse, or inedible portion, for each food.
Yield Amount YIELD This is a principal file. This file contains the yield from refuse and/or cooking losses assigned to certain foods. These yields are most often used for food inventory purposes.
Food Group FOOD_GRP This file is a support or "list" table that is used to link to the FOOD_NM table. It contains a list of 23 different group headings (in English and French) based on similar characteristics of the foods.
Food Source FOOD_SRC This file is a support or "list" table that is used to link to the FOOD_NM table. It contains a list of several food sources (in English and French) that foods can be grouped on.
Nutrient Name NT_NM This file is a support or "list" table that contains the list of nutrients (in English and French) used in the NT_AMT file, with which it is linked.
Nutrient Source NT_SRC This file is a support or "list" table that is used to link to the NT_AMT file. It contains a list of several sources and/or types of nutrient data (in English and French).
Measure MEASURE This file is a support or "list" table that is used to link to the CONV_FAC table. It contains a list of measures (in English and French).
Refuse Name REFU_NM This file is a support or "list" table that is used to link to the REFUSE table. It contains a list of refuse types.
Yield Name YLD_NM This file is a support or "list" table that is used to link to the YIELD table. It contains a list of yield types or yield descriptions (in English and French).

Fields And Linkages

Principle Files

I. Food Name File:

File name: FOOD_NM
Field Name Field Type Size Field Description Links
FD_ID N 11 Sequential number generated by the database identifying the food records. This is the primary key. NT_AMT CONV_FAC YIELD REFUSE
FD_CODE N 8 Identifier code for the foods. In the 2007b version this was the FD_C N/A
FD_GRP_ID N 11 Sequential number generated by the database for the food groups FOOD_GRP
FD_SRC_ID N 11 Sequential number generated by the database for the food sources. FOOD_SRC
A_FD_NME T 200 Abbreviated food name in English N/A
A_FD_NMF T 200 Abbreviated food name in French N/A
L_FD_NME T 255 Complete food name in English N/A
L_FD_NMF T 255 Complete food name in French N/A
COUNTRY_C N 20 Corresponds to the USDA NDB code N/A
FD_DT_ENT T dd/mm/yyyy Date the food name data was entered into the database N/A
FD_DT_PUB T dd/mm/yyyy Date the food name data was originally published N/A
SCI_NM T 100 Scientific name of the food N/A

II. Nutrient Amount File:

File name: NT_AMT
Field Name Field Type Size Field Description Links
FD_ID N 8 Sequential number generated by the database identifying the food records. FOOD_NM
NT_ID N 4 Sequential number generated by the database for the nutrient names NT_NM
NT_VALUE N 12/5 Mean value in 100g, edible portion. (The number of decimal places does not reflect the accuracy of the data.). N/A
STD_ERR N 8/4 Standard error of the mean N/A
NUM_OBS N 6 Number of samples N/A
NT_SRC_ID N 15 Identifier code generated by the database for the nutrient source NT_SRC
NT_DT_ENT T dd/mm/yyyy Date the data was entered into the database N/A
NT_TR T 1 Trace nutrient identifier. The nutrient is likely present, but at a level below significant contribution to the diet or beneath a level that can be measured adequately. N/A

III. Conversion Factor File:

File name: CONV_FAC
Field Name Field Type Size Field Description Links
FD_ID N 8 Sequential number generated by the database identifying the food records. FOOD_NM
MSR_ID N 10 Sequential number generated by the database for the measure descriptions MEASURE
CONV_FAC N 9/5 The factor by which one would multiply the nutrient per 100g to obtain nutrient amounts per the measure described (the weight of that food in the measure described divided by 100) N/A
CF_DT_ENT T dd/mm/yyyy Date the data was entered into the database N/A

IV. Refuse Amount File:

File name: REFUSE
Field Name Field Type Size Field Description Links
FD_ID N 8 Sequential number generated by the database identifying the food records. FOOD_NM
REFUSE_ID N 10 Sequential number generated by the database for the refuse descriptions REFU_NM
REFUSE_AMT N 9/5 Percent refuse N/A
CF_DT_ENT T dd/mm/yyyy Date the data was entered into the database N/A

V. Yield Amount File:

File name: YIELD
Field Name Field Type Size Field Description Links
FD_ID N 8 Sequential number generated by the database identifying the food records. FOOD_NM
YLD_ID N 10 Sequential number generated by the database for the yield descriptions YLD_NM
YLD_AMT N 9/5 Yield from refuse and/or cooking losses N/A
CF_DT_ENT T dd/mm/yyyy Date the data was entered into the database N/A

Support Files:

A. Food Group File:

File name: FOOD_GRP
Field Name Field Type Size Field Description Links
FD_GRP_ID N 15 Sequential number generated by the database for the food groups FOOD_NM
FD_GRP_COD N 15 Identifier code for the Canadian Nutrient File food groups. There are 23 different CNF food groups N/A
FD_GRP_NME T 200 Food group name in English N/A
FD_GRP_NMF T 200 Food group name in French N/A

B. Food Source File:

File name: FOOD_SRC
Field Name Field Type Size Field Description Links
FD_SRC_ID N 2 Sequential number generated by the database for the food sources FOOD_NM
FD_SRC_COD N 15 Identifier code for the food sources. Food sources give an indication as to Canadian content N/A
FD_SRC_NME T 200 Food Source description in English N/A
FD_SRC_NMF T 200 Food Source description in French N/A

E. Nutrient Name File:

File name: NT_NM
Field Name Field Type Size Field Description Links
NT_ID N 4 Sequential number generated by the database for the nutrient names NT_AMT
NT_COD N 15 Identifier code for nutrient name descriptions N/A
NT_SYM T 10 Nutrient symbol or abbreviation of the nutrients. They may differ from international nomenclature. N/A
UNIT T 8 Unit of measure (e.g., mg, g, mcg) N/A
NT_NME T 200 Nutrient name in English N/A
NT_NMF T 200 Nutrient name in French N/A
TAGNAME T 20 International Network of Food Data Systems (INFOODS) Tagnames. A unique Abbreviation for a food component developed by INFOODS to aid in the interchange of data. N/A
NT_DEC N 15 Number of decimal places used in the rounding of the nutrient value N/A

F. Nutrient Source File:

File name: NT_SRC
Field Name Field Type Size Field Description Links
NT_SRC_ID N 15 Sequential number generated by the database for the nutrient sources NT_AMT
NT_SRC_COD N 15 Identifier code for nutrient source descriptions. N/A
NT_SRC_NME T 200 Nutrient source description in English N/A
NT_SRC_NMF T 200 Nutrient source description in French N/A

G. Measure File:

File name: MEASURE
Field Name Field Type Size Field Description Links
MSR_ID N 10 Sequential number generated by the database for the measure descriptions CONV_FAC
MSR_NME T 200 Measure description in English N/A
MSR_NMF T 200 Measure description in French N/A

H. Refuse Name File:

File name: REFU_NM
Field Name Field Type Size Field Description Links
REFUSE_ID N 10 Sequential number generated by the database for the refuse descriptions REFUSE
REFUSE_NME T 200 Refuse description in English N/A
REFUSE_NMF T 200 Refuse description in French N/A

I. Yield Name File:

File name: YLD_NM
Field Name Field Type Size Field Description Links
YLD_ID N 10 Sequential number generated by the database for the yield descriptions YIELD
YLD_NME T 200 Yield description in English N/A
YLD_NMF T 200 Yield description in French N/A
N = Numeric - # of characters / # of decimals (if decimal)
T = Text
N/A = Not applicable

Update Files

For the 2010 version we are offering update files that track records that have been changed, added or deleted since the release of the 2007b version of the CNF.

A. Added Files
Field Name Field Description Comment
FOOD_ADD Added food names  
NT_NM_A Added nutrient names  
NT_AMT_A Added nutrient amounts Includes nutrient values added to already existing foods as well as new foods
MEASU_A Added measures  
CF_ADD Added conversion factors Includes conversion factors values added to already existing foods as well as new foods
REFU_ADD Added refuse  
YLD_NM_A Added yield names  
YLD_ADD Added yields Includes yields added to already existing foods as well as new foods
REFU_NM_A Added refuse names  
FD_SRC_A Added food sources  
NT_SRC_A Added nutrient sources  
These files are in the same format as the Principal or Support Files.
B. Changed Files
Field Name Field Description Comment
FOOD_CHG Changed food names  
NT_NM_C Changed nutrient names  
NT_AMT_C Changed nutrient amounts  
CF_CHG Changed conversion factors  
YLD_CHG Changed yields  
REFU_CHG Changed refuse  
MEASU_CHG Changed measures  
If the values in any fields have changed, the entire record is included for that file. These files are in the same format as the Principal or Support Files. Unfortunately, simple changes to the wording or spelling of the food names were not captured in the food change update file for this edition.
C. Deleted Files
Field Name Field Description Comment
FOOD_DEL Deleted food names  
NT_AMT_D Deleted nutrient amounts Includes nutrient values that were removed from the database (including those which accompany a deleted food
CF_DEL Deleted conversion factors Includes conversion factors that accompany a deleted food (including those which accompany a deleted food
YLD_DEL Deleted yields  
REFU_DEL Deleted refuse  
MEASU_DEL Deleted measures  
NT_NM_D Deleted nutrient names  
Although every attempt has been made to include all additions, changes and deletions to the CNF since the last release in these update files, the nature of this very large electronic database and the complex programming involved in editing, it is possible that some changes were not tracked accurately. It is recommended that one also downloads the entire database for reference.

References

Footnotes

Footnote 1

Health Canada. 1992. Canada's Food Guide to Healthy Eating. Minister of Public Works and Government Services Canada.

Return to footnote 1 referrer

Footnote 2

Health Canada. 2007. Eating Well with Canada's Food Guide.

Return to footnote 2 referrer