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.

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

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

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.

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.

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

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.

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.

  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.

  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.

Reviewing Reg Review, Part 1

A month ago, I walked into a hotel just south of Philadelphia. I had arrived only 5 minutes late, but one of the instructors had already begun. And no wonder, for she intended to cover in exactly 8 hours the whole of geological knowledge. Those unfamiliar with Reg Review’s mission may wonder why anyone would claim to do such a thing and why anyone would pay her hundreds of dollars to attempt it. The answer is simple: she was helping prepare students for the ASBOG, a licensure exam that featured a 55% pass rate in its most recent administration. Obviously, a lot of geologists need help studying. But does Reg Review really help? And who needs it most?

Reg Review claims that its students pass at a rates that range from 85% to 99%. While it wasn’t really possible to verify this, a friend of mine vouched for Reg Review’s study guide. Since my employer was generous enough to pay for the one-day preparatory course, I decided to give it a shot.

Reg Review supplies a study guide and a booklet of practice questions as part of the course, but these are also available for $145. In addition, students are issued another set of problems to answer prior to the class. Unfortunately, I did not bother to look at any of the materials before the day of the class. Here’s a tip, folks: look at the course notes at least a week before class starts. It’ll help.

The “Course Notes” were yet another set of sample problems, arranged to provide coverage of most ASBOG subjects. These notes formed the backbone of the day, as the instructors gradually worked their way through the answers and added comments and lectures as necessary. They also provided suggestion after suggestion for further reading, far too many textbooks to actually buy or study from. However, one feature of the class struck me most forcefully: a heavy dose of fear mongering.

To hear Reg Review tell it, the ASBOG exam is composed by a queer mixture of the devious and the incompetent. Furthermore, the sample questions given on the ASBOG website are completely misleading in regards to the breadth and difficulty of the exam. Certainly, Reg Review addresses the issue by supplying more challenging problems. This leads me to wonder if Reg Review’s statistical success is due more to the determination they inspire than to the quality of their materials. I have not finished reading the main study guide, but it seems like a good review so far.

Since I plan to take the ASBOG test in March 2010, I certainly can’t render any final judgments. However, I’m certainly looking forward to writing Part 2.

Q+A with VES, Inc.

LiveJournal Tags: VES,Labsuite,LD4,Geosystem for Windows,CADD,problems

Not too long ago, I asked a few questions about Geosystem LD4, some stupider than others. Since Geosystem doesn’t have a forum, I thought that I would share some of my problems and solutions here:

> Program presents: Could not read bitmap file. I checked your site and  saved it as a 24-bit file, but that did not help. The file is in 
C:\program files\geosys, which is where it should be. Any ideas? Thomas,
Typically, if the logo file's placed in the proper directory, this message will result from adding a ".BMP" extension when adding the logo to the report form: The program's "Pictures and Logos" dialog needs to have the name of the logo file you're adding, *without* the file extension; e.g., MYLOGO instead of MYLOGO.BMP. (We're dropping that requirement from the next program release, as it tends to confuse people unnecessarily.)
Go back to the report form editor ("Tools" > "Report Form"), select "Search" > "Find", enter
logo
into the "Search for" box, click the "Search" button, double-click on the first search result, then click on the "Edit" button of the following dialog. Finally, check what you've entered into the "Pictures and Logos" box and remove the .BMP at the end of the logo file name, if it's present.
If we haven't properly diagnosed the problem, could you e-mail us the logo file, along with the *exact* contents of the error message that you're getting?
Best Regards,
GEOSYSTEM Software Technical Support
http://www.geosystemsoftware.com
Contact technical support at http://geosystemsoftware.com/support.htm

Thanks, Geosystem, you nailed that problem. Now here are a few more questions:

Mr. Stanley,
Thank you for taking the time to write to us. We'll try to address your questions inline below. Please to contact us if you need further clarification on any response.
> * Why do horizontal lines on Subsurface Profiles occasionally appear dotted after being exported to .DXF format? (The lines correctly displayed as solid lines in LD4.)
We're not sure. Judging from the context of your next question, you're importing the .DXF file into MicroStation. Since the files import correctly into AutoCAD as well as many .DXF file viewing packages, there must be a specific oddity in the way MicroStation interprets LD4's files. We'll look into the matter.
> * Is VES, Inc. aware of any way to add .RSC fonts to the options in Geosystem?
No. The software uses Windows TrueType fonts only.
> * DelDOT is not among the agencies with forms posted on the Geosystem website.

