1

Topic: Range error

Hello!
I am having problems loading an Excel file into Recorder6.
I have loaded loads of Excel files before and also loaded another file earlier today (from the same supplier) and yesterday.
However this one gets hung up after I have clicked Validate Columns on the second screen of the Import Wizard.
I have checked as many things as I can think of in the import Excel file including removing some odd characters from the comments field, changing the formatting of the dates, removing some blank fields in the Determiner column, removing some '&'. Sorting and checking for other oddities. I also blocked and copied the date into a fresh spreadsheet in case there were any other oddities.
I have also saved the file as a csv file and tried loading that (twice).
Then I saved it back again as an Excel file in order to change the date formatting.
I have searched the Forum but not found anything that helps.
Needless to say now I am somewhat stuck...
But I do still love Recorder6, really!
Any suggestions?
many thanks,
Philippa

Post's attachments

sniptemp.JPG 59.73 kb, file has never been downloaded. 

You don't have the permssions to download the attachments of this post.
Dr Philippa Tomlinson
Biological Records Manager
Manx Biological Recording Partnership

2

Re: Range error

Hi
Try just importing the heading and the first data line. If this  works then the problem is in the data.  If this doesn't work let me know. I havent accesds to R6 for the next few days but will look as soon as I can.

Mike Weideli

3

Re: Range error

Thanks Mike,
Yes it works when I just import the header and the first line! Its not a huge file... 878 records. How do I find the error? I'm attaching the file in case you can find it.
Might it be a question mark in the Abundance field?
best wishes,
Philippa

Dr Philippa Tomlinson
Biological Records Manager
Manx Biological Recording Partnership

4

Re: Range error

please find Excel file attached

Post's attachments

2017 fungus group records.xlsx 61.41 kb, 6 downloads since 2018-10-10 

You don't have the permssions to download the attachments of this post.
Dr Philippa Tomlinson
Biological Records Manager
Manx Biological Recording Partnership

5

Re: Range error

I have come to the conclusion that it is the date field but I do not know how to correct this. I did try formatting the cells in this column as a date field and also as a text field but that didn't work. Do I have to re-type each one? I thought saving as a csv would have got rid of this problem too.... but it didn't. Shall I try saving in some other format?
Bemused!
Philippa
PS the annoying thing is I have a feeling this has happened before but I cannot remember how I resolved it

Dr Philippa Tomlinson
Biological Records Manager
Manx Biological Recording Partnership

6

Re: Range error

This might not work, but you could try importing with the date field set to import to sample comment, validate and then on the next screen set the date for all records to 2017. If that imports correctly, you can change the sample date to match the comments manually. You should only have about 80 to change then instead of 800.

Gordon Barker
Biological Survey Data Manager
National Trust

7

Re: Range error

I think the problem is the Abundance column.  There is one record where the abundance is "?".  Replacing this with a blank cell allows the spreadsheet to be validated without an error.  So there is something odd with a value of "?" that is causing the code to crash.

Andy Foy, Ecologist & IT consultant
Andy Foy Consulting

8

Re: Range error

Thank you very much! Yes the '?' was the main issue. It is a bit odd that this isn't flagged up as an error rather than crashing the system but I suppose that's for complicated database reasons.
I just need to remember this so it doesn't happen again!
with best wishes,
Philippa

Dr Philippa Tomlinson
Biological Records Manager
Manx Biological Recording Partnership

9

Re: Range error

I will record the ? Issue as a R6 bug and will takes a quick look to see how easy it will be to fix.

Mike Weideli

10

Re: Range error

Thanks Mike!

Dr Philippa Tomlinson
Biological Records Manager
Manx Biological Recording Partnership