How to manage floristic inventories data in an electronic sheet format with Anaphyto

Marco Ciolli, Paolo Zatelli

Department of Civil and Environmental Engineering

Faculty of Engineering - University of Trento

The study of forest typology is becoming more and more practiced in Italy, and it is well known that it may give many important information and can improve the quality of our forest Assessment. One of the most used PC programs to process floristic analysis in Italy and abroad, is surely Anaphyto written by JP.Briane of the Laboratoire de Systematique et Ecologie Végétale of the University of Paris.

It is possible to obtain Cluster Analysis (CAH), AFC, Boules optimisées. The software is very simple to use and has an almost unlimited capacity to process input data.

Nevertheless we can face some problems using Anaphyto. One of the most important is the input data format, because it's very difficult to write it correctly and it's harder to comprehend it "a posteriori". One another is that the botanic classification used for the species names that identify the codes is quite old and doesn't match with the most used classifications in Italy.

Anaphyto input data example:
A001 A 0001 3 1742 3 2384 1 3256 1
A001 B 1742 2 2358 2 2583 + 2589 + 3677 1 4215 1
A001 C 0001 2 0037 + 0278 1 0727 + 0831 + 1329 + 0802 1 1672 2 1742 2 1500 1
A001 C 2106 + 2625 1 2666 + 3287 1 3083 + 3256 2 3482 2 4215 + 4583 2 4625 +
A002 A 0001 2 1742 4 4345 1
A002 B 0001 2 1742 2 3256 1 3677 + 4214 +
A002 C 0001 + 0037 + 0278 1 1329 1 0802 1 1672 1 1742 1 2625 + 3251 + 3256 +
A002 C 0449 + 3482 2 4215 + 4583 + 4625 1

A001, A002 are the number of the sample; A, B, C indicate the layer (A= Trees, B= Shrubs, C= Grass)

The numbers without letters are the species codes, followed by their coverage ratio as Braun Blanquet (5,4,3,2,1,+)

Each number and letter must be separated by one space, each row can't contain more than ten values

It takes a lot of time to transform an electronic sheet format into an Anaphyto input format and more than this, during the translation it often happens to introduce mistakes of many kinds into the final input text file. It should be better to automatically transform the electronic sheet without introducing transcription errors and maintaining the original file, in which each species is associated to a chosen code, as a reference file to solve any kind of doubt. This could permit to easily manage the data and to make easier any kind of control on the correspondence species-codes.

So we have built a Microsoft Excel macro, which permits the automatic translation of an electronic sheet in an Anaphyto input data text file. The macro has been written in Microsoft Excel 7 in Windows environment, but it works also with version 5 of for Windows 3.1 and 3.11 or 95. The macro works on 386 processors but faster processors are recommended to process large amount of data. At the moment we don't know any kind of bug in the macro, please let us know if you find any.

To use the macro it's necessary to prepare an electronic sheet with this structure:

In the first row from the first column insert the sample number and copy this row at the beginning of each layer:

SAMPLE A001 A002 A003
Abies alba A 0001 3 2 4
Acer pseudoplatanus A 0010
Betula pendula A 0600
Castanea sativa A 0954
Fagus sylvatica A 1742 3 4 2
Taxus baccata A 4345 1
Tilia platyphyllos. A 4417
Ulmus glabra A 4564
SAMPLE A001 A002 A003
Abies alba B 0001 2
Acer pseudoplatanus B 0010 +
Alnus viridis B 0160
Amelanchier ovalis B 0211
Sorbus aucuparia B 4215 1 1
Taxus baccata B 4345
Viburnum lantana B 4659
Viburnum opulus B 4660
SAMPLE A001 A002 A003
Abies alba C 0001 2 + +
Acer campestre C 0005
Acer pseudoplatanus C 0010 +
Acinos alpinus C 0730
Achillea macrophylla C 0016
Achillea millefolium C 0017
Veronica urticifolia C 4625 + 1 1
Vincetoxicum C 4699
Viola biflora C 4703 1
Viola hirta C 4716
Viola palustris C 4723
Viola reichenbachiana C 4710
Viola riviniana C 4728

In the first column write the species' names, following the chosen classification. In the second column, write the code of the layer (A= Trees, B= Shrubs, C= Grass). In the third column, write the species' codes that we have read in the file "Europe.dbf" which is in the directory CODES created at the installation of Anaphyto. The species' names can be used as a permanent reference, while we have chosen to let at the side of each species his layer code to make easier to understand in which part of the sheet we are while we are looking for something.

It's very easy to convert almost any other kind of electronic sheet in Excel so we can use even old forgotten data. If we already have electronic sheet files the only thing we need is to insert the columns that permits you to add the species codes and the layer code, the rows with the sample numbers at the beginning of each layer and the file is ready to be transformed by the macro.

Now you have to:

1) Open the input file made as described

2) Open the macro file

3) Run the macro

The macro creates a new sheet, copies the requested values and letters from the input sheet and pastes them in the *.prn file.

4) At the end the macro asks if we want to save the file *.prn in a text file. We must answer YES and the Anaphyto input file is ready to work. Remember that the input file for Anaphyto must have the extension *.txt


Copyright 1997 Marco Ciolli and Paolo Zatelli.

This software is available under GNU license, whose text file should come together with the macro file.

This Macro program formats an Excel sheet for the use with the flora analysis program Anaphyto'. This macro has been developed with Excel version 7 (for Win95) and it has been tested with version 5.

WARNING! It has been saved in a (theoretical) Excel 4 compatible way but it causes SYSTEM CRASH in Excel 4.

This program is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation; either version 2 of the License, or (at your option) any later version. This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details. You should have received a copy of the GNU General Public License along with this program; if not, write to the Free Software Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA 02111-1307, USA.

A ZIP file including the macro file, an example input Excel sheet, GNU license text, a short introductory note and a description file is available both in Italian and English version

Note for non-Italian users: all macro statements are in Italian because the development and test stage has been carried out on an Italian Excel version, however they should be automatically translated by Excel in your local language.

The authors can be contacted at the email addresses
or via snail mail at the address:
Dipartimento di Ingegneria Civile e Ambientale, Facoltà di Ingegneria, Università di Trento
Via Mesiano, 77 38100 TRENTO