Testwork: Batch entry
Contents
Entering Testwork Results as a Batch
Test results are often transmitted from laboratories in spreadsheet form with several samples presented in a summary table. Entering individual results for each sample would be time-consuming, and a way to enter results for several samples simultaneously would be much more convenient.
The "Enter Batch Results" link appears at the bottom of the data tables for each of the grindability tests and other tables. Clicking that link will bring up a page similar to the diagram which consists of a series of individual input values (at the top) and a large blank entry field below. The input values vary depending on the test type, and many of the values are optional.
Step 1: Arranging the spreadsheet
Some modifications must be made to a typical spreadsheet provided by a laboratory before it is suitable to be uploaded in the database. For the purposes of this discussion, the spreadsheet is assumed to have a "data block" of values and assorted titles, letterheads and other extraneous data that won't need to be imported into the database.
The example spreadsheet (after values from Myers, Michaelson & Bond, AIME Technical publication No. 2175, July 1947):
The range of data we are interested in spans the cells "A-1" in the top-left to the cell containing "9.88" in the bottom-right. This is our "data block".
The spreadsheet must be modified to make it conform to the format that the database can accept. Remove any merged cells in the data block. The spreadsheet must be oriented such that rows correspond to a single sample and columns correspond to a piece of data.
If necessary, insert a blank row between the data block and the titles. Enter the column field names that correspond to the fields listed in upper portion of the batch entry web page — you will need to manually enter these field names and they are case-sensitive (capitalization matters). If a column in the data block contains data we aren't interested in storing, the column can be blanked out or the title row cell can be left blank for that data.
The field names we will be using in this example are:
- Sample: first column "Sample Identifier"
- ignore "Date Tested"
- umClosing: third column "Test Closing Mesh Size" (see note below)
- ignore "Technician"
- F80: fifth column "Feed Size"
- P80: sixth column "Avg Product"
- gpr: seventh column "Gpb grams/rev"
- ignore eighth column "kWh/ton" (short ton basis-wrong units)
- WiRM: ninth column "RWI kWh/tonne" (metric basis)
Change the number formatting to remove the triad separators (the comma at the thousands place; it will be mistaken for a decimal point if it remains). Bond grindability test closing mesh sizes must be converted to metric (µm). The value for 14# US is 1400 µm. Both F80 and P80 must also be entered in µm.
The modified spreadsheet should now look like this:
Finally, some missing columns should be added:
- Program: describes this testing program (in relation to any others). Use the date "2012".
- comment: (optional) use "Myers, Michaelson & Bond, AIME July 1947"
- Lab_id: (optional) if you know the laboratory who did the work, enter there 'id number' (not name) here. This work was done at the Allis-Chalmers lab in Wisconsin which is now owned by Metso; lab id number 13.
The blanked-out columns can be reused for these new data, or they can be appended to the right side of the data block. The order of the columns doesn't matter as long as the field names match the data below them.
Step 2: Copying & pasting the spreadsheet data
The modified spreadsheet should look like the figure below. The block in red indicates the range we will be copying & pasting into the batch input field on the website.
Copy the range in red, then switch to the web browser and paste it into the batch entry field.
The data in the batch field will not appear organized correctly (to a human), but it is actually correct in a machine-readable form called "tab-separated values". Press the "Submit batch" button (do not change anything that was pasted).
Step 3: Verify the data
The website will digest the data in the batch entry field and show you a preview of what it thinks the data means. Check the data in the preview table against your original spreadsheet. Only the first 20 rows of data are shown, but any additional rows entered are recalled when you press the "Submit" button.
Frequent problems to watch out for:
- Columns missing? If the field name is not spelled exactly correctly (capitalization does matter), then it won't appear in the preview.
- Triad separators in F80 field - is the first value "11770" versus either "11" or "11.77"?
- Did any of the letters in the text get garbled? Non-English accented characters (é, ñ) can sometimes get changed into gibberish when pasting between programs.
Two new columns are added automatically by the program (# and ProjNum). Don't worry about these two columns.
If you are satisfied, press the "Submit" button to commit this table of data to the database.
