Home | Using Katmandoo | Quick Start Guide | How-to | Table of Contents | Glossary | See Also | Collapse All

How to: Importing data using single-site Analysed Results file

Overview

This page explains how to use a single worksheet (similar to the dummy example shown below) with AOV results can be used to import
Assumption: Sub-system (in this case "Wheat") and Genus ("Wheat") already exists in the database.

Click here to read important note on importing data.

Example (dummy) Table

Following (dummy) example is used in this page for illustration only.

Crop State Site
Year		 TriaType Trial
Number		 AOV
Region		 Location Postcode Site
Name		 Genotype Trait Name Genotype
Mean		 wt trep ems tmy Statistical Model
Wheat NSW 2000 S4 Early 1 S/E Ariah Park 2665 Site X Genotype1 Grain Yield 5.767 97.04 3 0.0291 5.3175 ASReml
Wheat NSW 2000 S4 Early 1 S/E Ariah Park 2665 Site X Genotype2 Grain Yield 5.463 99.69 3 0.0291 5.3175 ASReml
Wheat NSW 2000 S4 Early 1 S/E Ariah Park 2665 Site X Genotype3 Grain Yield 5.284 107.54 3 0.0291 5.3175 ASReml
Wheat NSW 2001 S4 Early 1 S/E Ariah Park 2665 Site Y Genotype1 Grain Yield 5.021 97.54 3 0.0291 5.3175 ASReml
Wheat NSW 2001 S4 Early 1 S/E Ariah Park 2665 Site Y Genotype11 Grain Yield 5.576 100.1 3 0.0291 5.3175 ASReml
Wheat NSW 2001 S4 Early 1 S/E Ariah Park 2665 Site Y Genotype2 Grain Yield 5.116 107.48 3 0.0291 5.3175 ASReml

Required columns to import data

Following table list required columns for each import options:

Import Option Minimum required columns in the source file
Import Genotype Crop and Genotype
Import Location Location Name and Postcode
Import Site Location Name, Postcode and Site Name
Import Site Year Site Name and Site Year
Import Trial Type TrialTypeName
Import Trial Site Name, Site Year, Trial Type and Trial Number

OR

TrialAcronym
Import Genotype AOV Mean Site Name, Site Year, Trial Type, Trial Number, Genotype, Trait Name, Genotype Mean, Wt, trep, EMS, TMY and Statistical Model

OR

TrialAcronym, Genotype, Trait Name, Genotype Mean, Wt, trep, EMS, TMY and Statistical Model

Step 1 - Import Genotype

Before importing genotype, please ensure that the Crop (or Genus) exist in the database.

Use [ Task > Genotype > Manage Genotype ] to activate the Genotype Main Form. Then use [ Record > Import > Import Genotype ] to open the Import Genotype Wizard.
Illustrative snapshot:
First page of Import Genotype (Click it to change its size)
  1. Select source file to import genotypes.
  2. This checkbox must be ticked because the source file has repeated Genotypes and we want to import unique genotypes only. 
    • In the example table, the "Genotype 1" and "Genotype 2" are repeated more than once. When this checkbox is ticked, the import wizard will import unique genotypes ("Genotype 1", "Genotype 2", "Genotype 3" and "Genotype 11") ie it will ignore the duplicates.
  3. Map column names used in spreadsheet with column name of the database. Click here for the required columns.
Then Click on [ Preview ]  and Click on [ Save Genotype... ] to save the previewed genotypes into database.

Step 2 - Import Location, Site and Site Year

Use Import Location Wizard to import location, site and site year from the AOV Results file. Click here for the required columns.
  1. Follow the steps in Import Location section to import unique locations only.
    • Important Note: The checkbox "If source file.." must be ticked.
  2. Follow the steps in Import Site section to import unique sites only.
    • Important Note: The checkbox "If source file.." and "Allow imported sites to be used in All Sub-systems" must be ticked.
    • Select "Generate New Trial Acornym Suffix" option in the first page of the wizard.
  3. Follow the steps in Import Site Year section to import unique site years only.
    • Important Note: The checkbox "If source file.." must be ticked.

Step 3 - Import Trial Type

Use [ Task > Trial > Raw Data > Manage Trial Type ] to activate the Trial Type Main Form. Then use [ Record > Import > Import Trial Type ] to open the Import Trial Type Wizard.
Illustrative snapshot:
First page of Import Trial Type (Click it to change its size)
  1. Select source file to import trial types.
  2. This checkbox must be ticked because the source file has repeated Trial Types and we want import unique trial types only. 
    • In the example table, the "S4 Early" is repeated more than once. When this checkbox is ticked, the import wizard will import unique trial types ("S4 Early") ie it will ignore the duplicates.
  3. Map column names used in spreadsheet with column name of the database. Click here for the required columns.
