3.3.6 How to import an external bill

A bill can be imported by Quantities with the following file types:

Quants
The native BoQ file format for the Quantities add-on. This format is the most comprehensive and reliable, and is therefore strongly recommended in most circumstances. The content is XML, and a schema is available on request to anyone wishing to read/write to this format.

CSV
Standard CSV, well-suited for interoperability with spreadsheets and some databases, but lacks the defined structure of XML. This is straight-forward for a simple list of items, but is becomes harder to manage for larger, hierarchical bill structures. An additional dialog is displayed for CSV import to define the purpose of each column.

Click the Import button (item 2e in the illustration). A standard file navigation dialog will be displayed to select a BoQ file to import.

When a CSV file is selected, the following dialog is displayed to define the data standards and columns:

The dialog is divided into 3 panels:

1. Determines how keys will be interpreted in hierarchical bill structures. If Key contains parent category is checked, the keys of any parent categories will be stripped off the child items (because Quantities dynamically prefixes category keys for display purposes). A field to specify the key separator will also be displayed.
   
   As an example, if an item with the key “10.7.241” is imported and has parent categories of 10 and 7, the key of the imported item will be simply “241”.
2. Standard CSV options.
   
   The separator menu includes comma, tab, colon, semi-colon, space.
   
   Text is quoted should be checked if text data is always enclosed in quotes.
   
   First row [is] headings should be checked if the first row is the column names.
3. A table defining defining how the CSV columns should be linked to the key fields of a bill item.
   
   The table is headed up with a Configuration menu that allows the table content to be saved and reused. Each configuration is named and can be selected from the menu. Additional menu items allow configurations to be created, renamed or deleted (but there must always be at least one configuration remaining).
   
   The table is headed up with three columns:
   
   a) Column Name
   
   If The first row contains column headings option is checked, this column displays the names imported from the first row. Otherwise it displays the column number, e.g. Column 1, Column 2, etc.
   
   b) Sample data
   
   An example of the data to be imported – this is helpful if the columns are not named and also serves to confirm that the CSV options are correctly specified and the imported data is valid.
   
   c) Data Application
   
   Which bill item data field this column is linked to:
   
   Unwanted columns should be set to Unused. Unique ID is optional, but very useful. Ideally every bill item (and category) should have some kind of unique ID (a guid) that ensures bill items are reliably tracked no matter what changes are made to its name, key or parent category.
   
   Category Key and Category ID are also optional and specify the parent category of an item or category. When used, the values can be empty for top-level items. Flat bill structures don’t need these fields.

All imported bill items will be merged with any existing bill structure rather than wholly replacing it, facilitating round-trip data exchanges with external data sources without disrupting established recipes and calculations. The changes resulting from the import are displayed first and can be cancelled if necessary (without changing existing data):

The dialog contains 2 adjacent panels, new data to the left and original to the right. If the “Display changes only” checkbox is clicked, the tree will show only changes to the data.

The type of change is illustrated with the following colours:

• Black: An existing item that is unchanged by the import;

• Green: A new item to be added;

• Orange: A change to an existing item (details of the new and existing data can be compared by selecting the changed item);

• Red: An existing item to be deleted (it can be restored later if required);

• Blue: An existing item that has been deleted, but will be restored by the import.

The update will only be applied to the project if the user clicks OK.