Import from Excel
Read a firm's own Excel BoQ, check every line on one screen, then add it.
Most BoQs arrive as Excel workbooks laid out the way their author likes. The import reads such a file into the BoQ in three steps: Upload, Columns, Check. Nothing is added to the BoQ until you press Import on the check screen.
Step by step
- 1Open the BoQ's Details and press Import from Excel. The button is there while the BoQ is a Draft or Tender, for QS staff.
- 2Choose the file: .xlsx, .xlsm or the old .xls, up to 15 MB. Leave Saved layout empty to let HodariQ find the columns, or pick a layout saved from an earlier file of the same source.
- 3Press Read File. HodariQ looks for the row of column titles in the first 40 rows of the first bill sheet. When it finds at least the description, the quantity and a rate or amount column, it goes straight to the check screen.
- 4Read the counts at the top: bills, lines, lines with warnings, and the total read. Under them a message says in plain words which bills were found, which rows were left out as totals, where reading stopped, and which page totals of the sheet do not match their items.
- 5Look at each yellow line. Correct it in place (type, reference, description, unit, quantity, rate or %), tick Leave out for a row that is not a BoQ line, or press the tick at the end of the line to accept its warning once you have checked it.
- 6Press Import and confirm. The lines are added and the BoQ opens.
When a column was read wrongly
Press Back to Columns. Under Columns (Excel letters) type the letter of each column: Item / Ref, Description, Unit, Quantity, Rate, Amount. Under Where to read choose the Sheet and the First data row, or tick Read every sheet. Find Columns Again (when one sheet is read) reads the titles of the chosen sheet again; Preview goes back to the check screen with your columns. Only the Description column is required; with no Quantity and no Rate or Amount column you will see the columns step first instead of the check screen.
Workbooks with several sheets
- With more than one bill sheet, Read every sheet is ticked for you, and the column titles are looked for on each sheet separately, so sheets laid out a little differently still read.
- Hidden sheets (old versions, other buildings kept in the workbook) are left out, and the message names them. To read one, untick Read every sheet and choose it.
- Sheets named Summary, Collection, Cover or Contents are left out: they only repeat the bills.
Save the layout
On the columns step type a name in Save this layout as and press Save layout. The layout keeps the column letters, the first data row, the sheet name (when one sheet is read) and the rule for bill rows. Next time, pick it under Saved layout at the upload step: its columns are used as they are, without looking at the titles.
What Import does
- Each bill read is matched to the bills of the BoQ by its number. A bill with the same number gets the lines added at its end; a new number makes a new bill with the title read from the file.
- References read from the file are kept. A priced line without one gets the next free letter of its bill.
- A % addition is linked to the last Prime Cost Sum, Provisional Sum or Dayworks line above it in the same bill.
- Units not in the unit list are added.
- The BoQ's history notes how many lines were imported from which file.
- Lines with negative values cannot be imported: HodariQ names their rows. Fix them or leave them out.
Good to know
Lines in yellow are imported as they are unless you correct them or leave them out. Accepting a warning only clears it; it does not change the line.
Good to know
Cells with formulas are read as the value Excel last saved in the file. A workbook made by a program that never opened it in Excel may hold no saved values: open it in Excel, save it, and import again. A damaged or password protected file is refused with a message.
Tip
Importing a file HodariQ exported (see Export to Excel) gives back the same lines with their exact types: the export carries a hidden Line type column the import reads.
Something unclear? Ask us on WhatsApp: we answer the same day.