FREEWARE DOWNLOADS

Excel to CITE

CITE
CITE file Import
Configuration
Input requirements
CITE Tweaker
CITE to Excel
Excel to CITE
Calculix to CITE
CITE to ConQuest

 

Excel to CITE

Unlike CITE (standard) Bills of Quantities, Excel BQ's can come in any number of shapes and sizes so to make an Excel to CITE converter work correctly, the Excel input file must follow a few conventions. From version 1.06 onwards, BQFilter contains an enhancement allowing the user to adjust the settings to suit their particular Excel input file.

This is a typical input file (as per the image above) to download which follows the BQ Filter conventions. You can run it yourself or download the CITE output file here.

This image shows what the output file looks like.

 

Input file conventions

From BQ Filter v1.06 onwards, the input file conventions are as follows :-

All individual headings and descriptions must be separated by a blank line
Ensure that the different heading levels in the BQ have a different format type ie. Italic/Bold/Underlined, Italic/Bold, Italic/Underlined, Italic, Bold/Underlined, Bold, Underlined or Unformatted. Whichever formatting your Excel sheet uses must be entered in the BQFilter configuration settings (see later)
All descriptions with quantities must have a corresponding item reference.
Any "non-standard" side notes, collections or summaries in the Excel input file are likely to interfere with the program and should be kept to a minimum or preferably removed prior to running the conversion. There is provision in the BQFilter configuration settings to "pattern match" these lines so that they are ignored and don't create unexpected results.

That's it. The created CITE file, a plain text file, can be viewed using Notepad or WordPad.

It may need a little tidying up regarding summaries but should import without problems into CITE compliant estimating packages.

The image above shows the simple BQFilter user interface in which you select which conversion you wish to run and are then prompted with an options screen (see image below). You are asked a few questions about the input file you are using and the format of the output file you are creating. After the options are set, you are prompted for an input filename. On completion of the process a message box will inform you of the name of the output filename. This will be the same as the input file with the extension .ebq instead of .xls

Note: If the output filename already exists then it will be overwritten each time the program is run.

Use Original CITE references

This checkbox option is for those users who want to convert CITE to Excel BQ's for pricing and then back to CITE to submit their electronic tender.

To use this option, you need to first of all create your excel file using the option to show the original CITE BQ references. Then, when converting back to CITE, set the column settings to use this particular column for item references.

PrivicaRIB code to determine BQ description levels

This checkbox option is for a user customisation to calculate the Item Identifier from the length of description codes rather than from the Excel worksheet description format. Leave it unchecked to use BQ Filter in the normal manner.

Page reference from CATO generated Excel worksheet

If this checkbox is selected, the program expects the format of the input Excel workbook to be that created by the CATO bill production software package. Leave it unchecked to use BQ Filter in the normal manner.

Ignore automatic pagebreaks in Excel worksheet

If this checkbox is selected, the program will not increment the CITE page counter when it encounters an automatic pagebreak in the input Excel workbook. This is useful if your input workbook contains manual pagebreaks at the end of each page. 

Ignore all Excel pagebreaks and generate page references from Excel Item references

If this checkbox is selected, the program will base the CITE page numbering on the Excel item reference ie. incrementing the page prior to the Excel item reference reverting to "A" or "1". All pagebreaks within Excel are ignored with this option. 

BQ Directory

This setting, which is browseable, describes the location of your input files. The output files will be placed in the same directory on your hard drive.

Currency Description

This goes into the CITE output file header information. The setting is GBP if you are pricing in British Pounds.

Method of Measurement

This goes into the CITE output file header information. The setting is SMM7 if the quantities have been measured in accordance with current RICS/BEC measurement rules.

Layout of Excel input file

These six settings let you describe which columns (A->K) are used for the

BQ Reference

Description

Quantity

Unit

Price Rate

Extended Price

If it's an unpriced Bill then just select two unused columns for the Price Rate and Extended Price.

Format of Excel input file

These settings let you to describe the formatting used in the Excel input document. The options available for each level are :

