Earthwork Converter (Utility for importing Earthwork.txt file into Excel)

Syntax at the bottom of the seed earthwork input file (V8_EARTHWORK.inp) and seed earthwork run (available using GEOPAK Project Manager) creates a “column based ASCII file” that can be imported into Excel and manipulated for providing adjusted earthwork volumes and creating sub-totals of various earthwork volumes for placement at the bottom of Plan/Profile sheets.

More Information:
- using the earthwork input file.
- using the seed earthwork run.

The earthwork input file can be found on the server under O:\V8i_Resource\X_30\GEOPAK\Input_Files\

The seed earthwork run can be found on the server under O:\V8i_Resource\X_30\GEOPAK\Seed_runs\

The Excel workbook WFL_earthwork.xls can be found on the server under O:\V8i_Resource\X_30\GEOPAK\Bin and is composed of 3 worksheets:

Spreadsheet tabs with Instructions highlighted

The “Instructions” worksheet provides instructions for using the Workbook.

NOTE:
Your MSExcel security may be set too HIGH to run macros.
Before attempting to run this spreadsheet, select
Tools > Macro > Security and set the security to Medium.

Spreadsheet tabs with From GEOPAK highlighted

The “From GEOPAK” worksheet is used only for inserting and updating the text file created when your earthwork input file (or earthwork run) is processed. Totals for the various volumes are provided at the top of this worksheet for a quick check against your earthwork “.log” file. Follow these steps:

  1. In the From GEOPAK worksheet press the CLEAR Contents button (see below). This cleans all data out of the worksheet and turns off the cell protections.
    Portion of From GEOPAK worksheet showing the CLEAR CONTENTS button
  2. Import the text file that was created when you ran your earthwork from GEOPAK. This file should be in your working directory and should be named earthwork.txt unless you have changed the name in the earthwork input file (or run).

    To import the file, make sure your cursor is positioned on cell A7, use the Excel pulldowns, Data > Import External Data > Import Data, as shown below:
    MSExcel drop-down showing location of Import Data
  3. Browse to the location of your column based ASCII file, earthwork.txt. Highlight the file and click on Open as shown below:
    Import Text File dialog box showing earthwork.txt selected
  4. Adjust the Original data type to Delimited and set the Start import at row: to row number 3. Click Next >, as shown below:
    Text Import Wizard (Step 1 of 3) dialog box showing key areas highlighted
  5. The dialog below will appear. This dialog allows the delimiters to be adjusted. Assure that Space and Treat consecutive delimiters as one are checked and click Finish, as shown below:
    Text Import Wizard (Step 2 of 3) dialog box showing key areas highlighted
  6. The Import Data dialog will appear as shown below. Assure that the toggle Existing worksheet: is selected and that the cell selected is A7. Click OK as shown below:
    Import Data dialog box showing OK selected
  7. The data will import into the worksheet From GEOPAK, however not all the data will be visible. Press the FIT Contents button to resize the columns and set the protections as shown below:
    Screen shot of spreadsheet after data import

    The result is shown below:
    Screen shot of spreadsheet after FIT Contents has been run

This completes the process of importing the earthwork input file into the From GEOPAK worksheet. The column totals shown in Row 4 may be checked against your GEOPAK earthwork log file.

To refresh the data in the worksheet, press the button, REFRESH Data. The macro will ask what file should be imported. By default the existing file will be chosen. Either select this file or find a different earthwork input file to import.

Spreadsheet tabs with Adjustments highlighted

The “Adjustments” worksheet is used for entering shrink/swell factors, inserting (or deleting) rows for Added Quantities, and creating Sub-totals for Plan/Profile sheets. A fragment of the results (from the "Adjustment" worksheet tab) are shown below:

Screen shot of Adjustments worksheet

All data from the From GEOPAK worksheet is copied over to the Adjustments worksheet. Additional columns are also set up for manipulating the earthwork volume data. The additional columns include:

  • SUBTOTALS for UNADJUSTED Roadway Excavation volumes.
  • SUBTOTALS for Embankment volumes.
  • SUBTOTALS for Subexcavation volumes.
  • SUBTOTALS for Topsoil Excavation volumes.
  • Shrink/Swell factors.

Using Subtotals:

Subtotals are typically required for defining areas of excavation/embankment and subsequent placement at the bottom of the Plan/Profile sheets. For example, if a subtotal is desired for Unadjusted Roadway Excavation from 50+000 to 50+300, then:
  • Highlight the range of cells to be subtotalled, (i.e. B7:B22).
  • Click on the Σ button and the subtotal will appear in the column adjacent to the last cell selected, (i.e. C22) as shown below.
    Screen shot of Adjustments worksheet showing an area highlighted which will be summed

Using Shrink/Swell factors:

Normally, various soil types are encountered when constructing projects potentially resulting in varying shrink or sell factors. The Geotechnical report usually defines where these areas are located. Sandy material will shrink and rocky material will swell.

A generic shrink/swell factor may be entered on the spreadsheet (see cell D2) that relates to UNADJUSTED Roadway Excavation. Once this value is changed the entire D column inherits this value. It is possible to change this shrink/swell value as necessary in isolated cell ranges within column D to reflect other soil types. The results automatically appear in column E (ADJUSTED Roadway Excavation) and the Column E total is recalculated.

For example, note that Station 50+140 through 50+260 uses a factor of 1.0 to calculate the Adjusted Rdwy Ex. The values in B14:B20 equals E14:E20.

Screen shot of Adjustments worksheet showing changes to the default shrink/swell factor

Using the INSERT and DELETE row buttons:

Many projects require that “Added Quantities” from additional areas be added to the station ranges when placing the quantities on the Plan/Profile sheets. Since this workbook and its worksheets are protected (by default), it is not possible to simply insert or delete a row for additional volumes. The following buttons allow rows to be added or deleted:

The 'INSERT a row for Added Quantities' button
  • The macro unprotects the worksheet.
  • Adds a row where the cursor is positioned.
  • Makes some security adjustments.
  • Copies the shrink/swell factor for the volume just above it, rather than use the generic value.
  • Runs a formula where the manually entered value in row B is multiplied by the shrink/swell factor in row D and puts this calculated value in row E, (Adjusted Rdwy Excavation).
  • Changes the color of the cell in row A to white in order to easily distinguish where the rows were added.
  • Protects the worksheet.
The 'DELETE Added Quantities row' button
  • The macro unprotects the worksheet.
  • Deletes the entire row.
  • Protects the worksheet.