Bulk-edit tree data via import/export to csv

This tutorial uses a real-world example to demonstrate how to make bulk-edits to an orchard configuration using Truffle Grower’s ability to import/export data to csv files.  The example continues on from the previous tutorial “Import tree data from GIS (via shapefile/geojson)”.

Problem statement

Continuing the example from the tutorial “Import tree data from GIS (via shapefile/geojson)”, after importing the GeoJSON orchard data, we need to make some further changes to the tree data.  There are two parts to this scenario:

Part 1: Correct the tree row names

Part 2:  Add missing tree properties

Part 1: Correct the row names

We have imported the GeoJSON file to create rows and trees in our orchard with the naming convention:

Tree name = “Block–Row#–Id#”    (for  example: “A-1-8”)

Tree row = “Row#“   (for example: “1”)

Because the row name did not include a reference to the original block numbers, trees from different blocks were placed in the same rows.  For example, tree A-1-8 has been placed in the same row as tree B-1-2.

To correct this, we want to change the row a tree is assigned to so that the row name includes a letter indicating the block number:

Tree row = “Block–Row#“   (for example: “A-1”, “B-1”)

We will be able to calculate the new row name in a spreadsheet by splitting the tree name and discarding the trailing tree number.  Tree “B-1-8” will be assigned to row “B-1” and tree “C-1-8” will be assigned to row “C-1”.

To correct the tree row names to include a prefix for the block number, we will:

• 1. Export the tree data to a csv file
  2. Open the tree data csv file in a spreadsheet
  3. Edits to the ‘Row’ column are made in the spreadsheet
  4. Spreadsheet data is then converted back to a new csv file
  5. Import the new csv file back into Truffle Grower to merge the changes

Export tree data to csv

The first step will be to export our orchard’s tree data to a CSV file.  To do this from Truffle Grower:

• 1. Select the orchard in Truffle Grower
  2. Edit the orchard block
  3. List actions that can be performed while editing the block
  4. Select the ‘export trees’ action
  5. Send the resulting CSV file to a spreadsheet (via email)

Note: for this example, we have chosen to edit the tree data using the Excel spreadsheet application.  The csv file will be emailed to a PC, where it can be opened using Excel.  Changes are made to the resulting worksheet and then sent  back to the Truffle Grower app as a new csv file.

Open csv file in spreadsheet

Depending on the spreadsheet application the data is copied to, steps may be required to convert the csv file into a table in the spreadsheet.   For this example, we are using Excel (version 14.7.2) to manipulate the tree data.

After opening the csv file in Excel all the data for each row is merged in the single column ‘A’:

This is because Truffle Grower uses a semi-colon character ‘;’ as field delimiter when generating a csv file, whereas Excel expects a comma ‘,’ by default.

Redistribute data to columns

To redistribute the data into the correct columns, perform the following steps in Excel:

• • 1. Select the column ‘A’
    2. From the menu, select ‘Data -> Text to Columns…’
    3. In the ‘Convert Text to Columns Wizard’ – Step 1 of 3:
       1. Check the file type is ‘Delimited’
       2. Press ‘Next >’
          
    4. In the ‘Convert Text to Columns Wizard’ – Step 2 of 3:
       1. Select the semicolon as a delimiter
       2. Press ‘Next >’
          
    5. In the ‘Convert Text to Columns Wizard’ – Step 3 of 3:
       1. Select the ‘Date planted’ column in the data preview
       2. Select a column data format of ‘Date (DMY)’
       3. Press ‘Finish’
          

After closing the wizard, the tree data should be arranged in columns for each property, and the ‘Date planted’ field should be formatted to show date values:

Creating a new Row Column

We want to replace the values in the ‘Row’ column with a new value that combines the block and row numbers.  Because of the naming convention that was used for the tree names, all we need to do is copy the first two parts of the tree name into a new column that will become the row.

For example, the tree name “C-5-12” will result in a row name “C-5”.

To do this in Excel, we can perform the following steps:

• 1. Copy the name values to an empty column (column “J”):
  2. Select the new column, and split it by choosing the menu item “Data->Text to Columns…” and specifying a “-” character for the delimiter:
  3. Recombine the first two parts of the tree name to build a new row:
     1. Add a title “Row” to cell M1
     2. Add the following formula to cell M2 and copy the formula to the rest of the cells in column M:

        =CONCATENATE(CONCATENATE(J2,"-"),K2)

The result is a column containing the new row names we want to use:

Export the changes to a new CSV file

To generate a new CSV file from Excel, we will copy the data into a new worksheet, then save the worksheet as a csv file:

