1 (edited by Janet Simkin 16-12-2014 22:32:16)

Topic: Error validating excel spreadsheet

I'm trying to use the Record Cleaner to check spreadsheets of records against the new lichen rules, but have hit a few problems.

First I had to change the spreadsheets before it could read them correctly. The grid ref column format had to be changed from General to Text in some (but others worked fine without it being changed). Others had to have any formulae removed by pasting values, to avoid the Object error attached.

Having got the spreadsheets into the format that seems to be required, I find that Validation fails a lot of records on the grounds of not having a unique scientific name. What is non-unique about Parmelia saxatilis, Parmelia omphalodes, Physcia tenella, Ramalina farinacea, Lecanora soralifera, Cliostomum griithii, Hypotrachyna revoluta, Lecanora dispersa etc.? These are relatively common species. How can I get these records to go through to Verification?

These are the standard spreadsheets supplied by the BLS and they are used by many of our members to send in records, so I need to get this working before we demonstrate the Record Cleaner at our AGM in early January. Any help you can give will be much appreciated.

Janet

Post's attachments

NBN Record cleaner error.JPG 93.21 kb, file has never been downloaded. 

You don't have the permssions to download the attachments of this post.
Janet Simkin
British Lichen Society

2

Re: Error validating excel spreadsheet

Hello Janet

The NBN Record Cleaner tries to automatically match the taxon name to the corresponding TaxonVersionKey during validation so that they can be associated to appropriate rules during the verification stage. In cases where there is more than one choice of taxonversionkey to match to within the underlying species dictionary (as taken from the UK Species Inventory) it reports a non unique scientific name error and you need to manually match the name to proceed. This is done by right clicking on the taxon name in the data cleansing validation form and selecting the appropriate option in the drop down menu. These manual matches should be automatically saved for next time when using a saved template. Guidance on this is given in the Validation section of the NBN Record Cleaner User guide (http://data.nbn.org.uk/recordcleaner/do … rguide.pdf)

Having said this when just doing this on my machine, running Windows 7, the tick box is missing from this manual matching step so that these manual matches can not be saved. Let me know if this is the case for you. If so I could look into improving the underlying species dictionary to further reduce the need for this manual matching step. Having a quick look at the underlying SQL used to extract the species dictionary from the UK Species Inventory, for example, I can see that 3 of the species you mention (Cliostomm griffithii, Physcia tenella and Ramalina farinacea) could be automatically matched and I could have a further think about the rest.

Have you tried saving the BLS spreadsheet as a tab-delimited text file? This may get around the issues you are seeing which is likely to be a result of the spreadsheet format.

Best wishes

Graham
NBN Technical Liaison Officer

3

Re: Error validating excel spreadsheet

Thanks for explaining this, but how do I know from the drop down list which option will link to the TVK used in our rules? For instance, Ramalina farinacea gives two options, L. (Ach), SPECIES, LICHEN and SPECIES, LICHEN, but we only included this species once in the rules so we need to choose the right one.

Having chosen one from the drop down list I can't see any way to get the program to use it, it just ignores it and displays the same results again and again. There is no tick box to save or apply the selection. This is on Windows 8.1 64 bit.

Best wishes,
Janet

Janet Simkin
British Lichen Society

4

Re: Error validating excel spreadsheet

Hello Janet

There should be a tick box to allow saving of manual selection of non-unique scientific names in NBN Record Cleaner, but this seems to not be displayed when running on your machine, and also when I try on my machine running Windows 7.

I think it would be best to update the species dictionary used by the NBN Record Cleaner so at least the commonly recorded lichens are automatically matched to a unique taxonversionkey and they are not flagged up as non-unique in the first place. I have had a quick look at the BLS species list in the UK Species Inventory and highlighted the species I think need updating in the NBN Record Cleaner. I will send you the spreadsheet so you can have a look at this list. I will plan to update the NBN Record Cleaner species dictionary on my returning from the Christmas break in the new year.

Best wishes

Graham

5

Re: Error validating excel spreadsheet

Thanks for the list, we are checking through it now.

Most users must be on Windows 7 or 8 by now, so can we get the tick box to work again?

Janet

Janet Simkin
British Lichen Society