BBIM - schedule of rates | import CSV

steverugi · December 2023

Import a Bill of Quantities as .csv file

I can happily import a .csv file with a bill of quantity data in BBIM, here is the csv:

and here the corresponding Schedule in BBIM (PRICEDBILLOFQUANTITIES as PredefinedType ), no issue.

Connecting to model elements and schedule of rates

If I need to update the quantity of an item to be connected to some element in the model it's not a problem, I know how, BUT , as a normal workflow the rate of the item in the BoQ needs to come from a Schedule of Rates (SoR) like below:

to which similar items in the BoQ are connected, without being hardcoded in the CSV/BoQ.

At the moment my only option is to create a SoR directly using the GUI in BBIM and enter the items one by one, like the above image. A bit tedious when dealing with many rates, not to mention that it's usually a document used across different models/projects, often times subject to percentage variations.

Problem

I can't figure out how to import a SoR as CSV.
I tried to set the imported BoQ as SCHEDULEOFRATES but the result is odd:

unit of measure is no longer available (it shows U instead of m3)
the rate is the one from the CSV, which is fine
the Subtotal column, that should only work in summaries, uses the Quantity x Rate not giving the correct amount at the item level

Exporting a manually created SoR as .csv and importing it back resulted in an error, probably the schema needs to be somewhat different.

Question

Is it possible to import a specific Schedule of Rates as a .csv file? My python skills are not good enough to decipher the csv2ifc.py available at https://github.com/IfcOpenShell/IfcOpenShell/blob/v0.7.0/src/ifc5d/ifc5d/csv2ifc.py.
But it looks like it's mentioned in there...
What did I miss? Do I have to use a different CSV structure or?..

As always your help is greatly appreciated, thanks in advance

Massimo · December 2023

@steverugi can you share the resulting error when you import the csv SoR previously exported?

steverugi · December 2023

@Massimo said:
@steverugi can you share the resulting error when you import the csv SoR previously exported?

thanks for your reply, here the error message:

I also attach the IFC file, just in case, this is the snapshot of the exported SoR as .csv (can't attach it here for some reason)

I tried also deleting the Id column but still the same error
many thanks for your kind assistance

Massimo · December 2023

@steverugi well i just tried and it works if you use the same columns you used for import the Bill of Quantities (so Hierarchy, Identification, Name, Quantity, Unit, Rate, Subtotal, Property, Query)...after that you can change the predefined type into SCHEDULEOFRATES and you have a schedule of rates ...
The problem is because the import script uses a predefined column order and name ...

steverugi · December 2023

@Massimo said:
@steverugi well i just tried and it works if you use the same columns you used for import the Bill of Quantities (so Hierarchy, Identification, Name, Quantity, Unit, Rate, Subtotal, Property, Query)...after that you can change the predefined type into SCHEDULEOFRATES and you have a schedule of rates ...
The problem is because the import script uses a predefined column order and name ...

thanks, I rearranged it and it can be imported in the same way I was able to import the BoQ

of the three points in my previous post Problem the last one is sorted out by using 0 for quantities in the .csv (how did I not think about this before?) and by set all summary items and rate items' hierarchy to 1 since BBIM automatically applies the SUM criteria if set as 1,2,3 .... Summary values are not needed in a SoR, unless it contains buildup details.

I am now left with the third point:

unit of measure is no longer available (it shows U instead of m3)

here the rearranged .csv, also attached as txt file

most grateful for your assistance, cheers

steverugi · December 2023

IFC structure for Cost Item Unit (BoQ vs SoR)

I am not sure this is important to understand why the imported .csv cost schedule can convert all needed data except Unit as I wrote in my previous posts, checking the IFC text file I noticed that changing the predefined type from PRICEDBILLOFQUANTITIES to SCHEDULEOFRATES affects the way the Unit is stored, I sketched it in the image below (Hopefully this issue can be sorted out in the future)

PS I need to post a request, if feasible: the possibility to import the Cost Schedule directly from the GUI, like the one available for the Resource Schedule, maybe with a dropdown list to select the imported format in advance, but need to learn how to do it ;)

Massimo · December 2023

ping @SigmaDimensions

steverugi · December 2023

EDIT
I just realized that an IfcCostSchedule [PRICELIST] can be saved as a dedicated IFC file and imported as a Library into a different model, like with types.

This allows sharing rates among projects without importing it as .csv file. Nice feature !

Massimo · December 2023

@steverugi this is awesome! Have you tried with a PRICELIST if the quantity problem units still persist?

steverugi · December 2023

@Massimo said:
@steverugi this is awesome! Have you tried with a PRICELIST if the quantity problem units still persist?

the problem is when a pricelist is prepared in Excel or CSV file, on importing it does not keep the unit of measurement, something that works fine when the document is set as BoQ.

Instead, when I created a Schedule of Rates in BBIM and saved it as IFC, and imported in a different project as Library, it reads everything fine, no issues.
What I can see is that, on professional level, Schedule of Rates can be tens or hundreds items long, usually recalculated to adjust some based on variations. That's why the exchange in .csv/.xlsx is more convenient. Cheers

Massimo · December 2023

@steverugi big news: i think i understand now how it works!
First of all, in the folder src/ifc5d there are some .csv files examples that can be useful to understand how to structure the .csv file...
Also, if you want to import a Cost Schedule with predefined type SCHEDULEOFRATES, the .csv file must have these columns [Hierarchy, Identification, Name, Quantity, Unit, Value].
Don't insert 0 in the quantity column (because it raises an error), insert 1 instead, so the total will be the same as the value column.
Also, the unit has to be inserted in this way: m, m2, m3, etc...
Most important: toggle the option "is schedule of rates" in the import window. So click on File -> Import -> Cost Schedule .csv and in that window there is the toggle button on the right

It should work in this way, at least for me it worked...the imported cost schedule has the correct unit of measure :-)

steverugi · December 2023

@Massimo said:
@steverugi big news: i think i understand now how it works!
First of all, in the folder src/ifc5d there are some .csv files examples that can be useful to understand how to structure the .csv file...

OK will look into it

Most important: toggle the option "is schedule of rates" in the import window. So click on File -> Import -> Cost Schedule .csv and in that window there is the toggle button on the right

It was there all the countless times I imported a .csv and I had never seen it, wow!

YES it works fine, thank you so much @Massimo , big big help, grazie

Massimo · December 2023

@steverugi yes, indeed it is a bit hidden, i haven't seen that option too..untill i looked into the code and it showed up :-)

Nigel · December 2023

@steverugi & @Massimo I have been following your discussion as I suspect many others have. It would be great for the community (and me) if you could put together a workflow for the above, I for one got a little lost :)

steverugi · December 2023

@Nigel said:
@steverugi & @Massimo I have been following your discussion as I suspect many others have. It would be great for the community (and me) if you could put together a workflow for the above, I for one got a little lost :)

No problem, it's the least I can do after the support I received from this platform

steverugi · December 2023

Hi @Nigel