Note: This exercise requires the use of projects, library and other files available from: download.aspx. Instructions for unzipping and setting up these files is on: using gint downloaded examples.aspx.
When the table-and-field structure of an imported spreadsheet, Access database, delimited text file, or gINT project does not match that of the project you’re importing into, you use a correspondence file to specify the mappings between fields in the source and fields in the target. Similarly, you can use a correspondence file on export to create a file with a different structure than the source project. In addition to directly mapping source fields to their destination tables, you can also perform processing on the data in the fields to make necessary conversions.
Correspondence files are text files with a specific syntax. Generally they are stored in the \gINT\datatmpl\ folder. Correspondence files for import are usually given a .GCI filename extension, and those for export a .GCX extension. Once you have created a correspondence file for data files of a particular format, you can use it for import of these files as often as needed.
Note: You can use a .GCX file for import or a .GCI for export if desired, as the file syntax is the same.
gINT provides the DATA DESIGN->Correspondence Files utility application for creating and editing correspondence files. You could create correspondence files with a text editor, but the application is faster to use and less error-prone.
Select File->New Project->Clone Data Template. Clone from the data entry.gdt data template to generate a new project called simple project.gpj.
Open the corresp file test data.xls file in Excel. It is located in \gINT\projects\. Click on each of the sheet tabs, and notice the following:
POINT tab: This tab has borehole data, and will import to the POINT table in simple project.gpj. Several fields do not have counterparts in the project, and will not be included in the import. HOLE_LOG corresponds to Logged By in the project.
SAMP tab: This tab provides sample data. PointID and Depth are the same as in the SAMPLE table in the project. SAMP_BASE provides a bottom depth for each sample, but since our project uses Depth and Length rather than Depth and Bottom, this must be converted to Length. SAMP_TYPE will require translation to Type codes that correspond to sampler graphics found in our library. SAMP_PREC provides recovery values as a percentage; we will ignore this field for now and set up conversion to Recovery Lengthin a later exercise.
Note: You cannot use an Excel file as a source file for creating mappings in the Correspondence Files application while it is still open in Excel.
Go to DATA DESIGN->Correspondence Files.
Important: Select File->New File. This starts a new correspondence (GCX or GCI) file.
Important: Select File->Save As, and specify a file name of test import.gci in \gINT\datatmpt\. Click Save.
In Source File, click the Browse button, specify ‘Excel 2003’ for Files of Type, and select corresp file test data.xls in \gINT\projects\, then click Open. This is the spreadsheet to be used as a model for Excel files to be imported.
In Target File, click the Browse button and select simple project.gpj in \gINT\projects\, then click Open. This is the project database we will import into, with the aid of the correspondence file.
Click the drop-down arrow on the Target Table drop-down list. Notice that the table names in simple project.gpj are listed. Select ‘POINT’ in the list. Notice that a list of fields from the POINT table appears in the Target Field column of the mapping table.
Each Target Field is what is being mapped to (in the destination project). You need to provide a Source Expression to specify what you are mapping from (for those fields that are mapped from the source spreadsheet).
Select a Default Source Table of POINT. This is the name of the table (actually the sheet) in the source spreadsheet that maps to the POINT table in simple project.gpj.
In the Data Tool (shown below), set the Table drop-down selection to POINT and click the drop-down for Field to see which fields are available in the POINT sheet in the spreadsheet.
Click in the Source Expression to the right of ‘PointID’. In the Data Tool, select a Table of ‘POINT’ and a Field of ‘PointID’, then click Paste. Press Tab to exit the Expression popup. ‘<<POINT.PointID>>’ appears in the Source Expression.
Enter the following values in the mapping table for POINT, using the Data Tool:
Click Save to save the mappings to the correspondence file.
Next you create mappings into the LITHOLOGY table. Select ‘LITHOLOGY’ in the Target Table drop-down list, ‘GEOL’ in the Default Source Table, and ‘GEOL’ in the Table drop-down in the Data Tool. Enter the following:
The expression for the Graphic field converts from material codes used by the person who supplied the spreadsheet into equivalent material symbol codes in our library. The Case() function works as follows:
If GEOL.GEOL_LEG=102, return ‘FILL’
If GEOL.GEOL_LEG=403, return ‘SM’
If GEOL.GEOL_LEG=810, return ‘BASALT’
The <<Omit>> value in the USCS and Line Type fields is not required for successful data conversion, but will eliminate warning messages for these fields in the status log that is generated during import. To paste <<Omit>> into a field using the Data Tool, select a System value of ‘Data Items’ and an Items value of ‘Omit’, then click Paste.
Click Save .
To create mappings for the SAMPLE table, select ‘SAMPLE’ in the Target Table, ‘SAMP’ in the Default Source Table and ‘SAMP’ in the Table drop-down in the Data Tool. Enter the following:
The expression for the Length field calculates Length by subtracting the ‘Depth’ value in the spreadsheet from the ‘SAMP_BASE’ (bottom) value to get the sample length in feet, then multiplying by 12 to obtain inches.
The expression for the Type field (sample graphic) is comparable to what was done for the lithology graphic. The Case() function returns the following:
If SAMP.SAMP_TYPE=’P’, return ‘SH’
If SAMP.SAMP_TYPE=’SPTLS’, return ‘SPT’
If SAMP.SAMP_TYPE=’U100’, return ‘UD’
If SAMP.SAMP_TYPE=’U76’, return ‘UD’
Click the Set Empty Sources to <<Omit>> button. This sets the Default Source Table to <<Omit>> for all tables we’re not creating mappings for, and ensures that nothing is imported into these tables.
Select File->View As Text to view the contents of the test import.gci (alternately you could open it with Notepad or another text editor in \gINT\datatmpl\). Notice how this correspondence file captures the conversion settings you specified.
Select File->View As Text again to uncheck this option, and restore the standard correspondence file builder view.
Go to INPUT->Borehole.
Select File->Import/Export->Import from Excel File.
Click the Browse button for Excel File, and select corresp file test data.xls in \gINT\projects\.
Click the Browse button for Correspondence File, and select test import.gci in \gINT\datatmpl.
Leave Overwrite Options set to ‘Never’, and click OK.
Look at the results in the status log, then click OK to dismiss it.
Select ‘Test1’ in the Borehole tab, then view the records for this point in the Sample and Lithology tabs.
For additional reference information on correspondence files, see Help->Index->Correspondence Files (data design).