• 1. Create a new worksheet (in this example, ‘updatedRowNames’)
  2. copy data from the original sheet, from columns A to G and paste into the new worksheet
  3. in the new sheet, select cell E2 (the first value in the ‘Row’ column)
  4. replace the value with a reference to cell M2 in the original sheet, and copy the formula into the rest of the column.  The result is a table of tree data with the modified row names:
  5. Update the format of the “Date planted” column.  Truffle Grower expects date values to be a string of format “d MMMM yyyy” (for example, “6 December 2018“).  To make sure the correct format is included in the csv file, we need to update the date format used in the worksheet:
     1. Select cell D2 (the first ‘Date planted’ value)
     2. From the menu, select “Format -> Cells…”
     3. In the ‘Format Cells’ dialog, choose a category of “Date” and pick a type matching “14 March 2001”:
     4. Copy the format and apply it to the remaining cells in the ‘Date planted’ column:
  6. Save the worksheet as a new csv file:
     1. Menu command “File->Save as…“
     2. Select “Comma Separated Values (.csv)” as the format
     3. When Excel warns that the workbook cannot be saved, choose “Save the active sheet”
     4. When Excel warns that the workbook contains features that will not work in the selected file format, choose “Continue”

Import the CSV file to Truffle Grower

The final step in the process is to open the new csv file in Truffle Grower, and use the data to create a new orchard block:

• 1. Send the csv file to your iOS device (as an email attachment)
  2. Press on the attachment and select ‘Copy to TruffleGrower’
  3. Select ‘<create new>’ as the target orchard
  4. Press ‘Import’ to complete the operation

A new orchard block has been created using the new row names.  You may choose to rename the new orchard block, as by default it is given the name of the csv file.  It is also safe to delete the old orchard block, as it will no longer be needed.

Part 2: Add missing tree properties

 

We have imported tree definitions from a GIS application and used Truffle Grower’s import/export to csv file to make bulk changes to the row names of the imported truffle orchard.  For our example problem, there are a few tree properties remaining to initialise.  We have not provided the tree and truffle species and the ‘date planted’ for each tree.

While we could have initialised these properties in Part 1 when working with the csv file exported from Truffle Grower, it is sometimes easier to create and import csv files that only contain the tree fields being modified.  When we import this file to an existing truffle orchard, only the trees and fields included in the csv file will be updated.

• 1. Create a csv file containing records for the changes we want to make
  2. Open the csv file with Truffle Grower
  3. Merge the data with an existing truffle orchard

Create a csv file with required changes

Create a file with records for each tree requiring an update.  Include a column titled ‘Name’, and additional columns for each property requiring an update.

Name	Match an existing tree name
Tree species	Scientific name of tree species, in an abbreviated format like ‘Q.robur‘.
Truffle species	Scientific name of tree species, in an abbreviated format like ‘T.melanosporum‘
Date planted	Date the tree was planted, in a format like ‘6 December 2013′

For our example, the grower has been using a spreadsheet to record information about the truffle orchard.  These tables were used as the source of data for the input file.

To create the csv file, we used Excel to create a new worksheet containing the required properties:

• 1. Copy the tree name, species and planting date from the respective columns to a new worksheet.
  2. Ensure the column titles are ‘Name’, ‘Tree species’ and ‘Truffle species’
  3. Search and replace the species values to convert from common name to the abbreviated scientific name used by Truffle Grower
  4. Check that the date format is like ‘9 December 2015‘

As in Part 1, a csv file is generated by saving the worksheet using the menu command “File->Save as…“, and choosing  “Comma Separated Values (.csv)” as the file format.

⚠  Important: Tree and truffle species values added to the csv file must exactly match the abbreviated scientific name for one of the species available to the truffle block in Truffle Grower.  For example, “Q.robur” for English Oak, “C.avellana” for Hazelnut.  To see the list of available species for an orchard block in Truffle Grower:

• 1. Select the target orchard in Truffle Grower
  2. Select ‘Edit’ to change to the orchard edit mode
  3. Select one of the trees in the orchard to bring up its details
  4. Select the species field to view or edit the available species list for the orchard

Import the CSV file to Truffle Grower

The final step in the process is to open the new csv file in Truffle Grower, and use the data to create a new orchard block:

• 1. Send the csv file to your iOS device (as an email attachment
  2. Press on the attachment and select ‘Copy to TruffleGrower’
  3. Select the target orchard you want to modify.  A warning will be shown advising the number of trees that will be modified
  4. Press ‘Import’ to update the orchard with the csv file data

The existing orchard block is updated using the rows in the csv file.  For each row in the csv file, the tree with matching ‘Name’ will have its ‘Tree species’, ‘Truffle species’ and ‘Date planted’ properties updated.

Note: if a record in the csv file specifies a tree name that does not exist in the orchard, then a new tree will be created with that name.  Default values will be used for the tree’s location and row.  Since this is not what we want, check the ‘Effects of importing data’ summary to make sure no new trees will be created before committing to import the file.