Back to The Complete Idiot's Guide
Back to EARTHWORK

EARTHWORK2002.MDB

(Utility to convert Earthwork Log (.txt) File to a Spreadsheet format)

FROM THIS:

TO THIS:

WESTERN FEDERAL LANDS
FEDERAL HIGHWAY ADMINISTRATION
VANCOUVER, WASHINGTON

PETE FIELD and CHUCK DISSEN June 27, 2003

INTRODUCTION

Geopak produces a text file that summarizes the earthwork data for a proposed alignment. The file is more of a word processing type of document with a Grand Summary for all the different layer types at the end. Usually the designers would like to sum certain parts of the earthwork data, like within a certain station range. If that data was in a spreadsheet the designer would be more versatile in using the earthwork data.
The EARTHWORK2002.MDB menu driven utility was developed to run inside of Access 2002 for just for this purpose. Now a designer can load their earthwork data from Geopak into Access 2002 and use the EARTHWORK2002.MDB utility to convert their data from an ASCII format to a spreadsheet format. Once in the spreadsheet format, the designer has full power to manipulate, question, and check their earthwork data.

INSTALLING ACCESS 2002:

Microsoft Access 2002 is available from the HELP DESK. WFLHD has a site license for Access 2002 and can run enough concurrent copies that all of design can have a copy on their workstation. Currently, Access 2002 is part of the standard CADD workstation load. If you do not have Access 2002, contact the HELP DESK.

SUPPORT:

Pete Field and Chuck Dissen are available in the office to support the EARTHWORK2002.MDB utility.

INSTRUCTIONS:

The following instructions detail how to operate the EARTHWORK2002.MDB utility.

APPENDICES - SPECIAL INSTRUCTIONS:

Appendix A and Appendix B have some special instructions for working with log files that have added quantities and for working with large (over 1 Mb) log files.

EARTHWORK DATA CONVERTER:

BASIC INSTRUCTIONS

Note: If you have an earthwork log file with added quantites, see Appendix A on added quantities-checking the EW.TXT file.

Download the file EARTHWORK2002.MDB to your working directory (for this example c:\myfiles\myjob).

Open the EARTHWORK2002.MDB file either by double clicking on it from Windows Explorer or opening it with Access 2002.

Once the EARTHWORK2002.MDB file is opened in Access 2002, the following main menu appears:

From this menu choose IMPORT EW FILE and the program opens a dialog box that looks like the following. In this dialog box, simply enter the full drive and path name of your EW.TXT file, for example: C:\MYFILES\MYJOB\EW_MYJOB.TXT and then press the IMPORT LOG FILE. If an error occurs, choose END from the error dialog box. The most likely cause of the error is not having the full path and filename correct.

The next step is to convert the data by choosing the CONVERT EW DATA on the main menu. Press the CONVERT EW DATA button and depending on the size of the file it will take a few minutes or a few hours. EW.TXT files that are under 500kb typically take less that 10 minutes. You can speed up the process by combining the excavation types in your EW.TXT file. For hints on working with large ew.txt files and monitoring the status of the data conversion, see Appendix B - working with large EW.TXT files.

After the data conversion, choose VIEW EW HORIZONTAL to show the data in a form suitable to copying to a spreadsheet program for you to edit, format, sort out a specific station range, sum just a certain layer, or sum the totals for the project. VIEW EW HORIZONTAL returns the following spreadsheet view:

To copy the entire spreadsheet view from Access 2002 to EXCEL, simply press on the small square in the upper left corner of the spreadsheet view (see where the cursor is pointing). That will select the entire spreadsheet view. Now, from the EDIT menu, choose COPY. Then open EXCEL, and choose PASTE from that EDIT menu.

If you would like to check the that the totals for your EW.TXT file were properly processed by the EARTHWORK2002.MDB program, choose VIEW LAYER_TYPE TOTALS and a summary view of the spreadsheet view will be displayed. Simply verify these totals with the totals in the G R A N D S U M M A R Y section of the EW.TXT file.

Appendix A - ADDED QUANTITIES - CHECKING THE EW.TXT FILE

