[Bug]: recordNumber converting non-date collector numbers to dates

[ljwalker]

Contact Details

Diana Sawatzky

Symbiota portal name

SEINet (NANSH)

What happened?

Collector numbers that resemble dates (but are not dates) are automatically converted to date format in the Occurrence Editor and then displayed publicly as dates. For example, in 30466397, the collection number on the sheet is "08-27", but it appears as "27-Aug" in Symbiota. This conversion proliferates to spreadsheet exports of the same records.

recordNumber type = "varchar(45)", not "date"

Examples:

• https://nansh.org/portal/collections/individual/index.php?occid=30466399
• https://nansh.org/portal/collections/individual/index.php?occid=30466374
• https://nansh.org/portal/collections/individual/index.php?occid=30466362
• https://nansh.org/portal/collections/individual/index.php?occid=30466346

What operational systems are you seeing the problem on?

MacOS

What browsers are you seeing the problem on?

Firefox

[egbot]

This is an issue with Excel, which folks have been struggling with for as long as I know. Symbiota never reformats the collector number, but this type of reformatting is a common problem with Excel. The data was likely corrupted by Excel, and then imported into the portal.

When a spreadsheet (e.g. CSV) is opened in Excel, the application unfortunately evaluates each field and tries to guess the data type. It's not uncommon for a non-date fields to be translated and displayed as a date. If you don't resave the data, it's just the display of the data that is incorrect. If you open the same file within a text editor (e.g. notepad++, MS notepad, MS wordpad, etc), you will see that the format is correct. Unfortunately, if you save the file after opening it up in Excel, if will rewrite the field with the incorrectly interpreted date value. Excel will also trim zeros of the end of numbers, and sometimes round coordinates to the nearest cent. If you then upload the file, the record will contain the wrong data. For more information on this subject, see here or Google "stop excel from auto formatting dates"

Therefore, we recommend that folks don't use Excel. If you do, it's best to only use it to view the data, and avoid resaving. Alternatively, you can use other spreadsheet editors such as OpenOffice, LibreOffice, etc.

[ljwalker]

@egbot I forgot to include a screenshot. The main issue is that this is happening in Symbiota, but good to know about Excel:

[themerekat]

@ljwalker , are they importing the data first from a spreadsheet or directly entering it from the label?

[egbot]

It can't happen in Symbiota, there is mechanism to make this data conversion, but this type of thing regularly happen in Excel. And looking at the data edits (via admin tab on editor), there appears to be no data entry edits via the user interface. Thus, it looks like they upload the data via a CSV upload.