Saturday, February 13, 2010

A Brief Guide to Exporting Data from Geosystem to gINT

Why should I read this guide?

Aside from this guide, there is no comprehensive description of how to get data from Geosystem project data files into gINT project data files. If you are familiar with gINT’s data organization and Geosystem’s export tool, you can get started without reading this guide. Because a wide variety of field and table names are used in gINT, this guide will focus on building your skills to the point where you can make the right decisions on your own, rather than stating a specific set of steps to follow (although the appendix shows a fairly detailed sequence of actions).
This guide was created with Geosystem for Windows v2.1, LD4 module v4.1, and gINT version 8.2.004. While other versions of these programs are similar, they may not be identical in function.
Supplemental reading:

  • “A Brief Guide to Exporting Data from Geosystem” covers much of the same ground as this guide, but it does not discuss gINT specifically.
  • The LD4 User Guide and the LD4 Configuration Guide are good sources of information about Geosystem. You may also wish to try out the Data Summary and Export Tutorial, a bundled software element that briefly but effectively demonstrates how to use the export tool.
  • gINT has an excellent desktop help system, as well as online FAQs and forums.

Why is there no guide to exporting from gINT to Geosystem?

gINT allows both import and export of data, but Geosystem only allows export. gINT Software has sought actively to make their databases the best vehicles for all geotechnical information, so moving your data to gINT files is probably the best choice in any case.

What should I do first?

You may be tempted to open up the tool and immediately export every field listed in the LD4 data summary and export window. As is so often the case, it is best to restrain this instinct and take a moment to think about what you are trying to achieve. Adding unnecessary fields not only takes time, it also creates a large, messy file that requires pruning prior to use. Furthermore, you can only export fields associated with a single data entry form, so you must be sure that form contains everything you will need. Here are some of the things you will want to think about:

  • What data do I need to export?
  • Is this data already in my LD4 data file? If not, will I have to add new fields to my data entry form?
  • Do I have all of the Geosystem modules with which the project data file was created?
  • Do I need to present my results in LD4 boring logs or can I perform some data manipulation outside of the software?
  • Will it be easier to use my exported data in separate files for each project or in a single exported file?
  • What specific field names are in my gINT database?
  • Do the units in my Geosystem file match the units in my gINT file?
  • Does my gINT database have fields (such as numeric fields) that will not accept incorrectly formatted data? Which are they?
  • Do the fields correspond one-to-one between the data files, or will I need to split or combine some fields manually?
  • Are there any relevant Geosystem configuration files or gINT correspondence files that have been created already?
  • Once the data transfer is complete, will I have any uses for my Geosystem file?

Once you know what you are trying to accomplish, you can get started by making sure that your Geosystem data file has everything you need.

Why is there no guide to exporting from gINT to Geosystem?

gINT allows both import and export of data, but Geosystem only allows export. gINT Software has sought actively to make their databases the best vehicles for all geotechnical information, so moving your data to gINT files is probably the best choice in any case.

What should I do first?

You may be tempted to open up the tool and immediately export every field listed in the LD4 data summary and export window. As is so often the case, it is best to restrain this instinct and take a moment to think about what you are trying to achieve. Adding unnecessary fields not only takes time, it also creates a large, messy file that requires pruning prior to use. Furthermore, you can only export fields associated with a single data entry form, so you must be sure that form contains everything you will need. Here are some of the things you will want to think about:

  • What data do I need to export?
  • Is this data already in my LD4 data file? If not, will I have to add new fields to my data entry form?
  • Do I have all of the Geosystem modules with which the project data file was created?
  • Do I need to present my results in LD4 boring logs or can I perform some data manipulation outside of the software?
  • Will it be easier to use my exported data in separate files for each project or in a single exported file?
  • What specific field names are in my gINT database?
  • Do the units in my Geosystem file match the units in my gINT file?
  • Does my gINT database have fields (such as numeric fields) that will not accept incorrectly formatted data? Which are they?
  • Do the fields correspond one-to-one between the data files, or will I need to split or combine some fields manually?
  • Are there any relevant Geosystem configuration files or gINT correspondence files that have been created already?
  • Once the data transfer is complete, will I have any uses for my Geosystem file?

Once you know what you are trying to accomplish, you can get started by making sure that your Geosystem data file has everything you need.

Is my data file ready to be exported?

