Spreadsheet Rules

The source of the data that will be imported into the Cost Breakdown Structure (CBS) Register using the import routine is a Microsoft Excel spreadsheet. The number of columns and rows in your spreadsheet is only limited by the number of columns and rows that is supported by the version of Excel in which the spreadsheet was created. In order to effectively read and validate the data in the spreadsheet prior to import, there are certain rules that must be followed when setting up the spreadsheet that will ultimately be used as the data source for the import.

Saving the Spreadsheet

The import routine will import the currently saved data in your spreadsheet. If you have made modifications to your spreadsheet but have not saved them, the unsaved data will not be imported. It is important that you save your spreadsheet prior to importing to be sure that the most current data is imported into Estimate.

Column Headers

Each column in the spreadsheet represents a data field in Estimate, specifically in the Cost Breakdown Structure (CBS) Register. In order to map the data columns in the spreadsheet to the data fields in Estimate using the import mapping tool, each column in the spreadsheet must have a column header. The column header must be limited to one row and populate row 1 on the spreadsheet. The text of the column header does not need to match the data field titles in Estimate, and it can be any text that is most meaningful to you.

Blank Columns & Rows

The import routine reads the data in the spreadsheet and validates it prior to import. In order to read and validate the data in the most efficient manner, the import routine reads the data in the spreadsheet row-by-row and column-by-column and stops reading when the first blank column or row is encountered. Therefore, when creating the spreadsheet that will be used as the source of data for the import, it is important that no blank columns or rows exist in the data region that will be imported.

If a blank column exists in the spreadsheet, and columns after that blank column were previously mapped, a dialog will be presented to you after clicking the Import button, indicating that previously mapped column headers were not found in the spreadsheet.

In this case, you will be required to either remove the blank column in the spreadsheet, or remove the mappings for columns after the blank column using the mapping tool.

If a blank row exists in the spreadsheet, any data in the rows following the blank row in the spreadsheet will be ignored and will not be imported. In the example below, the import routine will stop reading the data at row 3 (Structure Excavation), and the Excavation cost item will not be imported.

Blank Cells

When importing data from a spreadsheet, blank cells in the spreadsheet are considered importable values as long as the column with blank cells is mapped. The effect that this will have on your data after import is determined by the data rules for the various fields in Estimate. Depending on the field, the result may be a blank field in Estimate as well, a value of zero, or an error if a value of zero is not acceptable.

The following are examples of the results you can expect when importing blank cells into the data fields in Estimate:

  • Description – When adding a new item, the result will be a blank Description field on the Cost Breakdown Structure (CBS) Register. When updating an existing item that currently has data in the Description field, the result will be the Description field being changed to a blank field.

  • Forecast (T/O) Quantity – When adding a new item or updating an existing item, the result will be a zero or blank space in the Forecast (T/O) Quantity.

  • Work Hours Rules – When adding a new item, the result will be the cost item having the Work Hours Rules value set to zero. When updating an existing item that currently has a value other than zero in the Work Hours Rules field, the result will be the Work Hours Rules field being changed to zero.

Invalid Data

Prior to import, the data in the spreadsheet that is currently mapped (intended for import) is validated against the business rules in Estimate. In the event that invalid data exists in the spreadsheet and has been mapped for import, the user will be presented with a dialog identifying the data in the spreadsheet that is invalid, and a brief explanation of why the data is invalid.

If invalid data is detected, no data is imported and you will be required to correct the invalid data in the spreadsheet and re-import.

In addition to the data being validated against the business rules in Estimate, some data is validated against spreadsheet rules. For example, when importing the Schedule status for a cost item, since this field is a Boolean field, the import is looking for a value of 1 in the spreadsheet to designate the cost item as being scheduled or 0 to designate the cost item as not scheduled.

For Boolean fields, 1 = true and 0 = false. Any data in these fields other than a 1 or a 0 is considered invalid data.

Resource Columns

The import routine provides the user with the option to import one or many resources for a single cost item. An option on the Define Mapping dialog dictates whether or not the user wants to import more than one resource into the same cost item. Selecting the Import additional Resources into the same cost item by moving to the right of the last mapped field option allows the user to import multiple resources into the same cost item.

In addition to selecting this option, the Cost Item Employed Resources & Hours mappings and your spreadsheet columns dictate whether or not multiple resources will be imported. In most cases, the spreadsheet contains columns for all six resource mapping fields (Resource Code, Resource Description, Resource Quantity, Resource Cost, Resource Type, and Resource Work Hours). Though you will, generally, not be mapping all six of these fields for an import, it is best that you have all six of these columns in your spreadsheet unless there are columns that you will never use. Each resource that you want to import into a cost item must have its own set of resource columns.

To import multiple resources into the same cost item, the import looks for a resource code in the first column following the last mapped resource column.