THE PROBLEM:

Geopak has the option of adding or subtracting quantities at set stations along the alignment.   The user first does a normal earthwork run, then adds/subtracts the given quantity for a given station using Geopak.   Geopak then edits the EW.TXT file to put in these added quantities.

The problem is that the edit for the added quantities is not always consistent in its manner of placing the added quantities in the EW.TXT file.   The following example will explain how to check for these inconsistencies and fix them.

In this example, quantities were added at stations 20+140, 25+414.174 , and 22+491.24.   The following are examples of how Geopak edited each added quantity:

20+140        CK                           Add/Sub Volume
              Common Exc       ------         455         500   1.10           
              Fill             ------           0           0   1.00

25+414.174  OCK                           Add/Sub Volume
              Fill             ------         700         700   1.00

And then the proper format:

22+491.24   ROCK                           Add/Sub Volume
              Common Exc       ------         273         300   1.10           
              Fill             ------           0           0   1.00

The EARTHWORK2002.MDB file does not care what the layers are named. One designer could have a layer called RDWAY_EXC and the other ROADWAY_EXC and the utility will work. However, the layers have to be consistent in the EW.TXT file for the totals to add up right.

THE FIX:

If you have added quantities, before running the EARTHWORK2002.MDB utility, check the EW.TXT file for inconsistencies. Open your EW.TXT file in a text editing program. Search the file for the text ADD/SUB and the search feature will lead you to each place an added quantity is. Once at the added quantity location in the EW.TXT file, check that the layer (ROCK, COMMON, etc.) is correct. Then check that the location of the layer is not immediately above the type (Common Exc, Fill, etc.)

For our example we would correct the added quantity line at station 20+140 to:

20+140      ROCK                           Add/Sub Volume
              Common Exc       ------         455         500   1.10           
              Fill             ------           0           0   1.00

And we would correct the added quantity line at station 25+414.174 to:

25+414.174  ROCK                           Add/Sub Volume
              Fill             ------         700         700   1.00

Make sure that the layer (ROCK) and the type (Common Exc, Fill) DO NOT line up:

25+414.174    ROCK                         Add/Sub Volume
              Fill             ------         700         700   1.00

(This will make the EARTHWORK2002.MDB misplace the quantity for this layer)

Save the correct EW.TXT file and you are ready to use the EARTHWORK2002.MDB utility. The added quantities will be listed in the EARTHWORK2002.MDB utility results and the total for each layer and type should add up correctly.

Appendix B - WORKING WITH LARGE EW.TXT FILES

THE PROBLEM:

Access 2002 is better at working with existing data than filling data in. A big part of what the EARTHWORK2002.MDB utility does is fill in a station and a layer for all the quantities shown in the EW.TXT file. That takes time, but luckily it is computer time and the just can just let the program run.

EW.TXT files that are 1.0 Mb and under, especially 0.5 Mb and under, run relatively quick (less than 15 minutes for files under 0.5 Mb, depending on the computer). Bigger files can take hours. The relationship between file size and processing time seems to not be linear, bigger files take many times longer.

TIPS (for files over 1.0 Mb):

If you have to need a set of quantities for a large job done quickly, break up your earthwork runs to smaller segments. For example, if your EW.TXT file was 2.5 Mb, that would take 6 hours plus to run. Break it up into 2 segments, EW1.TXT and EW2.TXT and that time to run both files will be done in a couple hours.

Consider combining excavation types. Combine Common Exc, Subgrade Exc, and Subsoil Exc as a choice in your input file before you run earthwork. This is only if you do not need these quantities separated.

Eliminate the under layers (such as ACP and AGGREGATES). Comment out these layers before running your earthwork. This will eliminate 2 to 3 layers and will reduce the size of the EW.TXT.

Run your EARTHWORK2002.MDB - CONVERT EW DATA overnight. This will let you let the computer do the work. One thing to watch with this is the network backup at approximately 11:30pm, which might affect the data conversion process.
Back to The Complete Idiot's Guide
Back to EARTHWORK