Open your project file and go to the Tools menu. Select “Data Summary and Export.” You will see a window with a variety of text boxes (See Figure 1). Look at the one titled “Dictionary.” This box contains all of the data fields used by the current project’s data entry forms. If you do not have both LD4 and Labsuite, you might not be able to see any laboratory data fields, even if your file was created by someone who entered data using Labsuite. If this is the case, you will need to add the relevant laboratory data fields to the current project’s data entry form.

1 Figure 1. The Data Summary and Export Window
Even if all of the raw data is visible, it may be better to have Geosystem perform some calculations prior to exporting the file. This is the case when there is a chance that you will need to deliver a Geosystem data file or report to your client. Of course, you can export your raw data, manipulate it, and type the results into a new field in the Geosystem data file, but this would duplicate work that could be completed by the software. Describing how to create calculated fields within Geosystem is beyond the scope of this guide, but the Customization Guide bundled with LD4 may help.
Finally, you may wish to merge multiple project data files prior to export. Under some circumstances, it may be preferable to keep all the project files separate. In most cases, merging projects will reduce the overall time to export and simplify subsequent processing. To merge files, go to the Tools menu and select “Project Merge…”
If none of these concerns applies to your data file and if all of the data has already been entered, you may skip to section 5.

How do I add fields and data to my project file?

In order to add fields to the data file, return to the Data Entry window by closing the Data Summary and Export window (See Figure 2).

2 Figure 2. The Data Entry Window

Go to the Tools menu, and select “Data Entry Forms.” (You must be using LD4 in order to add data entry fields to the file. Users who have only licensed Geosystem’s laboratory testing modules can only export the standard data fields). A new window will display. Select the option from the left side of the screen that corresponds to the type of data you wish to export. This will bring up a listing of the data entry fields already associated with your project data file (See Figure 3). In the yellow box at the top of the screen, Geosystem has a long list of fields that have already been made for you. You may select one of these by dragging it from the yellow box to an appropriate location on the card below. If none of the standard fields seems appropriate, you may create your own by selecting the gray button, “Click if your field isn’t listed” and filling the blanks at the bottom left corner of the screen. If you are creating a new field, set the field name to match the corresponding field name in gINT. Don’t worry about adding every single field that exists in your gINT file. Only add fields, such as laboratory testing fields, that already contain data, or calculated fields that must be displayed in your Geosystem reports. Check to make sure that your new data entry field is correctly formatted; then go to the Entry Form menu and select “Exit.” (Geosystem automatically saves any changes you have made).

3 Figure 3. The Data Entry Form Design Window

To enter more data into your file, simply select the relevant table from the yellow box on the left, select the relevant data entry field, and begin typing.
To add data from other project data files to the current file, go to the Tools menu and select “Project Merge…” Select “Continue,” find the first file you wish to merge, and select “OK.” Repeat until all the necessary project data files have been merged. The details of merging projects with problematic similarities are beyond the scope of this guide. You may wish to consult Geosystem’s Help menu for more information. How should I organize my data export system?
Geosystem stores the arrangement of your exported data in a *.lfg file it calls a “Configuration.” Since you can save a nearly infinite number of configurations, you may export your data in a variety of formats for a variety of gINT templates. This also makes it possible to break up a single project data file into pieces that are more easily assimilated into your gINT file. Due to the likelihood that your first attempt at data export will not be perfect, I highly recommend that you save a configuration as soon as you have finished arranging your data fields for export.
Creating a configuration is quite easy. Simply drag the data fields you need from the Dictionary text box to the Table box and select “OK” when prompted (See Figure 4). Note that the names displayed in these two text boxes may not be the same. The name in the Dictionary box represents the text used by Geosystem to label its data fields, while the name in the Table box represents the text that will be exported as a column title. The other boxes can be safely ignored, as anything entered in them will not be exported.  Geosystem shows them only because the software developers have used the same window for printing data summaries. You may wish to use the “Title:” box to enter a note describing the purpose for which you created this export configuration. Likewise, the Layout menu only pertains to printed summaries.

4 Figure 4. The Column Definition Window

