Maize: Import Germplasm Lists from Excel Files

BMS 3.0 Tutorials

Contributors

Graham McLaren & Shawn YarnesThe Integrated Breeding Platform

Summary

This tutorial describes the import of three germplasm lists from Excel into the Breeding Management System Database.?

Video: Import Germplasm from Excel File

Introduction

Importing germplasm lists from Excel spreadsheets into the program database requires appropriate formatting using a Germplasm Import Template (.xls).  All germplasm entries in the BMS are matched to unique germplasm IDs (GID). Imported germplasm is given new GIDs if no matches are made with existing entries. 

Restore from Previous Tutorial

Screenshots in this tutorial reflect lists made in the previous maize tutorial, Create Germplasm List from Database.

  • If you have not completed the previous tutorial, restore the Maize Tutorial program (.sql) to populate the Maize Tutorial program with previous data.
    Restoration File??: Restore Maize Tutorial 1.0 (.sql)

List 1: H2H Check List 

Description Sheet

As you consider your own breeding program, you will want to develop a list naming convention to track germplasm lists in an organized and intuitive way. 

  • Enter list details into the Description sheet as illustrated below. The list name must be unique to the program database. This file name describes a check list for a head-to-head query
    • Name: H2H Check List
    • Description: A list of standard entries for use in H2H comparisons
    • Date: 20040101
    • Type: LST

Observation Sheet

The columns ENTRY and DESIGNATION in the observation sheet are mandatory columns, and represent the minimum information needed to import a germplasm list. ENTRY must contain a sequence number (1, 2, 3…) and DESIGNATION contains the name of each germplasm entry. 

  • Fill in the Entry and Designation columns as illustrated below. 

  • Save the modified template as a new file, H2HCheckList.xls, in a directory that you can find again.

Import File 

  • Launch Import Germplasm under Information Management in the left menu or by selecting import from the List Manager options. 

  • Browse to the newly created file, H2HCheckList.xls.

  • Click NEXT to read the file and check its format. 

Specify Germplasm Details

  • Review import file and specify germplasm list details. Browse the full range of options in the dropdown menu or start typing to narrow the options with possible matches. If favorite methods and locations have been specified, narrow the menu options by selecting the Show only favorites boxes.
    • Breeding Method: Unknown derivative method
    • Location: CIMMYT Harare for the location. 
    • Germplasm Name Type: Line Name

  • Match imported germplasm to the pedigrees of existing germplasm whenever found. Check the box to automatically accept single matches whenever found, otherwise you will be asked to confirm each selection manually. Select Finish.

Save

  • Review the contents of the germplasm List Details page and click Save.

List 2: MT2010FP

  • In Excel open the pre-prepared file, MT2010FP.xls, that describes a male parent list 93 entries long.
    Example File: MT2010FP.xls
  • Review the description sheet describing the Maize Tutorial 2010 female parent list, MT2010FP.

  • Review the observation sheet, which contains detail of 93 lines, including CROSS and SOURCE data. Notice that the GID column is filled zero values. The Breeding Management System will read zeros in the GID column as empty, but entering zeros for empty values is not required. 

Import File

  • Import the Maize Tutorial female parent list, MT2010FP.xls, into the BMS database.
    Example File?: 
    MT2010FP.xls

Specify Germplasm Details

Since the pedigree option is set to select imported germplasm to existing germplasm whenever found, the BMS will attempt to match the imported germplasm to similarly designated germplasm in the database. If a questionable match is identified, the user will be asked to choose from a list of possible matches.  

  • Specify the same germplasm list details and pedigree options described previously in this tutorial. Select Finish.
  • Choose the germplasm with GID 195314 and select Continue.

Save

  • Review the list name and description and Save the list to the database. 

  • When all the entries are loaded the “Germplasm List Details’ form appears and you can click Save to save the list.

List 3: MT2010MP

  • Import the Maize Tutorial 2010 male parent list, MT2010MP.xls.
    Example File: MT2010MP.xls

Specify Germplasm Details

  • Specify the same germplasm list details and pedigree options described previously in this tutorial. Select Finish.

Save

  • Review the list name and description and Save the list to the database.

Review Lists

  • Launch the List Manger and Browse the Maize Tutorial program database.

  • Select the newly imported MT2010MP and close the popup window.

  • Review the list details. Positive GIDs indicate that imported germplasm were matched to the public database. Negative GIDs indicate that no match was found in the public database and that a new entry was made, unique to the maize tutorial program database.

Funding & Acknowledgements

The Integrated Breeding Platform (IBP) is jointly funded by: the Bill and Melinda Gates Foundation, the European Commission, United Kingdom’s Department for International Development, CGIAR, the Swiss Agency for Development and Cooperation, and the CGIAR Fund Council. Coordinated by the Generation Challenge Program the Integrated Breeding Platform represents a diverse group of partners; including CGIAR Centers, national agricultural research institutes, and universities. 

Creative Commons License

This work is licensed under a Creative Commons Attribution-Share Alike 4.0 International License.