Preparing simple Excel spreadsheets for importing data into the LTRAS database

• automatically sorts data by crop (corn vs. wheat) and method (combine- vs. hand-harvest)
• automatically links related data (yields from dataset 217 and related N analyses from dataset 293)
• automatically sorts data by system (even if data were entered in some other order)
• calculates mean and SD by system.

Don't guess -- read these instructions!
Everyone who has tried to guess how to do this without bothering to read the instructions has made serious errors.
It is much harder to correct a mistake than it is to do it right the first time.
So, please work through the example below, following the instructions, before working with real data.  You may then want to print these instructions and check things off as you do them, especially the first few times.

Most of this page refers to a simple example: a dataset with only one Variable (Grain-Yield) and one Date ('6/26/00), and without any Sort parameter (such as soil depth).  The master copies of the database and associated spreadsheets are saved on the Agronomy server, but you can work with copies on another computer, if you like.

1) Open the spreadsheet you want to import and decide which Variables (Grain-Yield, Soil-NO3-N, etc.) need to be imported.  For practice...

download before.xls by clicking the link, then save the practice file in MyDocs

doubleclick MyDocs\before.xls to launch Excel and open practice file.

2) Save the spreadsheet as an Excel 5 workbook (Approach can't import later versions of Excel) in MyDocs.
If you were saving real data for 1995, it would go in the Agronomy server in the following directory:  S:\Denison\DenisonLab\LTRASdata\1995.

3) Add a new worksheet (Insert/Worksheet) to your Excel spreadsheet, where you will put data in the format needed for import into database. Right-click tab at bottom and Rename it Database.  Drag it to the left.

4) Label columns in the new worksheet: Dataset, Plot, Value.

5) Copy plot numbers into the Plot column as references, so that any corrections to the spreadsheet will be updated automatically.  This also eliminates error-prone retyping of numbers. The details will vary among spreadsheets.  In our example:

• Click in cell B2 (first plot number) of the worksheet you labeled Database.
• Type  =
• Click the first plot number cell (e.g., worksheet wt grain yield and cell A8) to create reference link
• Press Enter -- you should see plot number 1-7 entered into cell B2 of Database sheet
• Click cell B2 and drag its lower right corner down to transfer the rest of the plot numbers.  Unfortunately, the example file contains some blank lines, which end up as zeros.  We will delete them below.
• For split plots, use E and W to indicate which half of plot was sampled.  Example:  2-7W


6) Find out what our standard units are for the each variable to be imported.  Huge errors can occur if you simply assume spreadsheet is already in our standard units.  If you can access the LTRAS database on the Agronomy server, leave the spreadsheet open while you do the following steps to find our standard units for your variable.
(Otherwise, email rfdenison@ucdavis.edu to find out about standard units for your variable and skip to step 4.)

• In WindowsExplorer, doubleclick S:\Denison\DenisonLab\LTRASdata\NEWDSET.APR to launch database program Approach97 and open the file we use to provide information about new datasets ready to be entered into database.
• Use password Public
• If you get the worksheet, click the Form1 tab(upper left) to see the dataset form
• Click new record icon (white card) if Form 1 is not blank
• Select variable (e.g. Grain-Yield) from DefaultVariable drop-down list
• Click somewhere else on the form and standard units will be displayed.  Write them down.
• Leave NEWDSET.APR open; we will come back to it to define our new dataset.


7) Convert to our standard units and then copy data as references into the Value column of worksheet Database by a similar process.  Our example already has a column with data in kg/ha, our standard units.  But to illustrate the process we will use data in lb/acre from another column.

• Click in cell C2, type =, then click in first data cell (e.g., worksheet wt grain yield cell N8) to enter reference to data in lbs/acre -- don't press Enter yet.
• Before pressing Enter, modify equation line to read ='wt grain yield'!N8*1.12 then press Enter.
• Note: multiplying by 1.12 converts lb/acre to kg/ha.
• Note that cell C2 in sheet Database now contains the yield in kg/ha, our standard units.
• Click a drag down from cell C2 to fill Value column with corrected data values.

 
8) Fill Dataset column with the number from upper right corner of your NEWDSET.APR form.  (If you can't access the LTRAS database on the Agronomy server, leave this column blank.)

9) Delete rows without data (caused by blank lines in spreadsheet) in Database sheet by right-clicking each row number and selecting Delete (not Cut or Clear contents).

10) Check your spreadsheet (workbook) before saving it.  Practice worksheet should look like this.
If not, ask for help now.

11) Fill out the requested information in the NEWDSET.APR form you opened earlier, including DefaultDate, which is the date of field harvest or sampling, not date of sample analysis.   Since you haven't defined Variable in the spreadsheet, you also need to select Grain-Yield as the DefaultVariable if you didn't already do so.  Exit Approach.  (If you are not connected to the Agronomy server, fill out this same information on a hardcopy of the form.)

More complicated datasets

If your dataset contains data for more than one date, add a column labeled Date to your Excel 5 file.  In order for dates to import properly into Approach, precede each date in the Excel 5 file with a single quote: '10/21/1993

If more than one variable was measured at the same time (or on the same sample, whenever the analysis was done), add a column labeled Variable.   For example, Soil-NH4-N and Soil-NO3-N are often measured at the same time.  It's OK to put these in separate datasets -- Approach will match them based on date and plot number -- but it may be simpler to put them in one dataset.  In that case, the Variable column indicates whether the Value in a given row of the Excel 5 spreadsheet is for nitrate or ammonium. Don't make up new names for existing variables!

A Sort column is used if there are data for the same Plot, Date and Variable that should not be averaged together.  For example, soil data could be sorted into values for 0-15 cm versus 15-30 cm.  The Sort column is also used to indicate weed species, using standard 5-letter codes.