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.

Using Correspondence Files for Importing and Exporting

1. Go to File->Change Library. Select training.glb and click Open.

2. Select File->New Project->Clone Data Template. Clone from the data entry.gdt data template to generate a new project called simple project.gpj.

3. 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.

   • GEOL tab: This tab provides lithology data. PointID and Depth are the same as in the LITHOLOGY table in the project. GEOL_BASE corresponds to the Bottom field. GEOL_DESC corresponds to Description. GEOL_LEG provides graphic codes, but these do not match the material symbols used in our project, and so will have to be translated.

   • 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.

4. Close Excel.

   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.

5. Go to DATA DESIGN->Correspondence Files.

   

6. Important: Select File->New File. This starts a new correspondence (GCX or GCI) file.

7. Important: Select File->Save As, and specify a file name of test import.gci in \gINT\datatmpt\. Click Save.

8. 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.

9. 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.

10. 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).

11. 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.

12. 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.

    

13. 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.
    
    

14. Enter the following values in the mapping table for POINT, using the Data Tool:

    Target Field	Source Expression
    PointID	<<POINT.PointID>>
    HoleDepth	<<POINT.HoleDepth>>
    Elevation	<<POINT.Elevation>>
    Logged By	<<POINT.HOLE_LOG>>
    North	<<POINT.North>>
    East	<<POINT.East>>
    Plunge	<<POINT.Plunge>>

15. Click Save  to save the mappings to the correspondence file.

16. 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:

    Target Field	Source Expression
    PointID	<<GEOL.PointID
    HoleDepth	<<GEOL.Depth>>
    Bottom	<<GEOL.GEOL_BASE>>
    Graphic	<<Case(<<GEOL.GEOL_LEG>>,=104,CONCRETE,=102,FILL,=403,SM,=810,BASALT)>>
    USCS	<<Omit>>
    Description	<<GEOL.GEOL_DESC>>
    Line Type	<<Omit>>

    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=104, return ‘CONCRETE’

    • 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.

17. Click Save .

18. 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:

    Target Field	Source Expression
    PointID	<<SAMP.PointID>>
    Depth	<<SAMP.Depth>>
    Length	<<Calc((<<SAMP.SAMP_BASE>> - <<SAMP.Depth>>) * 12)>>
    Type	<<Case(<<SAMP.SAMP_TYPE>>,=P,SH,=SPTLS,SPT,=U100,UD,=U76,UD)>>
    Number	<<SAMP.SAMP_REF>>
    Recovery Length	<<Omit>>
    RQD Length	<<Omit>>
    Blows 1st	<<Omit>>
    Blows 2nd	<<Omit>>
    Blows 3rd	<<Omit>>
    Blows 4th	<<Omit>>

    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’

19. 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.

20. Click Save .

21. 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.

    

22. Select File->View As Text again to uncheck this option, and restore the standard correspondence file builder view.

See Also

For additional reference information on correspondence files, see Help->Index->Correspondence Files (data design).