The order that you drag your data fields determines the order of the columns in the exported data file. Unfortunately, Geosystem does not offer any easy means of changing this order once you have created it (although a future version of the program will fix this issue). The only way to change the order is to delete all the data fields between the desired location of a data field and its current location. Therefore, you should take care to add fields to the Table box in the exact order you want to see. To delete a data field from the Table box, double click the field and select “Delete.” gINT will interpret the fields correctly, no matter what order they are in, but human operators may become confused by a difference in order between the exported data and the order displayed in gINT.
Once all the data fields have been selected and put into the correct order, the column titles should be renamed. You can change the names of the column titles in MS Excel or a text editor, but saving the new field names to a configuration file will save you the effort of retyping column titles again and again. To rename the data field, double click on it in the Table text box, change the name in the Column Title text box and select “OK.” When writing field names, avoid using quotation marks and whatever characters you will use to delimit the file.
You may have noticed two check boxes in the Column Definition window. Do not check either one. gINT or Excel can do much better statistical analyses. 
Once you have confirmed that all of the data fields are in the correct order and have the correct titles, go to the File menu and select “Save Configuration.” Once you have saved the configuration file, go to the File menu and select “Export to Disk…” Choose your directory and file name and select “OK.” (The file name cannot be more than eight letters long. If you can give the text file the same name as the gINT table to which it will be imported, that will save a step further on.) This brings up the Data Export Options window (See Figure 5). Unfortunately, these options are not saved in the configuration file. Instead, the program itself seems to store the settings from the last use. (This is another feature that may be changed in the next version of Geosystem.) Thus, you will need to check that these settings match the requirements for the exported data file.

5 Figure 5. The Data Export Options Window

I recommend the use of tab as the “Separator character:”, since no Geosystem fields contain tab characters. This is because the Data Entry window interprets any tab keystroke as the cue to move the cursor to the next data entry field. You also should check “First row has column titles.” Do not check “Surround each item with quotes.” If your data file contains quotation marks, this option could cause gINT or Excel to parse your file incorrectly during import. Select “OK.” Geosystem will now create the exported data file. This process can take quite a while with a large project or a slow computer. Once the text file has been created, you need to make a few modifications before it can be imported to gINT.

How do I prepare the exported file for import to gINT?

At this point, you need to decide whether to use ASCII text files or Microsoft Excel files as an intermediate data format. I recommend Excel files because the easiest way to prepare text files for import to gINT is to modify them in Excel or another spreadsheet program. In order to open the file properly, start Excel first; then open the file. Do not right click the file and select “Open With.” You will now see the Text Import Wizard (See Figure 6). If you chose Tab characters to delimit the exported text file, just select “Finish.”

6 Figure 6. The Text Import Wizard Window

gINT needs to know which table this spreadsheet corresponds to. Set the sheet name by right-clicking the tab at the bottom of the screen and renaming the sheet to match the gINT table name.
Take a look at the data contained in the various columns. Is it reasonable? If not, you may have typed the column title into the wrong data field. If you have saved a copy of your configuration, this will be easy to check. If all of the data appears to be correctly labeled and ordered, your data has been exported successfully.
Note that Geosystem exports most data in the form of a header row that contains the boring number followed by a series of rows containing subsurface information. In order for gINT to associate the subsurface information with the relevant boring, every line (except lines contained in the project table) must have a boring name. Fill any blank cells within the PointID column. For large files, it is faster to use the following method:

  • Insert a column into the A position.
  • Set up each A cell to imitate the one above it by entering “=A1” into A2 and copying the formula into all subsequent A cells.
  • Copy the PointID column into column A, with the following option checked in the paste special window: Skip blanks. (See Figure 7.)
  • Copy the contents of A into the PointID column, with the following option marked in the paste special window: Values.
  • Delete Column A.

7 Figure 7: The Paste Special Window

In some tables (usually the notes table in RK&K files), much of the data may not have a depth associated with it. These depths must be filled in by hand, as they must reflect the nature of the data itself and because the depths cannot conflict with (be the same as) depths already listed in the same boring and table.
Geosystem keeps most of its data in a single table. This means that when strata are exported, it is associated with numbers that represent sample depths, not strata boundaries. This means that each stratum will have a gap before the next stratum starts. The user must fix this by adding the correct bottom depth for each stratum in the Lithology table.
Sometimes the exported data will not be compatible with the gINT data format. For instance, if a field is numeric in gINT, any unit symbols must be removed from that column of the exported file. Likewise, now is the time to do unit conversions. Because gINT’s Find/Replace tool is flawed, you should take the time to replace graphical symbols while the data file is opened in Excel. Once you have converted all of the data into its final form, save the exported data file as either an .XLS or an .XLSX file.

