Bill of Materials Import Utility

Once a project is created, the user may choose to import a Bill of Materials (materials take off list) file directly into the Impact Estimator.

The Impact Estimator has always internally calculated a bill of materials based on user design inputs, using rule-of-thumb algorithms. Users previously could not directly modify these default values, however they could somewhat customize the input through a feature known as Extra Basic Materials. Using that feature, additional materials can be added to the building at the Project level and (since version 5.2) at the Assembly Group level.

The embodied impact of extra materials is included in the LCA results. In particular, material effects due to Extra Basic Materials in the construction phase are calculated (since version 4.2) by assuming that a crane is used to move all Extra Basic Materials material through a distance equal to half the building height.

Before v4.2, in the construction phase, the on-site impact of of materials were calculated with algorithms based on the different assembly types (e.g. steel stud wall, concrete slab on grade, etc.). Any materials entered as Extra Basic Materials are not assigned to a specific assembly type, therefore no material impacts have been calculated (transportation effects are calculated for all materials) for Extra Basic Materials. Starting with v4.2, material effects due to Extra Basic Materials in the construction phase are now calculated by assuming that a crane is used to move all Extra Basic Materials material through a distance equal to half the building height.

The Bill of Materials import utility and its supporting "helper" utilities now make it possible for users to import material take-offs in a variety of file formats directly into the Impact Estimator as an Extra Basic Materials assembly.

The user can fine-tune the supporting "helper" modules, which means each time the Bill of Materials Import Utility is used it has the ability to become iteratively more efficient.

How does the Bill of Materials Import Utility work?

There are essentially three steps to importing a file using the Bill of Materials Import Utility. The user interface form contains one tab for each of the steps, in addition to a Summary tab that contains a summary of the import process that can be saved as a text file. The final tab displays the total percent complete for all of the steps.

Step 1: Load a File

The first tab is dedicated to browsing for and loading a file. The user can search their computer for a material take off (or similar) file to import. File formats that are presently supported include Comma Delimited Text (CSV), Tab Delimited Text, Other Delimited Text, and Excel. XML is not yet supported.

The user can iteratively load a single file, adjusting the file type filters as well as the header and data start row numbers so that each imported row is appropriately labelled as METADATA, HEADER or DATA.

The user can optionally select DATA (or other) rows to skip. This may be necessary if additional non-data rows are embedded between data rows. This is possible for each of the steps.

Once the file has been loaded, the "Step 2: Map the Columns" tab becomes accessible.

This step can be iteratively repeated until the data is properly read from the file and displayed in tabular format.

Load a file

Browse: Use this button to browse the file system for a bill of materials file to load.

File Type

Use this collection of radio buttons that help identify the type of file to be loaded.

CSV (Comma Delimited Text) - the data fields are each separated by a comma.
Tab Delimited Text - the data fields are each separated by a tab.
Other Delimited - the data fields are each separated by a custom delimiter character that needs to be captured in the "Other Delimiter" field.
XML - the data is contained in an XML file. This option is not yet supported.
Excel - the data is contained on a single worksheet in a Microsoft Excel file. Supported file extensions include (XLS, XLSX, XLSB, XLSM). The worksheet (or named range) can be selected from the "Worksheet" selection list.

Header Start Row #: Enter the row at which the header information starts. All rows occurring before the header start row are assumed to contain METADATA. All rows that contain METADATA or HEADER information will be processed, but will be "skipped" and hence will not be parsed for meaningful Bill of Materials data.

Data Start Row #: Enter the row at which the Bill of Materials data starts. All rows occurring before the data start row are assumed to contain HEADER (or METADATA). Only data rows will be parsed for meaningful Bill of Materials data.

Load the file: Use this button to load the selected file. Using the "File Type" and "Data Start Row" filters, the file is parsed and displayed in table format.

Clear: Use this button to return the form to the initial state. All information will be reset to its default values and all loaded data will be lost.

OK Button: Click the "OK" button to accept and save the imported Bill of Materials data to an "Extra Basic Materials" assembly and close this dialog. This button is only enabled once the Status Flag for all rows in "Step 3 : Map Rows" are complete.

Cancel Button: Click the "Cancel" button to cancel the Bill of Materials data import process, discard all imported data and close this dialog.

Bill of Material Import Utility Tabs

Step 1 : Load a File.
Step 2 : Map the Columns.
Step 3 : Map the Rows.
Step 4: Map the Material Contribution Types
Summary.

Bill of Material Import Utility Helpers

TIPS & TRICKS

Microsoft.Ace.Oledb.12.0 driver:: The BOM Import Utility uses the Microsoft.Ace.Oledb.12.0 driver to read material take-off files for the legacy Excel (XLS) file type. When you installed the Impact Estimator for Buildings, the installer attempted to install this driver. If for whatever reason this driver is not installed on your computer, you will need to install it in order to use the Bill of Materials Import Utility to load XLS files. The driver can be downloaded from the following URL: http://www.microsoft.com/en-ca/download/details.aspx?id=13255.
CSV, Tab Delimited, or Custom Delimited File Types:: Columns may contain "intermixed" values (e.g., numbers, dates, strings etc). The OpenXml library is used to read these files and is only able to consistently successfully interpret a value that does not conform to the "norm"for a column if it resides in the first row. Otherwise, the non-standard cell value is dropped. In other words, if you want the column name (e.g., Quantity) to be successfully interpreted when importing a BOM file, it is in your best interest to make sure that the source file has the column headers in the first row.
All File Types:: If you pre-map the column names to "Material", "Quantity", "UOM" and "Material Contribution Type", the BOM File Import Utility will automatically map the appropriate columns when you load the file.
Material Take-Off Data in Multiple Excel Worksheets:: If you have material take-off data spread across multiple worksheets in one or more Excel workbooks, you can iteratively import each worksheet - one at a time - and choose to "merge" the imported material quantities.
Material Take-Off Data in Multiple Files:: If you have material take-off data spread across multiple files, you can iteratively import each file - one at a time - and choose to "merge" the imported material quantities, just like with multi-worksheet Excel workbooks.
Pre-Existing Extra Basic Materials Assembly:: If you have already defined an "Extra Basic Materials" assembly and want to merge material take-off data with it, you can import the material take-off data from one or more files and choose to "merge" the imported material quantities.