See tips for using Help

1-Specify Template and Worksheet You need to specify the Excel template file and the worksheet within the file.

Browse For Template Permits you to browse for an Excel file that contains data for loading. Typically the data is stored in a worksheet that is based on a predetermined structure (template) that contains field names recognized by SPI Import. Hence we refer to this excel file as a template.

If the template is on your computer, simply click 'Browse For Template.' However, if the template is on SharePoint, first specify the SharePoint path, then click 'Browse For Template'.

SharePoint Path If the template is on SharePoint, specify the SharePoint location/path where the template is located.There are 2 options:

1) The WSI pick-list lets you choose a Survey on the WSI SharePoint submission site.
2) Checking FRPA will let you browse FRPA's SharePoint submission site.

After you specify a SharePoint path, click on 'Browse For Template'.

Use FRPA List This button automatically connects to FRPA's list of individually submitted observations, and prepares the data for starting step 2: quality check of FRPA's submitted data.

Template Path This is a non-editable field that lists the path to an Excel (i.e. template). This field is automatically populated after you browse for a template using the Browse For Template button. This field only stores the path to the template; it does not store the template nor any data from the template.

Open Template This button is for convenience only. It opens the template in Excel so that you can peruse the template.

Worksheet From the pick-list specify the name of the Excel worksheet that contains the data you want to load.
If the picklist is empty (i.e. SPI Import couldn't generate a picklist), the easiest way to specify the worksheet name is to open the Excel file, right-click on the sheet tab, select rename, then press Ctrl-C to copy the name onto the clipboard. Subsequently put your cursor into this Worksheet field and press Ctrl-V to paste the name into this field.

2-QC Template Worksheet SPI Import will perform a Quality Check of the template worksheet.

(Re)load & QC Template Worksheet Every time you click this button, SPI Import will (re)load the Excel worksheet into SPI Import and do a quality check of the data. Immediately prior to loading the worksheet, SPI Import automatically erases all previous data about the dataset.
Numerous aspects of the data are checked. After quality checking is complete a Quality Check report will pop up. The report lists mosts of the checks that were performed and the result of each check.

Template Type SPI Import automatically populates this field after you click the (Re)load & QC Template Worksheet button. SPI Import can recognize several types of templates. If SPI Import cannot determine the template type, then SPI Import will automatically display a Determine Template Type Report (DTTR).
You can also force SPI Import to display a Determine Template Type Report by checking the DTTR checkbox before you QC the worksheet.

Includes Inc/Srv Obs This checkbox indicates whether the Excel worksheet contains observations (Incidental or Survey) of species. Sometimes the worksheet only contains Design Component data and/or Design Component Visit data and/or Sampling Condition data, and lacks species observations, in which case this check-box remains unchecked.

DTTR DTTR stands for Determine Template Type Report. If you check this checkbox SPI Import will display a DTTR which describes the process by which SPI Import attempts to determine the template type.

Passed QC Simply a checkbox to inform you whether the current template has passed QC.

Standard Digital Files (SDFs) A Standard Digital File is a file recognized by most operating systems. E.g. JPG, PDF. During quality check, this section becomes filled in with information about which columns in the template contain information about SDFs (e.g. photos) and the path to where those photos are stored.

SDF Columns The number of columns that contain names of photos/SDFs.

SDF Path The path to where photos/SDFs are stored. During QC SPI Import will check whether the names of files in the template match the names of files located at this path.

SDFs To Load The number of files that will be loaded during 'Step 7 - Load Data into SPI DC'.

3 - Load SharePoint Metadata This section is for autmatically loading a submitted project and/or surveys from SharePoint into SPI.

4-Specify Project, Survey, and Dataset Name You need to specify the project, and possibly survey, to which the template data will be loaded.

Project From the picklist, select the project to which this data will be loaded. The easiest way to select a specific project is to click the down arrow (), then start typing in the ID of the project. SPI Import will automatically select the project you type. When your project of interest is selected, hit the Enter key of your keyboard.

Survey From the picklist, select the survey to which this data will be loaded.

Dataset Name It is not necessary for you to enter a name. This name is only for your convenience because you may be working on several datasets at one time. The dataset name will be displayed in the list of existing datasets on the 'SPI Import - Main' form.

5-General Mapping General mapping is the process of further informing SPI Import the specific location in SPI to which this data will be loaded.
Survey Observations. If the template contains survey observations, SPI Import needs to know the study areas and design components to which your data will be loaded.
Telemetry Observations. If the template contains telemetry observations, SPI Import needs to know the specific animals to which you data will be loaded.
Because a template contains fields such as Study Area Name or Animal ID, SPI Import can automatically 'map' the study areas or the animals to study areas and animals that already exist in SPI. Alternatively, you can do the mapping manually.

Auto & Report Automatically performs general mapping and then opens a report listing what has been mapped.
Survey Observations. If the template contains survey observations, SPI Import first attempts to match study area names in the template to study area names in SPI. If a match is found, then those study areas are mapped to each other.
Sometimes SPI Import does not recognize the study areas that are already in SPI and therefore is unable to map to those study areas. This occurs if the study areas in SPI have not been associated with the survey you specified in Step 3 (Specify Project, Survey, and Dataset Name). To resolve this, you need to open the SPI DC application and ensure that the study area is associated with the specified survey. To do so, in SPI DC edit the study area and specify the correct survey assignment.
After SPI Import successfully maps study areas to each other, SPI Imports attempts to match design components (based on the design component label field) in the template to design components in SPI. If a match is found, then those design components are mapped to each other. If design components do not match, SPI Import prepares to create new design components in SPI. The new design components would be labeled as they are labeled in the template. You can override the creation of new design components by manually mapping design components.
Telemetry Observations. If the template contains telemetry observations, SPI Import attempts to match animals in the template (based on Animal ID) to animals already in SPI. If matches are found, then those animals are mapped to each other. If matches are not found, then you will need to map animals manually.

Manual Opens some windows for you to do manual general mapping.

Clear All Clears all the general mapping.

Report Reports on the general mapping. To understand this report, it is imperative that you understand how SPI data is stored in various tables in SPI. The SPI database supports a highly developed inventory design, and thus SPI Import is programmed to accommodate considerable variation in template content.
These variations are categorized into 9 scenarios. The scenarios are defined based on, for example, whether data in the template will be used to create new design components, or be linked to existing design components, whether data in the template will be used to create new design component visits, or be linked to existing design component visits, etc.
The report contains the field Scenario ID followed by Record Count. The values in these fields tell you how many records of the template contribute to each of the scenarios. If you click on the plus sign () at the beginning of each line of the report, a textual description of the scenario will pop-up. The remaining fields in the report give you more specifics about each scenario. For example, the scenario table shown below tells you that the template contains 111 records that contribute to scenario 6. The remaining fields give you more detail. They tell you that the data will be linked to 2 existing study areas (SA_Existing = 2), zero new design components will be created (DC_New = 0), the data will be linked to 111 existing design components (DC_Existing = 111), the data will not be linked to any existing design component visits (DCV_Existing = 0), etc.

5b Review and Confirm Mapping of New SAVs If your dataset contains new Design Component Visits (DCVs) that will be added to SPI, then you will need to review and accept the proposed new Study Area Visits off of which the new DCVs will hang.

6-Field & Code Mapping Field and code mapping is the process of assigning fields in the template to fields in SPI so that the data in the template will have a place to be loaded. In addition, the codes used in the fields are assigned to existing codes in SPI. The purpose is so that all relevant data in the template will have a legitimate place to be stored in SPI.

Auto & Report Automatically performs field and code mapping and then opens a report listing what has been mapped. SPI Import does the mapping by first attempting to find matches between field names in the template and field names in SPI. If field names match, they are mapped to each other. Subsequently SPI Import attempts to match the codes within those fields. Wherever codes match each other, they are automatically mapped to each other.
You can override the automatic mapping by manually mapping fields and codes.
The only field that SPI Import does not automatically map is the Surveyor field. Because the spelling and arrangement of an individual's name is so variable SPI Import was not designed to attempt to match names to each other. You will need to manually map surveyors names, however in the Surveyor Mapping form SPI Import provides considerable functionality to make this an easy job.

Manual Opens a window for you to manually map fields and codes. Normally you should always manually map Surveyor codes (names) because SPI Import is not designed to map Surveyors and the SPI database requires a value in this field. However, if you choose to not map Surveyors, then during the loading process SPI Import will ask confirmation from you whether it is okay that all the surveyor names be entered as 'Not Available'.

Clear All Clears all the field and code mapping.

Report Opens a report (table) about the Field & Code Mapping. Depending on the contents of the data template there may be up to 11 categories of data fields in the report, and each of those categories will list a subset of data fields in the template and contain information about the codes of those fields. The image below is a report showing all 11 categories.
The most important things for you to check are (1) that you agree with the list of 'Orphan - not empty' fields, and (2) the number of unmapped codes is zero in the categories 'Fixed - mapped' and 'SMT - mapped'. The specifics of those two things are described further below.

The 11 categories are composed of 4 super-categories: (1) Orphan, (2) Fixed, (3) SMT, and (4) UDF, and sub-categories 'not empty', 'empty', and 'potential'. 'Not empty' refers to fields in the template that contain some data. 'Empty' refers fields in the template that are completely empty, and 'potential' refers to fields in the template that could potentially be present, would be recognized by SPI Import, and thus would be loaded into SPI, however the fields are not present in the template.

1-Orphan. These are fields in the template that have not been mapped to any fields in SPI. It is important that you agree with the list of 'Orphan - not empty' fields because the data in these fields will not get loaded into SPI.
2-Fixed. SPI Import is designed to recognize some fields in the template and map them to fields in SPI. You cannot override these mappings and thus they are called 'Fixed'.
3-SMT. SMT stands for Species Method Type (AKA data-field group). These are the data fields derived from the generic Excel template and are specifically designed to store information about this survey. SPI Import will automatically map as many of these as possible based upon looking for exact matches between the template field and the SPI field. However, you can manually override these mappings.
4-UDF. UDF stands for User Defined. The SPI data system supports the use of miscellaneous user-defined fields for specific surveys. If these fields have been specified from within the SPI DC application, then these fields will also be present in this list.

By clicking on the plus sign in front of each row you can expand the report to see the list of data fields in each category. The expanded report lists the template fields, specifies how they are mapped, and lists how many codes within the fields have been mapped. You can right-click on any column in the expanded report and choose to sort as you see fit. There are 4 columns in the expanded report. They are described below.
TemplateField Column. This column lists the fields in the template.
CodesMapped. This column lists how many codes within each field have been mapped.
CodesUnmapped. This column is important for you to check and confirm. If there are values greater than zero in this column that means that some of the codes in the template have not been mapped to codes in SPI. Thus, if you loaded the data to SPI DC at this time, those codes would not get loaded. To ensure that codes get mapped you should do the mapping manually or get SPI Import to automatically map codes. Normally you should manually map Surveyor codes (names) because SPI Import is not designed to automatically map Surveyors and the SPI database requires a value in this field. However, if you choose to not map Surveyors, then during the loading process SPI Import will ask confirmation from you whether it is okay that all the surveyor names be entered as 'Not Available'.
NullsIncluded. This column of the report only provides information for the SMT fields. It informs you with a Yes whether the template contained null values (blanks) in some of the records for the specified SMT fields. A null is treated as a valid value (code) and will be mapped to a null in SPI. Thus a null will not be reported as an unmapped code, but will be reported as a mapped code, so it is your responsibility to confirm that it is okay to have null values included for these particular SMT fields.
SPITable_Field. This sixth column informs you to which SPI Table and SPI Field the template field is mapped.

Below is an image of report with 3 categories expanded: 'Orphan - not empty', 'SMT - mapped', and 'SMT - potential'. Notice that 'Male Adults' is an 'Orphan - not empty' field and 'Adult Males' isexpanded report. is mapped.

7-Load Data The final step in working with a dataset is to load it into the SPI Data Capture Oracle tables.

Preview Data Load Opens a table that cryptically lists the data that will be loaded into SPI. This table is not very helpful.

Load into SPI DC Initiates loading of the data into SPI. During loading, the bottom left corner of MSAccess provides a continually updated report of loading status. After loading is complete a summary of loading results will be presented and you should confirm that the loading results match your expectations.
SPI Import is programmed to abort loading and perform a rollback (i.e. undo the edits) on the Oracle tables if an error occurs during loading data into the SPI DC Oracle tables. However, MS Access seems to have some weaknesses here and thus a rollback never seems to occur. For this reason, if an error occurs during loading there may be bits of data in SPI DC that need to be manually deleted using the SPI DC application.

Close Closes the Dataset Detail form.