OSArch Community

Dynamic .csv to cost schedule NOW AVAILABLE

S by steverugi on 21 Apr 2025, edited 24 Apr 2025 # +15 votes

Finally, when using a .csv to query a model to create a BoQ, we no longer need to

close the BoQ
reload the .csv file again
REPEAT (if you have updated the .csv)

With the most recent Bonsai release you can link a .csv table to a cost schedule:

To reload the edited .csv just click on the "sync" icon in red

Truly amazing feature for those who continuously access the original .csv table to edit query, property, and BoQ structure.

A HUGE thanks to @Massimo for its implementation

If you feel like, you can donate to the Open Collective here and/or

buy Massimo a coffee here

Cheers and happy modeling (and taking off quantities)

M by Massimo on 21 Apr 2025 # +6 votes

@steverugi thanks for the hints, waiting for feedback! Cheers :-)

S by steverugi on 23 Apr 2025, edited 23 Apr 2025 # +8 votes

a little update on how useful the dynamic .csv file can be:

the reason why I removed the first two columns is to be able to add new cost items to the cost schedule and have it fully dynamic between Bonsai and .csv

Since the refresh doesn't write in the .csv it's possible to leave Excel open

This is an alternative use of Export/Import cost schedule that maintains Property and Query data for quantity takeoff

Still playing with it but it's quite promising already

Thanks Bonsai team!

D by Dimitris on 23 Apr 2025, edited 25 Apr 2025 # +2 votes

HI @steverugi ,

These are great features congrats to @Massimo and everyone else that was involved in this.

I have a couple of questions/ suggestions that I would like to share with you, with respect to the 5D module of Bonsai.

As I observed the linked csv file is limited per session, as in if the working file is saved and reopened the linked file data is not maintained (obviously ). Could there be a n automated generation of a reference within IfcDocumentInformation in order to preserve at least the name of the file (maybe the path also through a relative position?)

Here is an example:

Say we have imported a csv schedule:

Cost Schedule

