How To: Extend format definition functionality to cover unreceiptable lab files

Relates to versions - Geobank 2022 (22.0.526)+

This guide has been produced to assist end users in preparing Micromine Geobank import files, particularly in relation to lab results that do not fit an expected format.

Overview:

Sometimes, laboratories send assay results in CSV files formatted in a way that Geobank cannot load.
It is commonly found that some fields are concatenated in a unique/custom style. When mapping (in the Sample Tracker Metadata tool), these merged fields are impossible to map separately into corresponding columns, as Geobank treats commas (,) as separators between fields

In this case, we have no choice but to provide identical coordinates for these three fields: Element, Units and Method.

While doing so, Geobank will display an error such as:

Pressing OK will allow you to ignore and proceed, but it delivers a warning in the mapping:

Ignoring this warning again is possible, and a file can be created for import; however, when loading the file using the Receipting Tool, the load will fail. In such cases, it is recommended to apply manual intervention if it is not possible to receive the correct formatting from the lab.

Receiving the Lab's File:

To properly process the incorrectly formatted lab file, follow these steps:

1. Select the receipt file and Progress the workflow by pressing the 'Run Next' Icon [>]
   
   
   
   
2. Select the File Format we just created and progress the workflow by pressing 'Run Next'.
   
   
   
   
3. Confirm that the file loaded correctly, and then do 'Run Next' again.
   
   
   
   
4. Ensure Auto-Create dispatch is selected and click 'Run Next'.
   
   
   
   
5. In this situation, we encounter a challenge with incorrect data format, so we are switching to Microsoft SQL Server Management Studio (SSMS) to resolve the issue.
   
   
   
   We'll do so by splitting the merged Element, Unit of Measure and Method using the following SQL Query:
   
   

   /*
   UPDATE [GB].[ST_BUFFER_ANALYSIS] with split values
   This Example code is based on the assumption that the order of merged data is:
   ANALYTE --> LAB_UNITS --> LAB_METHOD.
          If it is different, then this logic needs to be adjusted accordingly 
    */
   UPDATE [GB].[ST_BUFFER_ANALYSIS]
   SET [ANALYTE]=
   CASE WHEN (SELECT COUNT(value) FROM  STRING_SPLIT([ANALYTE], '_')) > 1 THEN
   LEFT([ANALYTE],CHARINDEX('_',[ANALYTE])-1)
   ELSE
   [ANALYTE]
   END,
   [LAB_UNITS] =
   CASE WHEN (SELECT COUNT(value) FROM  STRING_SPLIT([LAB_UNITS], '_')) = 3 THEN
   SUBSTRING([LAB_UNITS],CHARINDEX('_',[LAB_UNITS])+1, LEN([LAB_UNITS])
   - CHARINDEX('_',REVERSE([LAB_UNITS]))
   - CHARINDEX('_',[LAB_UNITS]                                              
   WHEN (SELECT COUNT(value) FROM  STRING_SPLIT([LAB_UNITS], '_')) = 2 THEN  
   RIGHT([LAB_UNITS],CHARINDEX('_',REVERSE([LAB_UNITS]))-1)
   WHEN (SELECT COUNT(value) FROM  STRING_SPLIT([LAB_UNITS], '_')) = 1 THEN
   [LAB_UNITS]
   ELSE
   NULL
   END,
   [LAB_METHOD] =
   CASE WHEN (SELECT COUNT(value) FROM  STRING_SPLIT([LAB_METHOD], '_')) = 3 THEN
   RIGHT([LAB_METHOD],CHARINDEX('_',REVERSE([LAB_METHOD]))-1)
   WHEN (SELECT COUNT(value) FROM  STRING_SPLIT([LAB_METHOD], '_')) = 1 THEN
   [LAB_METHOD]
   ELSE
   NULL
   END

After executing the query above, the [GB].[ST_BUFFER_ANALYSIS] table should look like this:

Once this is confirmed, we can return to the Sample Tracker to complete the Reconciliation step. Upon completion, the data can then be assessed according to the usual routine, and any regular issues can be addressed via standard methods.

Want to learn more?

Online Help Manuals - Click here for the latest version

Learning Management System - Click here to login or here to request access