Then Click on [ Preview ]  and Click on [ Save Trial Type... ] to save the previewed trial types into database.

Step 4 - Fix Trial Acronym Prefix

The Trial Acronym Prefix must be unque for each trial type in order to import trial from the AOV results file (as shown in the example table).

Fix Trial Acronym Prefix (Click it to change its size)
  1. Unique Trial Type from the Source file which are saved into database.
  2. Unique Trial Acronym Prefix of trial type.

Step 5 - Import Trial

Use [ Task > Trial > Raw Data > Manage Trial, Operation and Raw data ] to activate the Trial Main Form. Then use [ Record > Import > Import Trial ] to open the Import Trial Wizard.
Illustrative snapshot:
First page of Import Trial (Click it to change its size)
  1. Select [ Import Trials ] option (if not selected).
  2. Select source file to import trials.
  3. This checkbox must be ticked because the source file has repeated Trials and we want import unique trials only. 
    • In the example table, the Trial ("Site X", Year 2000, "S4 Early" and Trial Number 1) is repeated more than once. When this checkbox is ticked, the import wizard will import unique trials ("Site X", Year 2000, "S4 Early" and Trial Number 1) ("Site Y", Year 2001, "S4 Early" and Trial Number 1) ie it will ignore the duplicates.
  4. Map column names used in spreadsheet with column name of the database. Click here for the required columns.
Then Click on [ Preview ]  and Click on [ Save Trial... ] to save the previewed trials into database.

Following default values are used for the missing data:
Trial Start Date 1/1/SiteYear (eg 1/1/2000)
Trial End Date 31/12/SiteYear (eg 31/12/2000)
Trial Manager Unknown
Design Type Unknown
Site Manager (owner) Unknown

Step 6 - Import Trial AOV Summary

Use [ Task > Trial > Analysed Data > Manage Analysis of Variance (AOV) ] to activate the Trial AOV Main Form. Then use [ Record > Import > Import Trial AOV ] to open the Import Trial AOV Wizard.

Click here for more informationon Trial AOV Summary.
Illustrative snapshot:
First page of Import Trial AOV Summary (Click it to change its size)
  1. Select [ Import Trial AOV Summary ] option (if not selected).
  2. Select source file to import Trial AOV Summaries.
  3. This checkbox must be ticked because the source file has repeated Trial AOV Summaries and we want import unique Trial AOV Summaries only. 
    • In the example table, the Trial ("Site X", Year 2000, "S4 Early", Trial Number 1, Statistical Mode "ASReml", Trait "Grain Yield" and Trait Instance Number "1") is repeated more than once. When this checkbox is ticked, the import wizard will import unique Trial AOV Summaries it will ignore the duplicates.
  4. Map column names used in spreadsheet with column name of the database. Click here for the required columns.
Then Click on [ Preview ]  and Click on [ Save Trial AOV Summary... ] to save the previewed Trial AOV Summaries into database.

Step 7 - Import Genotype AOV Mean

Use [ Task > Trial > Analysed Data > Manage Analysis of Variance (AOV) ] to activate the Trial AOV Main Form. Then use [ Record > Import > Import Trial AOV ] to open the Import Trial AOV Wizard.

Click here for more informationon Genotype AOV Mean.
Illustrative snapshot:
First page of Import Genotype AOV Mean (Click it to change its size)
  1. Select [ Import Genotype AOV Mean ] option (if not selected).
  2. Select source file to import trials.
  3. Tick this checkbox (default) so that Katmandoo can insert the treatments if they already do not exist!
  4. Map column names used in spreadsheet with column name of the database. Click here for the required columns.
Then Click on [ Preview ]  and Click on [ Save Genotype AOV Mean... ] to save the previewed Genotype AOV Mean into database.

Step 8 - Rebuild Site List for the Sub-system

When the sites were imported, the [ "Allow imported sites to be used in All Sub-systems" ] option was selected. But all the sites will not have data for all the Sub-systems. So, after completion of importing all the analysed result, it's highly recommended to rebuild Site List for each Sub-systems.

Login as administrator user name. Then use [ Task > System > Manage Sub-system ] to open the Sub-system Main Form. Then perform following steps for each Sub-systems.
  1. Select a Sub-system
  2. [ Record > Auto-Rebuild Site List of selected Sub-system ] 
  3. Click on [ Yes ] to continue to rebuild site list.

See Also