Import Ingredient data from an Excel document

Review your Excel document

The first step is to review your Excel spreadsheet. (An example .xlsx file is attached to this page for your reference.)

Make sure cell values are formatted as numbers, general or date. The Import Wizard reads all numerical values as numbers. So, for example, if the field is defined in excel as percent, the program will read 100% as 1, which is probably not what you intend. The Import Wizard will map your columns to existing program fields or create new ones.

1. Go to Database > Import

2. Browse and select your Excel file.

3. Click OPEN. This will automatically open the Import Wizard

4. Click Ingredient to move to the next screen. 

Select Rows 

1. Select the header row. This will be the row with the data field names in it, like Name, Amount, Supplier, etc. (Most of the time it’s the top row, so the wizard automatically selects the top row.)

2. Click Next.

3. Select which data rows you wish to import.

4. Click Next

5. Set up the field mapping.

Open

Field Mapping

1. Map Ingredient Name and Defining Amount. In order to continue with the import, you MUST have your Ingredient Name and defining amount mapped. If you do not, red Xs will appear in the top section and you will not be allowed to complete the import.
   
   Note on Defining Amount: Nutrient values are reported per this amount. If the defining amount (eg: 100 grams) is the same for all ingredients included in the import file, enter it in the Assign a defining amount field in the top section. If the defining amount varies for ingredients in the import file, the field(s) can be added to the import spreadsheet and the information added during the drop in-process.

2. Map remaining fields. The mapping data appears in columns. The mapped fields will be in the order listed on the import spreadsheet. The program will attempt to map fields with the same name. Program mapped fields should be checked for accuracy. The import wizard will remember mapped fields to assist with future imports. Mapped fields are designated by a green checkmark. Unmapped fields are designated with a red X. Not all fields need to be mapped for the file to import successfully, however, the data for unmapped fields will not be imported.

To map a field

1. Click the link under the Mapped Field column. (It will say "ignore" if nothing has been mapped.)

2. Click the radio button next to the type of field to be mapped (Nutrient, Allergen, User-added field or Ingredient Attribute.) Sometimes the Wizard will find the match right away. Click “show all” to see all fields that are available to map.
   
   

   Open

   

3. Select the best option.

4. When you've finished mapping the fields, click Next.

Mapping Moisture (in grams)

The Moisture field can either be mapped as a “New Nutrient field” (in grams), mapped to the existing “Water” field, or not mapped at all (“Ignore”).

• New Nutrient (recommended): If you map moisture as a new component, map it as inactive and select the unit (likely grams or %).

• Existing Water field (use only if your end products have zero moisture): If moisture is mapped to the existing Water field, the program will automatically map it as inactive with grams as the mapped unit. Within the program, water is removed from the ingredient weight on the formula screen. Again, this is useful only if you are working with hard tablets or other products where there is zero moisture in the end product.

• Not mapped: If you do not wish to map the moisture field it can be skipped in the import process. Choose Ignore as the Mapped Field.

Validation

This page will show you if you have any errors that would cause the import to fail. Example: Name conflict. This would indicate that you have an existing database item with the same name. 

If the Validation says everything is OK, click Next to begin the import.

Import

This will tell you when the Import is complete. Click Exit to return to the Genesis Supplements program. Click New Import to choose another file to import. Click Search to return to the program Open search page.

On this page:

Open

COLUMN DETAILS

Mapped

• Red X indicates an unmapped fields

• Green check indicates a mapped field.

Original field name

• What appears on the header row of the import spreadsheet

Mapped field

• Field names from the software

Active

• A check in the Active box indicates that the component is active. Unchecking the box indicates that the component is inactive.

Mapped unit

• Some fields require a unit of measure. This shows up here.

Default value

• Null (no value) is the default value for the fields. This can be overwritten

Sample data

• Click on the right/left arrows to scroll through the ingredient data listed in the spreadsheet

FIELD TYPES DEFINED

Nutrients
This section lists all nutrient components (and other components) available in the program. Search for an item in the search box or scroll to see all of the items in a list. Click on one of the items listed to select it. To add the name listed in the Original Field as a new nutrient component, select New Nutrient. Numbers and zeroes will display in the fields as entered. Blank fields are seen as Null (no data).

• Calories: This value will show in the ingredient Overrides section.

• Mapped Unit
  When an existing nutrient component is mapped the default mapped unit will be the unit reported on the label or a commonly reported unit. This can be changed. When a new nutrient component is mapped, the default unit will be percent. If ppm or CFU/g are chosen, the new component will be added to the program as a contaminant.

Allergens
This section lists all of the allergens available in the program. Search for an item in the search box or scroll to see all of the items in a list. Click on one of the items to select it. To add the name listed in the Original Field as a new allergen, select New Allergen. In the excel spreadsheet, an “x” in the field designates that the allergen is present. The following field inputs may be used to designate that the allergen is not present: blank, 0, f, no, null, none, false, inactive.

Groups
This section lists all of the groups available in the program. Search for an item in the search box or scroll to see all of the items in a list. Click on one of the items to select it. To add the name listed in the Original Field as a new group, select New Group. In the excel spreadsheet, an "x" in the field designates that the ingredient is in that group. The following field inputs may be used to designate that the ingredient is not in the group: blank, 0, f, no, null, false, inactive.

User Added Fields
This section lists all of the user added fields available in the program. Search for an item in the search box or scroll to see all of the items in a list. Click on one of the items to select it. To add the name listed in the Original Field as a new user added field, select New User Added Field.

Ingredient Attributes
This section lists all of the ingredient attributes available in the program. Search for an item in the search box or scroll to see all of the items in a list. Click on one of the items to select it. Available ingredient attributes are listed below

• Allergen Statement: This will import into the Custom Allergen Statement field.

• Concentration: The concentration of the herbal ingredient (eg: 1:4). Enter this information into the spreadsheet as text.

• Cost: Cost must be entered as three fields, the Cost, Cost Quantity and Cost Unit. For the Cost field, enter the cost amount. For example if the cost is $50.00 per 1 kg, enter "50.00" into this field.

• Cost Quantity: The number of units that reflect the cost amount. For example if the cost is entered per 1 kg, enter "1" into this field.

• Cost Unit: The unit that reflects the cost amount. For example if the cost is entered per 1 kg, enter "kg" into this field.

• Created: The ingredient create date. 

• Defining Amount: This is entered as Defining Amount (one field) or as Defining Amount Quantity and Defining Amount Unit (two fields).

  • Defining Amount: Enter the defining amount for the file (eg: 100.00). When prompted select the unit that corresponds to the defining amount.

  • Defining Amount Quantity: Enter the defining amount (eg: 100).

  • Defining Amount Unit: Enter the unit that corresponds to the amount entered into the Defining Amount Quantity column.

• Latin Name: The Latin name of the herbal ingredient

• Name: The name of the ingredient

• Notes: A free form text field. This information will be listed in the Notes field in the ribbon

• Percent Organic: Enter a number (not a percentage). For example, for 100%, enter 100.

• Product Name: This field further describes the Supplier Name field. This information will show in the Supplier field as “Product Name by Supplier Name”

• Shelf Life (days): Enter a number.

• Shelf Life (years): Enter a number

• Source: Enter a source name. Either match a name in the program drop down menu or create a new name.

• Supplier Name: Enter the supplier name.

• User Code: Enter a user code. These codes must be unique.