How the import reads a file
Column titles by key words, bills, page totals, collections, line types and the checks on every row.
BoQs are written in many ways. The import does not need an exact template: it reads the titles by the words inside them, then reads each row by a fixed set of rules, and tells you every place where it had to guess.
How the columns are found
A row is the title row when it names the description and at least two more columns. Each title is cleaned (capitals, brackets and line breaks do not matter) and read by its key words. A long sentence is never taken as a title, so a description in the data cannot be mistaken for one.
| Column | Titles that name it (examples) |
|---|---|
| Description | Description, DESCRIPTION OF WORKS, Item description, Particulars, Details, Works |
| Quantity | Qty, Qnty, Qtty, Quantity, Quantities, Quant |
| Rate | Rate, Rates, Unit Rate (Tshs), Rate (TZS), Price, Unit price |
| Amount | Amount, Amount (TZS), Total, TZS, Tshs, Shs, KES, UShs |
| Unit | Unit, Units, UOM, U/M, Unit of measure |
| Item / Ref | Item, Items, Item No, Item Ref, Item code, Ref, Ref No, Reference, Code, Bill item |
The order of the rules matters: a title is tried as a description first, then a quantity, then a rate before an amount (so Unit Rate (Tshs) is the rate, not the amount) and before a unit. A running count (S/No., Sr No, Serial, No.) is used as the reference only when the sheet has no Item or Ref column.
How bills are found
- A row that starts with BILL No. 2 - SUBSTRUCTURE or ELEMENT NR. 2 - FRAME (BILL, ELEMENT, No., Nr., Number or # in any capitals) starts a new bill with that number and title. If the row has no title, the next text row is taken as the title.
- Such a row with a figure after it is a bill listed with its total in a collection or summary: it is skipped.
- A sheet with no bill row takes its bill from the sheet name (Bill 2, Element 2 - Frame).
- Failing that, numbered sections (SECTION 2 - CONCRETE, PART 3) are taken as the bills, and the message says so. Lettered sections (SECTION A) stay sections inside a bill.
- Failing all of these, everything goes into Bill 1 named after the sheet, each line is marked, and when more than 25 lines land in one bill a yellow box warns that everything was read into one bill.
Rows that are left out
| Row | What happens |
|---|---|
| Empty rows | Skipped. |
| The column titles printed again on a later page | Skipped. |
| Page totals: Carried to Collection, Carried forward, Brought forward, C/F, B/F, To collection, Page 2, Page total, Total carried, Total of this page | Skipped, and the total written there is checked against the items of its page. |
| A collection block: COLLECTION, Collections:, Bill Collection, Element summary, BILL No. 2 - COLLECTION | Skipped up to the next bill. |
| The summary of the whole BoQ: SUMMARY, GRAND SUMMARY, General Summary | Reading of that sheet stops: it only lists the bills again. |
A heading spaced out letter by letter (C O L L E C T I O N) is read as the word. Every row left out is listed in the message, with its row number, so you can see nothing priced was dropped.
How each remaining row is read
- A row with a quantity or an amount is a priced line.
- A text row continues the item above when that item has a reference but no quantity yet: long descriptions over several rows come in as one item.
- A text row with a reference starts an item whose quantity comes on a later row.
- Any other text row is a section when it is bold or in capitals (under 90 characters), else a heading / note.
- Text merged across the reference and description columns is read as the description.
How the type is chosen
| When the description (or its section) says | Type |
|---|---|
| Unit %, or Add ... % / Add ... profit with no unit | % addition (the % is taken from the quantity column) |
| Attendance | Measured item |
| Contingency, or a Sum line in a contingencies section | Contingency |
| Prime cost, P.C. sum, or a Sum line in a PC section | Prime Cost Sum |
| Provisional sum, or a Sum line in a provisional section | Provisional Sum |
| Daywork in the line or its section | Dayworks |
| Any line of a bill titled Preliminaries or General items | Preliminaries |
| Anything else | Measured item |
Figures and the checks on them
- Numbers written as text are read: 1,800,000, TZS 1,800,000.00, 1 800 000/=. (500) is minus 500; -, -- and nil are empty.
- An amount with no quantity is a lump sum: quantity 1, the amount as rate and, when no unit is given, unit Sum for the three QS sums or Item otherwise.
- A quantity and an amount with no rate: the rate is worked out as amount / quantity and the line says Rate worked out from the amount.
- Quantity x rate that differs from the amount written by more than 1 shilling is marked: Qty x Rate = ... but the sheet says ....
- A unit word in the quantity column (Item, LS) is read as quantity 1 in that unit. Quantity and unit swapped (Sets | 4) are read the right way round, with a warning.
- Marked as well: a quantity, rate or amount that is not a number, a unit that is a number, no unit, a negative quantity, an item with no quantity or amount, no description, a % with no sum above it in the bill, and a reference used twice in a bill. Many BoQs start the letters again on each page: a page whose first item is A (or 1) starts afresh and is not a duplicate.
Good to know
A page total that does not match is reported, never corrected: it is the sheet's own arithmetic. The lines themselves are imported as quantity x rate.
Something unclear? Ask us on WhatsApp: we answer the same day.