The following is a checklist and troubleshotting guide for validating and processing skeletal files for upload into the SERNEC portal.

The csv files created by the Imaging Workflow App have the following header:

catalogNumber,sciname,identificationQualifier,recordEnteredBy,
verbatimEventDate,recordedBy,recordNumber,
country,stateProvince,county

WARNING: These files only use commas to separate fields and the fields ARE NOT delimited by quotes. Staff have filled in data with commas in some places and this will cause data errors. The file needs to be checked in MS-Excel repeatedly and later in MS-Foxpro to make sure these errors are corrected.

The original CSV files come with two entries for each specimen image. This is because there is a JPG and NEF file image.

• Open CSV in MS-Excel.
• In the Data Tools section choose Remove Duplicates. The duplicates to remove are based on CatalogNumber.
• Check the CSV for columns that don’t line up. There should only be data in columns A-J. Column H (country) should never be empty. Column I (stateProvince) should never be empty if the country is the United States.

All records will need to have a GUID assigned. Add a column (K) with GUID as the header. Use the macro to populate the column with GUIDs (more details on this later). The header should now be:

catalogNumber,sciname,identificationQualifier,recordEnteredBy,
verbatimEventDate,recordedBy,recordNumber,
country,stateProvince,county,GUID

Preparing the preliminary CSV for loading into the SERNEC portal.

• Open the CSV in MS-Excel and once again check for skewed columns. Files with GUID may have too many columns and need to be corrected. The following entries may be problematic:
  • recordEnteredBy: staff have entered more than one name separated by commas or /. We are only allowing one person’s name in this field. Correct this manually in MS-Excel by using data filtering and replacement, in Notepad++ with replace, or later in MS-Foxpro.
  • country: the following need to be corrected:
    Virgin Islands, British -> British Virgin Island
    Palestinian Territory,Occupie -> Palestine
    * there may be others with commas to fix!