How To Use a Case Sensitive Search to Create Geology Code 2 data automatically

Explanation

When reviewing your Geology data, it may be useful to create custom geology code 2 values based on the descriptions found within the data. This is most common where the main constituents are capitalised within the description. It is not currently possible to do a filter within OpenGround Cloud Professional that is case sensitive (it will treat gravel and GRAVEL as the same) which would allow for a quick interrogation of this data, however, it is possible to export the data and then edit using Excel, then import it back in.

Process

• Open the Data Grid within OpenGround Cloud Professional that contains the values that need to be changed.
• Select CSV Export from the Grid Tools ribbon.
• Export the file to a convenient location.
• Open the exported CSV file within Excel.
• Open up the Field Geology Descriptions Tool available in the downloads section below.
• Copy the relevant data from the CSV file (A2:G2 in a non edited CSV file).
  
• Paste the data into the corresponding cells into the Field Geology Descriptions Tool (A3:G3)
  
• The tool has already been setup to use the search phrases found in cells I2:Q2. If other search phrases are needed then edit these as appropriate.
  
• The values in the 'ABBR to use table' (Cells T2:AB2) can then be adjusted to give the output that will be needed.
  
• The results of this process will then appear in column AD
  

• Check your output, put a filter on your new Geology Code 2, and manually modify any that are peculiar (i.e. SAND and SANDSTONE will both be found where SANDSTONE is present).

• Copy Column AD and replace the values in column G.
• Delete all of the columns following Column H (so all of the working columns) so you are left with just the relevant data.
• Save the file as a CSV file (File - Save As, ensure type is set as in the below screenshot (File Name and Type is important).
  
• Import into OpenGround Cloud Professional using the Data Import function.

Download

The tool mentioned in the above process can be downloaded from here.

Acknowledgement

This process and tool was provided with many thanks by one of our users - Jim Poole from Coffey. If you would like any assistance with this process then please contact Bentley Support.