Importing Data

CSV Data with sire and dam names

This is the easiest type of data to import. The basic procedure is to open up your existing database in PedPub or PedX and open the CSV data in Excel. Shown below is a screen shot of PedPub.

The screen shot below shows Excel with a sample CSV file open. Note that the Sire and Dam are there as text rather than registration numbers or others numbers.

The first step in loading the data is to move the columns in Excel so they match up with the ones in Breedmate or use the Field Choose in breedmate to move Breedmate's columns around so they match. You will also need to ensure that the dates are in the same format.You can change the format the PedPub uses by using the TOOLS-Alter Database command, click on the the DOB Format cell and select the appropriate format from the drop down as shown below

Note that if moving columns in Excel you may need to insert blank columns if Breedmate has a column that is not in the CSV data in Excel. To move a column in Excel, right click in the top of the column to select the entire column and select "Cut" from the context menu. Then right click on the column where you want the cut column to go and select "Insert cut cells". The screen shot below shows the Excel file after the columns have been moved and a Sex column inserted.

The final steps are left click in the first column in Excel (this should be the Name column) and holding the mouse button down drag across to select all the columns you want to import. Note there may be other columns to the right which you don't want to import. Then press Ctrl+C to copy the cells to the clipboard. Now switch over to Breedmate and use the EDIT-Insert Records command. The screen shot below shows PedPub after the CSV data is pasted in.

CSV Data with sire and dam numbers

The procedure for doing this is very similar to the previous example except that the first step has to be to convert the sire and dam number to names. In the screen shot below we can see a portion of a CSV file that uses numbers.

The two columns at the end are the sire and dam names and they were created using a formula. The formula uses the VLOOKUP function. It has four arguments =VLOOKUP(value, table, col_index, match):

  • value - The value to look for in the first column of a table. This cell reference should be the sire or dam number.
  • table - The table from which to retrieve a value. The first column of that table must be the number column. The last column doesn't have to be the last column in the data but it must be up to or after the name column. In our example the table is the A to C columns. The C column is the Name column. We could have defined the table as going up to the DE , E or F column but that would be pointless. Also note when defining the tables row range we used a $ symbol in front of the row numbers to make them absolute. That way when we extend them down or copy them to other rows those row numbers won't change.
  • col_index - The column in the table from which to retrieve a value. This is 1-based so if the Name column which is C of a table which is defined as being columns A, B, C will be at index 3.
  • approximate match - always set to FALSE so it must be an exact match.

So in our example the formula for the sire name in row 2 is "=VLOOKUP(G2,A$2:C$16343,3,FALSE)" whereas the dam names formula is "=VLOOKUP(H2,A$2:C$16343,3,FALSE)". NOTE: the range on the table is A$2:C$16343 NOT A2:C16343. You MUST have the $ before the row number, otherwise when you copy the formula down to other rows the row number will be incremented and we don't want that otherwise the table will start from progressively lower rows.

After verifying that the lookup is correct, select the sire and dam names cell with the formulas then drag them down for all rows.

You can now proceed as in the previous example by moving the sire and dam name columns to the correct position. Make sure not to delete the sire and dam number columns, just move them off to the right hand side and out of the way. When you eventually copy the data to the clipboard you will not select those columns.