Apologies for the oversight; DelDOT are clients of ours and we've built their boring logs for them. The logs have been posted to our website at: http://geosystemsoftware.com/ld4forms/DelDOTLogForm.htm
* There is no import facility of any kind.
We've only encountered a couple of clients over 20 years who have had specific import applications in mind (as opposed to just thinking it might be a nice thing to have), so an official import tool isn't high on our list of must-add features. We do have in-house tools that can import data from spreadsheets, databases and text files, and, through the program's scripting features, easily add custom importers as needed, so please feel free to let us know specifically what you require in the way of data import.

At first I thought that they were kidding. Then I realized that there was almost no reason for anyone to move data into Geosystem, when you can do almost anything with Mathematica, Excel, or ArcMap.

> * Is there a way to trigger calculation of the values of a newly added field?
There isn't a way to force a calculation update. We're checking to see if that option can be ported into the current version of the software; we'll let you know within the next few business days whether that will be possible.
> * Changing the order of the columns of exported data should be easier.
You're right, the current method of deleting and re-adding the data is cumbersome. We've already rewritten the tool for the next program release, and columns can now be shifted using drag-and-drop.

Thank goodness.

> * Data export options are not saved in  configuration files.
Here again, we have corrected that in the next release of the exporter.

Good work.

> * The replace text tool is buggy, e.g. 9"
becomes -i9nches, not 9-inches.
Thank you for reporting that. We've been able to duplicate the bug (it only happens when the word to be replaced appears at the end of a line of text) and we'll try to get it corrected ASAP.

You’re welcome.

> * The limit of an 8.5x11 sheet of paper is very constraining. Custom sizes and shapes are desirable.
The option to create custom form sizes is on the next version's wishlist, but it's fairly low priority so it may not make it into the final release. Your current program *does* allow you to pick ANSI page sizes such as B, C, D and E. In particular,         ANSI D is 34x22, so that should work perfectly for your .DXF export. Make the change by selecting "Tools" > "Report Forms", then "Sections" > "Form Settings", and change the "Form Size" box. Finally, to avoid modifying your original report format, select "Report" > "Save As" and pick a new filename.
If you're exporting an entire form, with plate areas, etc., you'll need to completely redesign the form to fit the larger area, but if you're merely exporting a stick log consisting of, e.g., a stratigraphy column, blow counts, etc., this method will work great.

This was quite helpful, actually. Thanks.

> * Individual or customizable layers for .DXF export would be desirable. (For instance, one for each boring.)
Thank you for the suggestion, we'll add that option to the next version's wishlist.
> * Need an option for printing only one elevation scale.
Could you clarify your request please? We're not sure exactly what you're asking for.

The elevation scale prints on both sides of the page. I only want it on the left. I’ll have to contact them again.

> * Neat  corners are not formed between elevation scales and footers.
Could you clarify what you mean? If you could fax a sample that would be great.

I guess I’ll have to make a screenshot and try again.

> * Needs callout lines for overcrowded text.
The program has several callout line options. If you could fax a sample of your report, we could recommend a good option for you.

True, but not the useful kind. I need callout lines that can deal with text that runs past the starting depth of the next text line. Maybe I’ll ask this one again.

> * Does the Labsuite software have a total unit weight data field?
Do you want the software to calculate the unit weight for you? It does not currently have that capability; however, you *can* modify your boring log data entry form to perform the calculation.
Best Regards,
GEOSYSTEM Software Technical Support

My file  contained Labsuite data, but I only had an LD4 license. If the unit weight data field were created by Labsuite, it could be brought into LD4. However, it appears that there is no standard unit weight field. Not to worry. It’s an easy calculation.

Overall, it seems that Geosystem is  flawed and VES is willing to improve it. It’ll be interesting to see what happens over time.

A Brief Guide to Exporting Data from Geosystem for Windows

Why should I read this guide?

Although extensive documentation for Geosystem’s export tool has not been published, Geosystem for Windows contains some helpful information. To access it, go to the Tools menu, select “Data Summary and Export…,” go to the Help menu, and select “Current Screen.” In addition, VES, Inc. has published a very brief guide to exporting data into a file compatible with Microsoft Excel. It can be found at: http://www.geosystemsoftware.com/support/gfwexprt.htm. New users can try out the Data Summary and Export Tutorial, a piece of bundled software that briefly but effectively demonstrates how to use the export tool. Finally, users may consult Creating, Saving and Archiving Laboratory Test Files with the GEOSYSTEM Data Manager, an interesting—but somewhat outdated—manual available at the Geosystem website.

