Importing data into the ACE Database

Here is an example dataset containing deer management data:

This is stored in an Excel spreadsheet called deer.xlsx.

Before we create a dataset, we need to create a data frame that defines the fields. This is a quick way to do it:

columns <- tibble::tibble("Comments" = "str opt", "Species" = "Species:common_name", "Person" = "People and organisation:name", "Number" = "int opt", "Date" = "date", "Latitude" = "lat", "Longitude" = "lng")

The resulting data frame looks like:

The Comments field is a string and is optional.

The Number field is an integer and is optional.

The Species field is the species. If this is an integer in the dataset, it is interpreted as a species_id. That is not really the intention, though. It is expected to be a string that is looked up against the scientific or common name in the Species table and then stored as a species_id.

The Person field is a reference to the People and organisation controlled list.

The Date field is a date.

The Latitude field is a latitude value, and the Longitude field is a longitude value. These are combined to give the location. If a latitude is defined, a longitude must be too, and vice versa.

If you need to add extra columns, use the acer_add_dataset_field function.

res <- acer_add_dataset_field(“Deer management”, fields = columns)

With the columns defined, we can create the dataset:

res <- acer_create_dataset(“Deer management”, fields = columns)

The response will be an R object in which res[[“success”]] should be TRUE.

Now we can add data to the dataset.

Read the spreadsheet using the readxl package:

deer_data <- readxl::read_excel("deer.xlsx")

And check the data:

res <- acer_validate_data_points("Deer management", deer_data, shapes = TRUE)

The res[[“success”]] value is TRUE if all data points would be added. If there were failures, res[[“errors”]] is a data frame indicating which rows were not added. For example:

And to add the data:

res <- acer_add_data_points("Deer management", deer_data, shapes = TRUE)