search

BiologicalRecordsCentre / iRecord

Repository to store and track enhancements, issues and tasks regarding the iRecord website.
http://irecord.org.uk
2 stars 1 forks source link

Change format of number ranges in our csv downloads #1599

Open kitenetter opened 5 months ago

kitenetter commented 5 months ago

Verifiers continue to be frustrated by Excel's insistence on interpreting abundance ranges as dates, e.g. "6-20" gets changed to "June 2020" when verifiers download records and open into Excel.

I know there are potentially technical solutions to this, and that users can import csv files into Excel and specify the data type rather than just double-clicking to open the csv, but that all adds barriers to verifiers being able to deal with data efficiently.

I think the simplest short-term solution is to change the format of our number ranges so that "6-20" becomes "6 to 20" and likewise for other ranges.

I propose to make edits to the following termlists:

@johnvanbreda @kazlauskis do you see any problems with making this change?

@DavidHepper this will also affect the abundance ranges used on the dragonfly forms, will that be a problem?

kazlauskis commented 5 months ago

The apps will upload the termlist options as warehouse numbers, so if you are only rewording the existing range labels in the warehouse for the download report, then we don't need to update the apps.

DavidHepper commented 5 months ago

Yes, Excel is particularly stupid because it does this even when the column is specifically set as type Text not General. The alternative I recommend and use when uploading to NBN Atlas is character En Dash ( U+2013 = Alt+0150 = – ) which looks similar but doesn't confuse Excel.

kitenetter commented 5 months ago

Thanks @DavidHepper for the en-dash suggestion, which I've now applied to the two termlists mentioned above. Closing.

kitenetter commented 4 months ago

@johnvanbreda in the csv files we generate to send to NBN Atlas, and the csv files generated for user downloads, the number ranges are still being outputted with a normal hyphen, even though the same records are displaying in iRecord with an en dash. Is there more that needs to be done to ensure the en dash version propagates through the system?

DavidHepper commented 2 months ago

Are the exports set to output UTF-8 ? This also preserves accented characters in Scottish Gaelic placenames, ♂ (= U+2642) in comments, etc. (I made the same mistake recently, sending to NBN Atlas.)