From Project Overview - Project Setup - Documents
when a CSV file is linked there could be a routine to generate also the Document Reference and populate the corresponding necessary fields from the cost schedule (I do not know whether this operation necessitates a saved IFC file with the corresponding cost schedule or if it could be generated from memory:

ID and Name from the IFC file in IfcSchedule

and map those information to the document reference info:

Mapping properties

Now when opening an IFC file with cost schedule, the 5D module could check on the GUID of the cost Schedule and compare it to the GUID from the Document reference thus matching the search location.

At this moment the export of the IfcSchedule doesnt include any querry that was used for mapping quantities or the elements that support the quantity displayed in the cost schedule (an IfcGroup maybe could be generated from the elements (if any) included/ mapped in the quantity)

Cost Scedule from the exported CSV

I think that this workflow opens the path to roundtripping and sharing information to multiple users of the file.

Takes on this any one?

S by steverugi on 23 Apr 2025, edited 23 Apr 2025 #

@Dimitris I think the IFC link works the same way and path+filenames are saved in a similar fashion, so in case of federated models the linked path and filename are already there I think.

good point, will get back to you on the rest of your post later

thanks

M by Massimo on 23 Apr 2025 # +1 votes

@Dimitris good points, thanks for sharing. About storing the location of the file, i think it would be a good feature. I was thinking about store the filepath in a PSet but i think the solution you have proposed (store the location as a document) would be better.

In the meantime, you could save the .blend file in order to store the location of the file because at the moment it is stored as a blender prop, but it is a momentary workaround.

About the export, i'll think about it, it seems a bit too much for my capabilities, but i'll think about it. Cheers

D by Dimitris on 24 Apr 2025 #

@steverugi said:

@Dimitris I think the IFC link works the same way and path+filenames are saved in a similar fashion, so in case of federated models the linked path and filename are already there I think.

good point, will get back to you on the rest of your post later

thanks

Hey @steverugi thank you, I'm not quite sure how linking an IFC file could work. Maybe I'm missing something. Have you tried it in files and worked?

Thanks,

S by steverugi on 24 Apr 2025, edited 24 Apr 2025 #

Hi @Dimitris

as you suggested: it stores the path of linked models as IfcDocumentReference

IFC file wtih the links:

Bonsai:

I am refering to the storing method of linked .ifc files, maybe it can be adopted for .csv too

S by steverugi on 24 Apr 2025 # +2 votes

@Dimitris

Now when opening an IFC file with cost schedule, the 5D module could check on the GUID of the cost Schedule and compare it to the GUID from the Document reference thus matching the search location.

At this moment the export of the IfcSchedule doesnt include any querry that was used for mapping quantities or the elements that support the quantity displayed in the cost schedule (an IfcGroup maybe could be generated from the elements (if any) included/ mapped in the quantity)

I think that this workflow opens the path to roundtripping and sharing information to multiple users of the file.

Takes on this any one?

currently we have two types of .ifc format that can work as cost schedule "importer" for Bonsai

the one with query to generate a cost schedule using "Property" and "Query" like this one:

to have this:

as you rightly pointed out once the .ifc is closed it does not save the link anywhere, maybe saving the file path as documents can solve the issue

the exported .csv from Bonsai that generates a similar file with addition of "Id" and "Hierarchy" to allow the same to be imported back to Bonsai

this second option can only edit existing cost items, and if "Property" and "Query" columns are added they can recalculated quantities accordingly, to add more cost items you need to remove the first two columns "Id" and "Hierarchy"

In summary, I think, we need to

find the way to store both .csv file to keep the link after closing (Link method?)
find the way to store queries, a candidate could be the "formula" field of IfcQuantity*, which is a label so it wouln't hurt ifc's feelings I guess

but needs to be tested

thank you for your inputs, much appreciated

D by Dimitris on 24 Apr 2025 #

@steverugi said:

In summary, I think, we need to

find the way to store both .csv file to keep the link after closing (Link method?)

find the way to store queries, a candidate could be the "formula" field of IfcQuantity*, which is a label so it wouln't hurt ifc's feelings I guess

but needs to be tested

Hey Steve thanks,

Yes, I think that the IfcDocumentReference is a way to go as it also aligns with potential "Level Of Information Need" as per ISO 19650, as means of storing information with respect to "Costing" Use Case.

As for the "Equation" that is also a really big feature if implemented (I do not think that it is at the moment), but I would like to ask the following if any one is aware of:

In the "Cost Template" file under the column "Property" can we use "Regex". if not then that would also be a very usefull feature.

Thank you,

S by steverugi on 24 Apr 2025, edited 24 Apr 2025 #

@Dimitris

In the "Cost Template" file under the column "Property" can we use "Regex". if not then that would also be a very usefull feature.

since Property column contains the value of the Qto pset to be used on the selected elements defined by the Query, what could be the advantage of using Regex in Property? The way I see it it's necessary for Property to be expressively unique.

Say you have a query selecting all walls of a particular type on the ground floor (like: IfcWall, type="A-P-WAL-200", location="G1"), you need a Property to extract the desired quantity, these are the options for Qto_WallBaseQuantities:

How would a Regex be helpful? genuine question

cheers

D by Dimitris on 24 Apr 2025 #

Sure thing,

If I have an article of formworks for all vertical elements (IfcWall.SOLIDWALL, IfcColumn.COLUMN) but the Properties for these quantities are not a common name for both

for Column we have :

and for Wall we have :

So it would be great when parsing the csv file under property and querry to use the querry and loop through the "properties" mentioned matching (regex) the corresponding according to the element that is looped, thus not having to duplicate the article once for Column and once for Wall. Of course this can be extended in other similar cases.

Cheers

S by steverugi on 24 Apr 2025 #

@Dimitris

good point

it is already possible to use multiple classes in the Query column (like 'IfcWall,IfcSlab,location = G1') but Property does not allow Regex atm

would you post a feature request? thanks

M by Massimo on 24 Apr 2025 #

@Dimitris doesn't selector already support regex? Look at here https://docs.ifcopenshell.org/ifcopenshell-python/selector_syntax.html

For your example it would be something like /Net*Area/

D by Dimitris on 24 Apr 2025 #

I dont think that selector is integrated in the property name in ifc5d.csv2ifc.create_cost_items

I have tried it but doesnt function.

It need to be implemented

S by steverugi on 24 Apr 2025 #

@Massimo said:

@Dimitris doesn't selector already support regex? Look at here https://docs.ifcopenshell.org/ifcopenshell-python/selector_syntax.html

For your example it would be something like /Net*Area/

I confirm, the Selector works beautifully in Spreadsheet or Query, not in Property

M by Massimo on 24 Apr 2025 # +1 votes

Ah ok good to know