All of these contain valuable information. However, they do not provide detailed recommendations for creating anything other than a generic spreadsheet. Adventurous users will be perfectly happy to experiment with Geosystem and develop a data export system that matches their unique needs. Everyone else has the opportunity to learn from the mistakes of others by reading this guide, especially the sample export system presented in the appendix.

This guide does not purport to fully explain all of the operations you may be required to carry out prior to exporting data. You should have some familiarity with Geosystem for Windows. Furthermore, this guide was created with Geosystem for Windows v2.1, LD4 module v4.1. While other versions of Geosystem for Windows are very similar, they may not be identical in function.

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 really 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?

• Will it be easier to use my exported data in separate files for each project or in a single exported file?

• Do I need to present my results in LD4 boring logs or can I perform some data manipulation outside of the software?

• In what file format will my exported data need to be?

• Are there specific field names that my final product will need to have?

• Do the units in my data file match the units I will be using at the end of the process?

Once you know what you are trying to accomplish, you can get started by making sure that your 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.

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 future reports will need to use a field not yet created, but required by the final use of the exported data. 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 might 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.

Sometimes the delimitation required for your final product will not be compatible with the text stored in some fields. Although there is a method for dealing with this issue during export, you may be forced to choose between eliminating all of the delimiting characters from your data file or all of the quotation marks. (This is because many programs use quotation marks to determine how to interpret delimiting characters.) 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 2.) 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.¹

Figure 2. The Find/Replace Window

Finally, you may wish to merge multiple project data files prior to export. Under some circumstances, it will 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.

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

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

Figure 3. 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 4.) 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. 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.)

Figure 4. 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. Now that all of the project files are combined, remember to replace any characters that could interfere with the exported data file’s delimitation.

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 goals. This also makes it possible to break up a single project data file into pieces that are more easily assimilated into your final product. 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 5.) Note that the names displayed in these two text boxes may not be the same. The name in the Dictionary box represents the string used by Geosystem to label its data fields, while the name in the Table box represents the string 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 purposes for which you created this export configuration. Likewise, the Layout menu only pertains to printed summaries.

Figure 5. 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. 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 need. To delete a data field from the Table box, double click the field and select “Delete.”

Once all the data fields have been selected and put into the correct order, the column titles should be renamed. If you will be importing the exported data file directly into a program that requires specific strings, this is an absolute necessity. If you will be opening the file in a program that allows text editing, you can change the names of the column titles later. However, keep in mind that most users will export their data using the same configuration file more than once. 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. These are self-explanatory, but I will note that the format in which the counts are presented is “total=”#. Many users will prefer a more sophisticated set of statistics available from any of the variety of programs that can use the exported data.

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.” This brings up the Data Export Options window (See Figure 6.) Unfortunately, these options are not saved in the configuration file. Instead, the program itself seems to store the settings from the last use. Thus, you will need to check that these settings match the requirements for the exported data file.

Figure 6. The Data Export Options Window

The three most common characters used to delimit (separate) data are probably tab, comma, and space. If you have a choice, tab is usually the best, as 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. Most Geosystem project data files will contain both commas and spaces. If you must delimit your exported data file with these characters (or any other character that appears in the data file,) you may check “Surround each item with quotes.” This will allow many programs to interpret the data fields containing delimiters as solid blocks, ignoring any characters that are actually part of your data. Unfortunately, this requires that your project data file contain no quotation marks. (See Section 2 for a description of how to easily remove problematic characters from your file.) Alternatively, you can export any file in a tab-delimited format and use a spreadsheet or text editor to modify it by hand. Since this is likely to be more work, you may wish to work out an export system that uses Geosystem to its full potential.

Unless you will be accessing your exported data using a software program that cannot accept column titles, you should check “First row has column titles.” The contents of the exported data file may be easy for you to interpret today, but they may not be so obvious to anyone who must review your work in the months and years to come. Once you are satisfied that the options will preserve your data structure, 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.

Am I done yet?

