Import/Update Inventory From an Excel File

Owned by Alexander Kinzer
Last updated: Oct 09, 2024
5 min read

Overview

If you need to make changes to your inventory outside of TreeKeeper, users with Upload/Download rights can access Data Transfer Utilities for downloading and importing options. To export your inventory into Excel, go to Data Transfer > Excel Download. See the following for how to download/export selected data: Downloading/Exporting Data from a Search.

The general process (described in-depth below) is as follows: Make the desired edits in the Excel sheet, update the Change Date & Time to be more recent than what’s in TreeKeeper for all sites, navigate to the Import/Update Tool, map columns, and import your data into TreeKeeper.

A recommended practice would be to download, edit, then import a sample set of test data to famliarize yourself with this process before using this tool with real data.

Requirements For a Working Import File

Before beginning an import, it is important to ensure the requirements for a working import file are met. See the list below:

  • UniqueID – all records must have an entry in this field.

    • If the UniqueID is the same as a site in TreeKeeper, its data will be updated as long as the Change Date and Change Time are more recent than what exists. (UniqueIDs cannot be blank)

    • If the record does not currently exist in TreeKeeper and you want to import it as a new site, you will have to create a UniqueID that is not yet used in the system.

      • When a site is added through the front-end of TreeKeeper, the auto-generated UniqueID is in the following format: [user's initials][yyyy][mm][dd][hh][mm][ss]

      • Example: a user named John Doe added a site at 2:30:00 PM on 1/26/2024. The UniqueID for that site would be JoDo20240126143000.

        • If John Doe was importing 30 new sites through the Import / Update Tool, he could use that UniqueID for the first site, then increment by 1 second for each additional site: JoDo20240126143001, JoDo20240126143002, etc.

  • Coordinate Type – Latitude, Longitude is almost always a good option. GeoJSON and X/Y values must match the system projection.

  • Change Date and Change Time – fields that determine if the changes are newer than what currently exists in TreeKeeper for each site. These fields will update any time a site is edited.

  • Inventory Date – The date when the site was inventoried. Will not update when a site is edited.

  • Species – this column must contain only the Botanical name. Anything misspelled will not be able to find a match in TreeKeeper.

  • All required fields – Any required field must have a valid value entered in the import file.

All date fields must be in MM/DD/YYYY format.

Finding the Import / Update Tool

After logging in to TreeKeeper, you can start the process by clicking on Data Transfer in the side toolbar.

In the Data Transfer Utilities window, click on Import / Update and choose the format of the file you will use to import. Excel and CSV files are available as an option to everyone, while the ArcGIS Collector is available as a paid option.

image-20240926-124621.png

Choosing the Import File

Once you’ve chosen a file type and made sure your import file has met the requirements, you can browse to the location of your Excel / CSV file on your device and click ‘Submit’.

image-20240926-124716.png

Column Mapping

Now that the import file has been chosen, you will have to map columns from the Excel / CSV file to TreeKeeper attributes.

In the Select TK Layer dropdown, select the data layer that you are importing to, then click Map Columns to begin mapping the import file columns to TreeKeeper attributes.

The first few fields to fill out will be the coordinate information, Remote ID (UniqueID), Change Date & Time, and Inventory Date.

As you scroll down, you will see all of the attributes in your TreeKeeper system.

A few notes on this section:

  • Required attributes * – TK Attributes with a red asterisk * next to them.

    • These cannot be left blank in the import file.

  • List-type attributes – The hyperlinked TK Attributes are list-type.

    • Clicking on the hyperlink will show you all of the valid list values.

    • Any list value in the import file that does not exactly match a value in this list will cause that record to fail.

Before clicking on ‘Upload Features’, it’s recommended that you save your column mappings. This will remember your mappings the next time you go to import the file, making the process easier in the event that some records fail to import. To do this, scroll back to the top of the column mapping form and click Save.

When you’re ready, click Upload Features and the tool will begin uploading the data to TreeKeeper.

Upload Results

When the tool has finished the upload, the results will show. The results are broken down into Successes and Failures:

Successes

  • Unchanged – the Change Date & Time are not more recent than what currently exists for that site, so the site did not change.

    • If sites are showing as Unchanged in the results but you are certain the Change Date & Time is more recent, make sure the date fields are in MM/DD/YYYY format and try again.

  • Inserted – a new site was added into TreeKeeper. (i.e. there was no matching UniqueID in TreeKeeper)

  • Updated – an existing site was updated with the new data.

Failures – a table will display containing the UniqueID of the record that failed to upload along with an error message. You should also receive an email.

For example, the image below shows that ‘None’ is not a valid list value for the Observation attribute. To fix this, you would either need to add ‘None’ to the Observation list through Attribute List Management or change those values in the Excel file to one of the valid list values.

Once the issues are fixed, you can run the import again until there are no failures!