IAPP Batch Loader information
On this page:
- About the IAPP Batch Loader
- What to know before you start
- The Batch Loader schemas
- IAPP code tables and sample batch files
- All about the Site And Survey Batch Load File
This tool lets users load multiple site, survey, and treatment records and images simultaneously via the use of upload schemas. A schema is a text file that is structured and formatted in a specific way so that the database can write the data from each column to a pre-designated field in the database. The Quick IAPP Data Batch Loader Guide (PDF, 418KB) offers guidance on how to use the batch loader.
- The schemas are in .csv format. The bulk loader will not process any other file format correctly. Although they can be opened and edited in MS Excel, they must be saved as a .csv file. It is best to download the schemas from the Schema section below, save them to your computer and use them in their downloaded format for uploading to IAPP
- The batch loader will accept a MAXIMUM OF 200 RECORDS PER FILE, if you have more than 200 records to upload please split your files into 200 record increments and upload them separately
- The structure of the schema files should not be altered in any way; the column headers determine the file's column count. Removing or adding a column will cause a load error
- You may load any individual batch file on its own, or load 2, 3 or all 4 types of files simultaneously in the same bulk load process
- All date fields need to be formatted as YYYY-MM-DD, the same way they are entered into IAPP manually.
Format the date fields in your .csv files, as indicated in the illustration at right. If the field doesn't conform when you enter dates into it, you may have to adjust the date and time settings for your computer's operating system (Windows or Apple), as indicated in the illustration
- When creating new sites, you must enter a unique negative number in the Site ID field for each new site. Simply use consecutive numbers (-1, -2, -3, etc) for each new site. Once the load has been completed, these temporary negative numbers will be removed from the loader's memory and can be re-used in later loads
- When you load multiple files simultaneously, make sure that any treatments that were performed on new sites (i.e. sites that do not yet exist in IAPP) have the same negative number in the Site ID field of the treatment file as the new site they belong to!
- If you load the Site and Survey file first, on its own, and you load treatments to new sites in a subsequent load, then the negative number can no longer be used since the new site by then has a Site ID. In that case, you must establish the Site ID that has been assigned to the new site, and enter that new site ID in the Site ID field of the treatment batch load file
- To safeguard data integrity and avoid the possibility of data errors as much as possible when loading large quantities of data, all chemical treatments will be processed with the Auto-Calculation feature enabled. It is not possible to disable Auto-Calculation in the chemical treatment batch load file
- If you wish to override the auto-calculation, you need to go into each record once it has been created, unclick the Auto-Calculation checkbox and make your edits manually
- You can enter data in all the fields, but only the fields that contain the data required for the Auto-Calculation feature will be read; fields not required for the calculation will be ignored
- Once the load has completed successfully, you will receive an Upload Process Log message that details everything that was done (Site IDs of the newly created sites, Site IDs of the sites that were updated, and Site IDs and Treatment IDs for the treatments that were added to sites)
In case there are any errors that come to light during the loader's validation phase, you will receive a message in the Upload Process Log indicating which files contain errors, the line numbers where those errors can be found, as well as a description of the error. A likely error you may encounter in any file is: "Failed to process record null on line xx: [Invalid number of columns]"
This indicates that there is a hard return somewhere in the line, which you cannot see in Excel. However, there is an easy way to fix this:
- Open the file in WordPad (a text utility that comes with Windows)
- Go to View | Word wrap
- Set it to 'No word wrap'
That will display each line of your .csv file in one long string, breaking only where there is a hard return, as highlighted in red in the illustration at left.
Any file error will stop processing of the file. The entire load will be rejected, and no records will be written to the database.
This allows you to fix the errors, and reload the entire file rather than try to tease out which ones were and weren't loaded.
Taking great care in preparation of your data, and reviewing the upload files carefully, is strongly recommended! Once a file has been loaded into IAPP, any data errors will need to be correctly manually for each individual record!
There are 4 different upload schemas available for batch loading data into the IAPP database:
- Site and Survey schema (CSV, 694KB)
Use this when you need to:
- create one or more new sites, and/or
- want to update one or more existing Site Details records, and/or
- need to add one or more surveys to new or existing sites
- Chemical Treatment schema (CSV, 875B)
Use this for adding chemical treatments for plants on new or existing sites.
If you are adding chemical treatments to new sites in the same upload session as a Site and Survey file (those indicated by the negative numbers in your Site and Survey schema), you must use that same negative number in the Site_ID field in the chemical treatment schema, so that the bulk loader knows which site to attach the treatment to
- Mechanical Treatment schema (CSV, 433B)
Use this for adding mechanical treatments for plants on new or existing sites.
The same rule regarding the use of negative numbers for new sites applies to mechanical treatments
- Biological Treatment schema (CSV, 651KB)
Use this for adding biological treatments for plants on new or existing sites.
The same rule regarding the use of negative numbers for new sites applies to biological treatments
- Load Images to existing Sites or Treatments (CSV, 64B)
Use this for adding images to existing sites and treatments
Some of the batch file fields require codes, whereas others require a description; this was done for data integrity reasons and to avoid errors. Wherever codes and specific names are required, this code tables spreadsheet (CSV, 50KB) will supply everything needed.
The spreadsheet has 9 worksheets, each of which outline the kind of data the schema requires for:
- Invasive Plants
- Distribution and Density
- Treatment Methods
- Herbicide Names
- Biological Agents
- Recommended Activity
- Site Specific Use
- Site Soil Texture
It may be best and fastest to cut and paste some of those longer names - e.g. Ministry of Forests, Lands and Natural Resource Operations - from that document directly into your csv.
We also recommend a review of the Batch Files Fields and their Characteristics (CSV, 19KB) file, pictured at right.
This spreadsheet reviews each batch file's fields in order, explains what data type it requires, and whether it is mandatory or optional under which circumstances.
If you would like to look at a sample of what each file would look like, there are samples for each file type for download. Please note that these sample files are in .xlsx format to be able to clearly illustrate - by their coloured column headings and text - which fields are mandatory, and what type of data is expected in each field. Please do not use these files for batch loading since they are xlsx rather than the required csv.
- Site and Survey Batch Upload Sample file (CSV, 13KB)
- Chemical Treatment Batch Upload Sample file (CSV, 15KB)
- Mechanical Treatment Batch Upload Sample file (CSV, 11KB)
- Biological Treatment Batch Upload Sample file (CSV, 12KB)
- If you want to create a new site, you must enter a unique negative number in the Site ID field. Each new site will have to be designated with its own unique negative number. Once the load has been completed, these temporary negative numbers will be removed from the loader's memory and can be re-used in later loads
Any treatments that were conducted at these new sites that you wish to load at the same time, must therefore also have that same negative number in the Site ID field, in order for the bulk loader to realize that this treatment belongs to that specific new site!
- This file allows for a variety of data activities:
- Create a new site, which also requires at least one plant species and a survey for that species
- Update an existing site's data only without adding a new survey
- Update an existing site's data and add one or more new surveys
- Add one or more surveys to an existing site without updating the site information
- If there are changes to the Site Details and you have the required permissions, the Site will be updated accordingly
- If you do not have the required permissions, the processing will still be done, but the Site Details will not be updated. If there is a survey for the site, the survey will be added
- If a Survey Date is not supplied, the Survey portion of the record will be ignored. Site details will attempt to update, but no survey details will be validated or entered, even if other values are present
- If a Survey Date is not supplied, and you lack the permissions required to update the site, a warning will be sent indicating the record in question cannot be processed. It will result in a bulk loader error with no records written
- Like in the Add Site Wizard, new sites allow for only one jurisdiction, which will be added with a value of 100%
- If a site's jurisdiction is updated in the file but it already contains more than one jurisdiction in IAPP, the supplied jurisdiction will be ignore
- If a site's Jurisdiction is updated and it currently contains only one jurisdiction, the site will be updated with the newly supplied jurisdiction
- If a site requires multiple new surveys, each survey is entered on its own new line. It is necessary to repeat the mandatory Site data for each survey