Italic/Bold/Underlined

Italic/Bold

Italic/Underlined

Italic

Bold/Underlined

Bold

Underlined

Unformatted

BQFilter will use this information to determine which CITE identifier to use for each line in the output file so make sure your Excel input file uses a different format type for different BQ levels. You can see in the above example that main headings are Bold/Underlined, sub headings are bold only, specification headings are underlined only and item headings are unformatted (as are the quantity items).

Select "n/a" from the drop down box to identify unused levels. The above example contains only four levels so (any) four of the eight available levels will be unused.

Excel Headers/Footers to ignore

Your Excel workbook might contain headings on the first few rows of each worksheet or possibly within a worksheet just before a pagebreak (footer) or just after a pagebreak (header). The spreadsheet headings are not required in the CITE output and are likely to cause problems unless bypassed. You can see in the above example that the topmost row contains worksheet headings but there are no continuation headings at pagebreaks. The settings would therefore be "1" for worksheet headers and "0" for pagebreak headers and footers.

Additionally (or alternatively if you prefer), unwanted lines like collections and summaries can be bypassed using pattern matching. To enable this feature select "YES" to "Use advanced filtering? ".

 If you select "NO", all the remaining options will be "greyed out" and are not used.

If, for example, your Excel BQ has some lines which say "To Collection" in column C which is normally the quantity column, BQ Filter would consider this as a quantity without item reference and throw up an error unless the line is ignored.

To ignore the line select "C" from the drop down column list and type in "To Collection" (without the quotes) in the adjacent text box. This will tell BQ Filter to ignore *ALL* lines where column C contains (anywhere within the text) the phrase "To Collection". Be careful when using this feature since you might inadvertently match out lines you don't mean to if your phrase is not precise enough.

If you select "NO" to match case, then the phrase "To Collection" would also match "TO COLLECTION" or "to collection" or "tO cOLLectION".

You can use up to four patterns. If you don't require all four, you can disable the unused ones by selecting "n/a" from the drop down column list (which greys out the adjacent text box) or by leaving the text box blank.  

Worksheet Titles?

You can choose (YES or NO) whether the titles on your Excel worksheet tabs are used as top level headings in the CITE output.  You can see in the above example that the worksheet tabs are "ROOF" "STAIRS" and "EXTERNAL WALLS" which are element headings so in this case we want to say "YES" so that they are used in the output. If your tab says the default "Sheet1" you will want to say "NO" here.

Increment CITE Bill reference between worksheets?

If you are using an Excel workbook with multiple worksheets, you can choose (YES or NO) whether to use a new bill reference when you change worksheets.

Create separate CITE files for different worksheets?

If you select YES, a new CITE file will be created for each worksheet contained in the Excel workbook you are using. If you select NO, all worksheets will be appended into a single CITE file.

Multiple Lines or Single Cell?

This setting describes whether long descriptions are contained wordwrapped within a single cell (as per the above example) or whether they extend onto the cells below (multiple lines).  If multiple lines are used, you need to state whether the quantities appear on the first or the last line of the (long) descriptions. This last option will be "greyed out" if the descriptions are in single cells since (in this case) the quantity and descriptions will always be on the same line.

Quantity on first or last line?

This setting refers to the CITE output and is not to be confused with the previous setting which refers to the Excel input file. This can be either FIRST or LAST to print the quantity on either the first line of text or the last line in the CITE output file. This will only apply to long descriptions. Note that in strict compliance with the CITE4.2 standard this should be set to LAST but the option is available to retain compatibility with estimating packages which don't strictly comply with the CITE 4.2 standard.

ID9?

This setting can be either YES or NO to either print "9" as an item identifier against the priceable items or to suppress the "9". Note that in strict compliance with the CITE4.2 standard this identifier should be printed but the option to suppress it is to retain compatibility with estimating packages which don't strictly comply with the CITE 4.2 standard.

 

Back to Top

 

 
 

 

Home Services

Send mail to with questions or comments about this web site.
Copyright E-Quantities Ltd.