Duration: 105 minutes
The data that describes a prospect or mine can be collected and recorded in a variety of ways. These include survey data from total stations, rock sample data from geologists’ notebooks, drillhole data from portable data entry equipment, compass traverse notes, data files from aerial surveys and aerial photography, along with existing data in a variety of third-party formats. Getting all of this information from different formats into a project and into a coherent and useful state can be challenging. This lesson describes some of the processes that can be used to get data into a Micromine project.
After this lesson you’ll be able to:
· Import text files into Micromine files (tables);
· Merge assay results from a lab with their associated coordinates; · Import data from a third-party GIS format (ESRI Shapefile); · Import (or link to) data from a database table using ODBC; · Spatially preview imported data in Vizex;
· Enter tabular data using the File Editor;
· Navigate through the records and fields in the File Editor.
Getting Data into Micromine
A geologist working on an exploration or mining project will typically encounter four different data sources:
· Tabular data exported as a text file format from a third-party application;
· Spatial data in a CAD, GIS or GPS format;
· An external relational database such as Microsoft Access®, SQL
Server®, or Oracle®. Data within these databases are normally managed
by a geologically aware system such as Micromine’s Geobank;
· Non-digital data such as paper-based field logging or printed logs and
reports.
An example of each data type is explored in the following topics.
The Different Ways of Obtaining Tabular Data
Micromine includes three different ways to get large quantities of tabular data into a project with a minimum of effort. They are:
Notes: · Importing · Linking · Merging
When you import tabular data you create a Micromine version of the source data as it existed at that moment. The source data can be any supported tabular format, and you can control which fields are written to the Micromine target file. The data must be re-imported if the source version changes. When you link data you create a permanent connection to the source data, which can only originate from an ODBC data source or Microsoft Access database. Linked data is easily refreshed to stay up-to-date whenever the source version changes.
When you merge tabular data, you select fields in a source file and merge them to the correct fields in the target file by matching values in a key field. You can also append data. The source data can be a text or Micromine file.
Before you begin using these techniques, it’s worth reviewing the different types of text file that you’ll meet when you undertake these processes. If you use Field Marshal to collect field data you can exchange files in native Micromine format. Field Marshal and the Micromine File Editor contain many data entry and processing functions created specifically for earth science data. It’s often cheaper and more efficient to buy FIELD MARSHAL or use the Micromine File Editor than to customise a non- geological spreadsheet or database application.
Notes:
Obtaining Data from Text Files
There are a numerous types of text file but in general they fall into two groups: delimited and fixed width. A delimited file contains rows of values separated by a separator or “delimiter”. Common delimited formats are comma-delimited (using commas) and tab-delimited (using tabs). Each row is terminated with a CR (carriage return) character and a LF (line feed) character.
Fixed width files, sometimes known as column-delimited files, contain
values separated into columns made up of a fixed number of characters. Delimited files are generally more space-efficient, but human operators can more easily read fixed width files.
Example fixed width file
Example comma delimited file
You import text using File | Import | Text. Generally Import Text is used to transfer independent data sets into a Micromine project. Alternatively use
File | Merge | Text to merge text data. The Merge Text function is used to
re-establish relationships within data sets, for example, merging assay results with the coordinates at which the samples were taken. You can also merge two Micromine files to achieve the same result.
The next two exercises will teach you to import a text file and then merge the imported file (now in Micromine format) into another Micromine file. Before you continue...
Make sure you’re using the built-in file viewer. To ensure this is the case, select Tools | Options | Editor from the main menu and check that Built-
Notes:
Importing CSV Text Files
CSV (comma separated values) is a simple and popular text format well suited to tabular data exchange. Most applications can export and import the CSV format. A typical scenario might involve a geologist recording field data using the built-in software on a notebook computer or PDA (personal digital assistant). On return to the office he or she would most likely upload the data to a Micromine project. CSV is an ideal format for this situation.
The simplest way to import a CSV (or any other text) file is to allow Micromine to automatically define the output file structure. You do this by choosing Determine from Input file in the Output File Structure group, and then using the Scan Rows or Scan File buttons to determine the structure.
You’ll explore these options in the next exercise.
Exercise 4.1: Import assay data from a CSV file
In this exercise we’ll import the new analytical data referred to in Exercise 2.3, in preparation for merging it into the existing soil geochemistry file. These data were received as a Microsoft Excel workbook and were exported from Excel in CSV format.
To import the text file:
1. Select File | Import | Text from the main menu.
2. Click the browse […] button next to the Input File response and navigate to the Import folder (within your training project). Select the file NEW_ASSAY_RESULTS.csv.
The .csv extension will be invisible if you have set Windows to Hide extensions for known file types.
3. Right-click the text file name to display it in the text viewer. Confirm that it’s in comma-delimited format, and note that the first row is a column header for Sample number and the six additional elements (Ba, Mo, Sb, As, Hg, and Tl). Close the text viewer when you’re done.
4. Set the Format to COMMA DELIMITED.
5. Enable Field Name Header and choose the One row radio button. This file contains no rows to ignore so there’s no need to set any Rows To
Ignore options. You can use these options to ignore metadata, internal
headings, or footers in a more complex file. Define the structure of the output file
6. Select Determine from Input file in the Output File Structure
Notes: 7. Enter the Output File name NEW_ASSAY_RESULTS and ensure the
Type is set to DATA.
8. Enter the Report file name ASSAY_IMPORT.
9. Click the Scan File button to scan the input file and automatically determine the structure of the output file.
In the workplace, if you have any doubt about the scan, right-click the input file to view it and, if necessary, click the Preview button to alter the output structure.
10. Click OK on the dialog box to run the function. Micromine will report a large number of errors.
11. Right-click the Report file and select View from the pop-up menu to inspect its contents.
All of the errors are of the type Value missing. The reason will become clear when we view the output file.
12. Right-click the Output File and select View from the pop-up menu to verify the file contents.
The missing values all originate from the Tl (Thallium) column. You can confirm that these values genuinely are missing by viewing the input file.
Merging Assay Data
We added fields to the data file in Exercise 2.3 to prepare that file for the merging process. The NVG_GCHEM file contains a sample number, co- ordinates describing where the sample was taken, some existing geochemical data, and new, empty fields for the additional geochemical elements. In Exercise 4.1 we imported the additional data into a file called NEW_ASSAY_RESULTS.
In this task we’ll merge the new geochemical data (NEW_ASSAY_RESULTS) into the existing soil geochemistry file (NVG_GCHEM).
To successfully merge the records in two files there must be a field with the same values in both the target and source files. This is known as a key field. If the key field in each file contains duplicate values, you must use two or more fields in each file to differentiate each record.
The Merge function processes the target file record by record. It finds the key field in each record and reads the value it contains. It then looks for the same value in the key field in the source file. When it finds an equivalent value, it takes the data from the fields you’ve nominated and writes it to the corresponding fields in the target file.
Notes: Exercise 4.2: Merge assay data from Exercise 4.1 into the sample locality file
In this exercise the sample identifiers are unique. This means you can use the fields containing sample identifiers in each file as the key field in the merge. In cases where there are duplicates in the key field in the target file you must use multiple fields to form the key.
Do the following:
1. Select File | Merge | MM from the main menu. 2. Double-click the Source File response and choose
NEW_ASSAY_RESULTS (ensure the file Type is set to DATA). 3. Double-click in the Target File response and select NVG_GCHEM. Defining the Key Field
We know that the Sample field is common to both files. And, because there are no duplicates in the Sample field in either the source or target files, you can use Sample as the single key field.
To define the key field:
4. Click the Key Fields button. (If the button is disabled, select the Use
key fields option lower down the dialog.)
5. Double-click the Source Field for Key # 1 and choose SAMPLE. Micromine will automatically set the matching field name for the Target
Field.
6. Because the sample numbers consist of alphanumeric strings, set the
Match to CHARACTER. If you omit this step Micromine will ignore the letter prefix of each sample number. Close the Key Fields dialog when you’re done.
We’re only using a single key field so there’s no need to proceed to the next row.
Defining the Merge Fields
You must now define the merge fields, which is very similar to defining the key field. Micromine does, however, greatly simplify the process whenever the field names are the same in both the source and target file.
Notes: 8. Click the small Select Fields button near the top left of the dialog.
9. Drag the mouse down the field list, starting with Ba and ending on Tl, to highlight the six geochemical fields, as shown on the following diagram. Click OK when you’re done.
There’s no need to select SAMPLE because it’s already defined as the key field.
10. Because the field names are the same in both files, Micromine will complete the form, automatically mapping each source field to the matching target field.
11. Optional: You can map source to target fields on an individual basis by double-clicking and choosing a Source field, then double-clicking and choosing the matching Target field.
12. Close the Merge Fields dialog once you’re satisfied with the field mapping.
13. Set the Match to FIRST, which will only merge the first occurrence of each key field value.
14. Enter Unmerged in the Unmerged file response, Duplicate in the
Duplicate file response, and Merge_stats in the Report file response. These files will record the results of the merge process.
Running and validating the merge
15. The final stage of the process is to run the merge. Do this by clicking the
Run button on the dialog box.
16. Running this process may give you an error massage, reporting that Hg is longer in the source file than it is in the target file, this is because it has been changed from being a REAL value to a FLOAT value. You can Press Yes to ignore this error message.
When the process is complete, a report will appear describing the results of the process, which is also reproduced in the Merge_stats file. You should also right-click the Unmerged and Duplicate files to check their contents, which list any source sample numbers that were duplicated or not merged. In this example there are none and these files are not created.
Notes: Checking the results of a function
A useful check on any function that processes files is to right-click on the output file (or files) and select View from the pop-up menu. Any
problems will become apparent and you can re-run the function after making the necessary corrections. Report files are considered output and should also be checked.
In the example above, the non-existence of the Unmerged and Duplicate
files is an indication of success, because they are only created when unmerged or duplicate records exist.
Exercise 4.3: Label the sample locality file
Now that we know a little more about the soil geochemical data, we’ll add labels for the Au1 geochemical data to the display:
1. Open the Soil geochemistry form set by double-clicking it in the Display pane at the bottom left of your screen.
2. The Points dialog will open back on the Points tab, which is where we last left it. Switch to the Label tab and complete it as shown here:
Prompt Setting
Show labels: Enabled
Text field (first row) AU1 (double-click to choose) Position: Top-right (double-click to choose
option 12)
Angle: 45 (type the number)
Font: Tahoma, Size 8 (double-click the AaBbYyZz text)
Notes:
You can quickly choose the Tahoma font in the Font dialog by typing the letter ‘t’. Windows will immediately jump to fonts beginning with that letter.
3. Click the Save button at the right of the Points dialog to save the alterations, and then click OK to redraw the labelled points. Your display should look like this diagram:
Notes:
Importing CAD or GIS Data
In addition to tabular data such as sample locations, drillhole information, and geochemical results, Micromine also supports numerous graphical formats from CAD (Computer Aided Drafting), GIS (Geographic Information System), GPS (Global Positioning System) and general purpose mining applications.
Supported formats include:
· CAD formats: AutoCAD DXF and DWG, Microstation DGN; · GIS formats: Mapinfo , ESRI ArcView , and MapGIS; · Surpac Strings.
· Personal Geodatabase · GPS Exchange (GPX) · Google Earth (KML)
Although it’s possible to directly display many of these formats in Vizex without conversion, in the following exercise you’ll import topographic contours from an ESRI ArcView Shapefile and convert them to a Micromine string file. Directly displaying CAD or GIS data in Vizex is covered in Part 2 – Displaying and Manipulating Data.
Exercise 4.4: Import and display topographic contours from an ESRI Shapefile
1. Select File | Import | Vector (CAD/GIS/GPS) Data from the main menu.
2. Double-click the CAD/GIS file response and browse to the Import folder (inside your project). Choose the nvg_topo_contours.shp file and click Open.
3. Enable Import attributes.
4. Type NVG_TOPO_CONTOURS in the Output File name and set the
Type to STRING.
5. Set the field names as shown here:
Prompt Setting
East field EAST
North field NORTH
RL field RL
Join field JOIN
String field STRING
Layer field MM_LAYER (Don’t use LAYER – it’s a reserved field name)
Notes: 6. Click Run to import the contour data. Right-click | View the new
string file to confirm the import, and then Close the dialog once you’re done.
As verification of the process we’ll display the imported contours in Vizex: 7. Select Display | Vizex | Strings from the main menu to display the
Strings dialog.
8. Ensure that the Input Data tab is active and the file Type is set to STRING.
9. Double-click the File response to choose the NVG_TOPO_CONTOURS file. Micromine will fill out the remainder of this tab for you.
10. Switch to the Display Options tab and ensure that only the Default
colour is set; use a light brown colour, and clear all other options on
this tab.
11. Click the Save As button at the right of the dialog to save your settings as a form set with the Title Topographic contours.
12. Click OK to apply the settings. Your display should now resemble the following diagram.
13. Conclude this exercise by selecting Edit | Remove All from the main menu.
The last two exercises have provided an introduction to Vizex, which is fully explained in Part 2 – Displaying and Manipulating Data.
Notes:
Importing or Linking Database
Data Using ODBC
ODBC is an acronym for the term Open Database Connectivity. It’s an industry standard interface that provides data access to a variety of database applications. ODBC isn’t tied to a particular operating system or database program, but instead allows you to see your data in the same way irrespective of the underlying software. You’re able to link to the database of your choice simply by installing the desired drivers on your computer. Whenever you install a database product, the appropriate drivers are usually installed along with it.
Setting up a new ODBC connection first involves creating a Data Source Name (DSN). There are three types of DSN, so the choice of which to use seems complex at first. In reality, they differ only in their degree of ‘visibility’ to the rest of the users on your computer or network. You need only create the DSN once; from then on it’s constantly re-used each time you reference the database.
The three types of DSNs are:
· User DSN, which is local to a computer and is accessible only by the current user;
User DSN: One user on one computer. Not available to other users.
· System DSN, which is local to a computer but isn’t user-dedicated. In other words, it’s shared amongst all users of that computer;
System DSN: Available to all users on one computer.
· File DSN, which is shared among all users on a network. It’s neither user-dedicated nor local to a particular computer.
File DSN: Available to all users on a network.
Notes: On most computers, the User and System DSN’s are grouped under the
category Machine Data Source.
You can use an ODBC connection two ways in Micromine: You either Import the data or Link to the database. When you Import, Micromine creates a ‘snapshot’ of the data. The files created in the Micromine project are ordinary data files, exactly the same as any other data file you’d create yourself. Because of this, the newly created files are independent of the database and