Prepare a Spreadsheet for Import

Several spreadsheet formats are supported by Trimble Quest, including xls, xlsx and csv. Regardless of the file format, these BOQs typically have a similar structure. Each bill Record, be it a bill Section, a bill item or a bill Comment, is displayed on a separate row. The individual fields of a record, for example, Item Nr, Description, Unit, etc., are spread across separate columns. As long as this basic format is maintained, namely rows for records and columns for fields, Trimble Quest will be able to import a BOQ from the spreadsheet. Columns may appear in any order and the spreadsheet may be split over several worksheets. Nevertheless, it is advisable to edit and clean the spreadsheet pre-Quest importation for optimal results. To best understand how to go about this process, it is necessary to understand how the Trimble Quest BOQ importer works under the hood.

Before Trimble Quest starts the import process, a user has to identify the applicable columns that must be imported. This column identification is done via a visual BOQ Import manager in Trimble Quest and no editing of the original spreadsheet is required. Once this process is complete, Trimble Quest runs through the spreadsheet row by row from top to bottom. If a row contains either a bill quantity or a unit, Trimble Quest will assume that the row is a bill item, and it will create and insert a bill item in the Trimble Quest BOQ, e.g. Item A, Figure: Spreadsheet BOQ. The properties of this bill item will correspond to the fields present in the columns of the imported row.

If a row has neither a bill quantity nor a unit, Trimble Quest will assume that the row is a bill comment, e.g. Item B, Figure: Spreadsheet BOQ. A bill comment will then be created and inserted in the Trimble Quest BOQ. The properties of this bill comment will correspond to the fields present in the columns of the imported row. It should therefore be noted that any lines in the BOQ not relevant to the pricing process will be imported as bill comments if they are not removed from the spreadsheet prior to importation. Consequently, the first cleanup step that users are encouraged to take before importing is to remove all rows/lines from the spreadsheet that they do not want present in the Trimble Quest BOQ, e.g. Item C, Figure: Spreadsheet BOQ. Note that blank rows/lines will automatically be ignored during import, so it is not necessary to remove them.

Since bill sections do not have a quantity or unit associated with them, bill sections will also automatically get imported as comments if the original spreadsheet BOQ is used unaltered. This is obviously not ideal since bill sections have an important role to play in both the reporting and visual layout of a bill of quantities. Consequently, users must identify which lines in the spreadsheet represent bill sections.

To achieve this, users must insert an extra column anywhere in the original spreadsheet BOQ. We refer to this as the Section Depth column and it will be used to identify the level of each bill section. The spreadsheet data will be imported relative to a section in the project BOQ, which may be the root section or any other section already in the project BOQ. Therefore the section depth in the spreadsheet BOQ should start at 2. Similarly, deeper level subsections must be marked 3, 4, etc, up to any depth required. The inclusion of the Section Depth column will allow Trimble Quest to identify all the applicable sections of the BOQ, and organize their children accordingly, e.g. Item D, Figure: Spreadsheet BOQ.

An example is shown below:

Next topic: Import into Trimble Quest