How do I import the data file into gINT?

Once the exported data has been saved into an Excel file, it is quite simple to import. From the File menu, select Import from Excel file and locate the exported data. (See Figure 8)Then select “Ok.” gINT has a parent/child database structure, so you must import parent tables first. For the RK&K standard format, the “Point” table must be imported first. Once gINT has imported the data, it will present a log that describes any problems. Check the log for errors, rectify their cause, and import the Excel file again.
Once each of the tables has been imported, the gINT file is complete. Congratulations.

 8 Figure 8: Importing from Excel Files

What if I choose to transfer the data with an ASCII text file?

The main advantage of using an Excel file rather than an ASCII text file is that you will not have to choose between removing commas and quotation marks from your data file. Otherwise, the process is quite similar. Since gINT can parse Excel files, this method is not recommended.
gINT requires that text files be delimited with commas prior to import. This means that any commas in data fields will interfere with the import process. Although there is a method for dealing with this issue during export, you may be forced to choose between eliminating all of the commas from your data file or all of the quotation marks. As a general rule, it is easier to replace “”” with “–inches” or “in.”, than it is to find a substitute for the comma. The easiest way to carry out replacement of a specific character is to use Geosystem’s replace function. To do so, go to the Project menu and select “Replace Text…” This brings up the Find/Replace window (See Figure 8). Enter the character you wish to eliminate and the text you wish to replace it with. Then select “Replace All.” Unlike many other Windows applications, Geosystem will not automatically replace every instance of the character. Instead, it will ask the user to verify every replacement. While this is a slow process, it does give you the opportunity to replace the eliminated character with different strings in different contexts. You must do this for every project data file that is to be exported or, preferably, do it once for a merged project data file.

 9 Figure 9. The Find/Replace Window

What if I want to use a correspondence file instead of the Geosystem configuration file?

In order to remain concise, this guide does not cover this subject. However, use of a gINT correspondence file would remove the necessity of many operations such as find/replace within Excel or Geosystem. Correspondence files do not eliminate completely the necessity to prepare the data file, since most tables still need to have boring numbers or depths added. However, use of a correspondence file can make conversions of units and coded data (such as soil graphics) faster and more reliable. For help with correspondence files, you may wish to consult gINT software’s “Data Design Tutorial.”

Appendix: A Sample Table Transferred from Geosystem to gINT

In preparation for a major highway project, I had to create subsurface profiles for a number of bridges located in Delaware. The data had been stored in Geosystem files at the client’s behest, but gINT offered the opportunity for greater customization of those profiles. Also, RK&K used gINT to store all other geotechnical data, so conversion of this data meant greater possibility of reuse on future projects.

I did not know whether the client would require me to use the combined LD4 file in the future, so I decided to add calculated fields in Geosystem (See Figure 3). In addition, I had to add several fields—such as preconsolidation pressure and initial void ratio—to the Data Entry Form because the file had been created with Labsuite and I did not have a license for that module. 
Each of the bridges had its own Geosystem data file. Since the process of converting from Geosystem to gINT is quite time-consuming, I decided to merge all of the bridge files. Before doing so, I created a new data file in order to avoid making any changes to the original bridge data. Then, I used the project merge function.

Next, I had to make the export configurations. I decided that since gINT stores data in multiple tables, the simplest method of exporting data to it would be to make one configuration file for each table in gINT. I began with Point and Project, because the point table must be imported into gINT before any dependent tables. Once I had exported these, I could create the other tables, including the one called Laboratory Data. Geosystem is limited to eight characters when naming some files, so I saved both the exported file and the configuration file under the title “LabData.” Nearly all of the data to be exported to the Laboratory Data table falls into the Geosystem category “Additional Subsurface Data.” However, the boring name (aka PointID, aka Source Name) comes from another section of the Geosystem data file. As I was about to find out, this created a little extra work to be done in Excel (See Section 6). I changed the sheet name to Laboratory Data, which is the same as the relevant gINT table, and saved the file.
Then, I imported the table into gINT, using the “Import from Excel File” tool. Finally, I checked the data to make sure that the units matched those displayed in gINT and to find any other errors. When I was sure that the file was ready, I moved on to transfer the remaining tables from Geosystem to gINT.