Once the file has been exported, you should open it up in a spreadsheet program to make sure that everything is where it should be. Take a look at the data contained in the various columns. Is it reasonable? If not, a couple of things may have happened. The first is that 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. The other possibility is that there are delimiting characters or quotation marks within your data that are being misinterpreted by the spreadsheet program. You must eliminate these or change the export options and export your data again. Do not save the file that you have opened up, as the spreadsheet program will change the way that the data is stored. If all of the data appears to be correctly labeled and ordered, close the file. Your data has been exported successfully.

You may find the final product unusable due to Geosystem’s inability to list all relevant data on each line. (For instance, each boring number only appears once.) This problem can only be corrected manually. Most users will be able to quickly fix their files in a spreadsheet program. However, due to the idiosyncrasies of spreadsheet file formatting, some users must use a basic text editor—such as Microsoft Notepad—to amend their data.

Appendix: A Sample Project Exported

For this hypothetical project, a researcher wishes to develop a regional coefficient for correlations between standard penetration test N-values and undrained shear strength in northern Virginia. To do so, she will be using a software program called NCAN to process the raw data into depth-based and geologically based families prior to determining the relevant coefficient. NCAN is very fussy about data formats and will only accept a comma-delimited text file with a *.ndf extension. Furthermore, the data must be stored in a table with the following format:

Depth	Stratum	SS	N
#	Text	#	#
#	Text	#	#
#	Text	#	#

Note that because the data is stored in a text file, the researcher does not have to create any special number format as she would for a spreadsheet file. However, she still needs to ensure that the data itself is stored correctly. NCAN’s manual states that Depth is measured in feet, Stratum can be any one-word description of the geology, SS stands for undrained shear strength measured in tsf, and N is the N-value.

The researcher has collected data from a variety of organizations. Some of the information has already been stored in Geosystem files, but most of it had to be typed into Geosystem. The researcher chose to enter the new data into the project data file which already contained a large quantity of information. Next, she merged the other project files into the main project data file. The researcher found it necessary to create a new data entry field called Stratum. For convenience, she placed Stratum and Material Description at the start of each card (See Figure 7.) Then, she decided upon several geological categories and manually entered the relevant categories into the Stratum field based on the material descriptions available.

Figure 7. Creating the Stratum Field

Although the results of laboratory testing for shear strength were stored in Geosystem’s standard field “Shear,” the units are pounds per square foot (psf.) The researcher decides to create a calculated data field to store the data converted to shear strength measured in tons per square foot (tsf.) She would use SS as the field name, but that has already been taken by a custom data entry field in one of the merged Geosystem project data files. So she uses “Shear2” instead (See Figure 8.) Unfortunately, Geosystem treats all calculated fields as subject to manual override. In this case, there is no data in Shear2, but the program understands this to represent a blank value entered by the user. Thus, the researcher must manually calculate and type each tsf value into the new data entry field.²

 Figure 8. Creating a Second Shear Strength Field

Once all of the data has been entered, the researcher prepares to export it (See Figure 9.) She places the fields in their final order, renames the column titles, and saves the configuration. Next she exports the data, using column titles and comma delimiters. Quotation marks are not needed because none of the data fields are likely to contain commas. Because the project data file is large, Geosystem takes about two minutes to export it. The computer’s resources are stretched by the export process, so the researcher takes a hard-earned break.

Figure 9. Saving the NCAN Export Configuration

When she returns, the data has been exported to NorthernVA.txt, the filename specified during the export process. The researcher opens NorthernVA.txt in Microsoft Excel, using the comma-delimited option. She scrolls through the data, making sure that everything is in its proper place. Because none of the data fields contained commas or carriage returns, all of the data is where it should be. The researcher closes the file without saving and renames it to NorthernVA.ndf. Now the data is ready for export to NCAN.

1 In my experience, the Replace tool in Geosystem is buggy, so you should scan the resulting text for errors. Due to this fact and the difficulty of use, it is best to clean data after export, if this is at all possible.

2 It would have been much easier to export the data in “Shear” and use a spreadsheet program to calculate and save the data into “SS.” However, this would have precluded the use of shear strength measured in tsf in boring logs or other reports created in Geosystem.

Upcoming Posts

Since I am not quite ready to make my first real post, I have decided to give you some idea of what to expect in the near future. Hopefully, the public commitment will inspire me to get these things done.

1. A guide to exporting data from Geosystem for Windows
2. A quick description of the easiest way to make said data usable
3. A description of how to get your data from Geosystem into gINT
4. Possibly a rant about Geosystem’s shortcomings
5. Possibly a guide to using gINT with dedicated GIS