Frequently Asked Question
Laboratory Tests - Importing CSV Files
Last Updated 3 years ago
Generic CSV Data File Formats for Laboratory Test Data in PAMDownload an example CSV file
PAM offers the ability to work with "Generic CSV" files for Soil, Leaf, Sap and Nutrient Depletion test results. The CSV files which will be imported by PAM for these tests need to be in a specific format. These file formats can be both imported and exported.
What is a CSV File?CSV is a file extension which stands for "Comma Separated Values". It’s a file format that many spreadsheet programs such as Microsoft Excel can work with. It works by organising data as a table, with a number of rows and columns (just as in a spreadsheet). The CSV file itself is a plain text file, which can be viewed using your favourite text editor (such as Notepad), although it probably won’t make much sense.
- Each row in the data table is represented as a line of text in the CSV file.
- The values in the columns of each row are represented as plain text, with columns separated by commas or spaces.
- Where the data in a column contains a comma or a space, it is enclosed in double quotes.
- NEW in 2016 - We added a new MANDATORY column "Test Depth" see the example CSV file for placement (between Sample Date and Site!)
Column HeadingsFor Laboratory test data, the first row of the CSV file is assumed to contain column heading information. If it does not, it should be left blank, as the importer disregards this first row, and any data in it will be lost (not imported).
Geographical Location DataRows in the CSV file can contain geographical location information – a latitude and longitude that specify the exact place at which a test was taken. If geographical information is included in the CSV file:
- It MUST be in the first two columns in the files
- The first column must be the latitude of the point, expressed in decimal degrees with latitudes south of the equator being less than zero1
- The second column must be the longitude of the point, expressed in decimal degrees with longitudes west of Greenwich being less than zero.
Data Header ColumnsThe four columns following the geographical location information, if it is present, or the first four columns in the CSV file if not, contain header information about test sites which is used for ALL laboratory test information. These columns must be present, and must be contain data in the following order:
- Field ID
Text which specifies a name for the field (paddock) in which this test was carried out. It doesn’t need to match the paddock names on your farm, although it would obviously be preferable if it did.
- Sample No.
Text that specifies a sample number assigned by the laboratory to a test sample.
Up to 20 characters which are used as a label for the test site if geographic coordinates are provided for the test.
- Sample Date
The date the sample was taken. This needs to conform to your system short date format, which can be set via the control panel, and which normally defaults to US date format (mm/dd/yy).
Test Result DataThe remaining columns within the CSV file hold test result data, comments about them, and notes about the entire set of tests performed at a particular site. The results can be either text or numeric.
Numeric results may have associated comments. These are stored in a separate field. The comments field :
- Can contain up to 20 characters of text
- MUST have the same name as the result column , with the addition of the text " – COMMENT". Note the spaces – it is "(space)-(space)COMMENT". Column names are case insensitive, so the heading of the Comments columns may be in any combination of upper- and lower-case.
- Must come AFTER the result column to which it relates. Ideally, it will be the column immediately following the result column.
A special column heading of NOTES is used for a column containing notes which relate to an entire suite of tests for a site. These notes might contain (for example) a recommendation for action to improve a paddock’s soil condition or productivity.