We value your input!
Please participate in Archicad 28 Home Screen and Tooltips/Quick Tutorials survey

Collaboration with other software
About model and data exchange with 3rd party solutions: Revit, Solibri, dRofus, Bluebeam, structural analysis solutions, and IFC, BCF and DXF/DWG-based exchange, etc.

Schedule quantities to an SQL server database

Ahmed_K
Advisor

Hello,

Is there a way to connect archicad schedules to an sql database? 

A direct link Without the need of excel files.

 

Operating system used: Windows 11

AMD Ryzen 9 3900X, 32 GB RAM, RTX 3080 10 GB
Archicad 27
Windows 11 professional
https://www.behance.net/Nuance-Architects
8 REPLIES 8
poco2013
Mentor

FYI:

The short answer is NO. The Archicad API's have no direct access to schedules. i am told this is by design -- don't know why?

 

Indirectly, the easiest why is to use a Python script to access all the properties that you would schedule, assuming those properties are accessible to the Python API. Python has native support to access SQL databases.  A little more difficult would be to use the C++ APi. in both cases, those scripts are manually initiated (click).

Gerry

Windows 11 - Visual Studio 2022; ArchiCAD 27
Ahmed_K
Advisor

Thanks Gerry for the reply , i didn't expect someone will reply on this, i'm lucky 😄

 

So, i understant that python would not acees the schedule fields, it will search for that propeties, values , from the geometries placed in the model, like schedule do, 

 

this won't be an easy task, beacause schedules use criterias to firlter the model, with python, i have to create filters " like schedules " , right ? 

AMD Ryzen 9 3900X, 32 GB RAM, RTX 3080 10 GB
Archicad 27
Windows 11 professional
https://www.behance.net/Nuance-Architects
poco2013
Mentor

Correct.

But, in Python, the criteria could be easy or quite involved. For example, a typical search would get the ID's of all walls, then the properties of those walls and if certain properties meet certain values, then retrieve the applicable properties. This is essentially what the schedules do  behind the scenes. The logic to do this is not difficult in Python, as it is a popular database language. usually less than 10 - 20 lines of code. and reasonably fast depending on the number of elements you need to iterate through. But would never be as fast as the C++ API. BTW-- if you had a extremely large and complicated database and relationships, consider also using the Pandas Python AddOn, as it was made for just that.

 

Finally, the C++ example,  Element_Test contains example functions to retrieve all the builtin and custom functions of any type element, then the easiest thing to do would be to dump all that data to a Python script and using Pandas, filter and correlate the data.  This is particularly useful if you are handling surfaces and components, not all of which are directly accessible by Python. It can be somewhat slow (several seconds and hundreds of properties), but Pandas does most of the work, The AddOn part would involve a simple C++/Python interface, which I have already done without much effort. Depends on how involved you want to get and how needy you are? === Just some of my thoughts.

Gerry

Windows 11 - Visual Studio 2022; ArchiCAD 27
Ahmed_K
Advisor

It's like reinventing the wheel, so, it would be easier, and a straight forward solution, to export schedules as xlsx files, and link that files to an sql database, 

From a user perspective, i would prefer to use schedules and visualise data in an interactive way, to check what I'm scheduling,  and export that.

AMD Ryzen 9 3900X, 32 GB RAM, RTX 3080 10 GB
Archicad 27
Windows 11 professional
https://www.behance.net/Nuance-Architects
poco2013
Mentor

"Whatever floats your boat" -- Note , you would have to continually go through the save the process vs a one time configuration?

 

FYI: Python scripts can also read xlsx files and then save to a SQL database automatically. Many videos on this on YouTube.

Gerry

Windows 11 - Visual Studio 2022; ArchiCAD 27
Ahmed_K
Advisor

The number of clicks doesn't bother me, i need a workflow that have some checkpoints in-between key steps, 

A python script that combine and send to sl is a good choice for me

AMD Ryzen 9 3900X, 32 GB RAM, RTX 3080 10 GB
Archicad 27
Windows 11 professional
https://www.behance.net/Nuance-Architects
Ahmed_K
Advisor

Update : 

Javascript and python code that reads excel files, and store them in SQL database , that was my chice for development, i'll share the results when something is ready ! 

 

Thanks @poco2013  !

AMD Ryzen 9 3900X, 32 GB RAM, RTX 3080 10 GB
Archicad 27
Windows 11 professional
https://www.behance.net/Nuance-Architects
scottjm
Advisor

Is my recollection correct that there is a python api function to publish a publisher set?  
you could use that to automate that part of the process if you place the schedules into a publisher set and set the export format to xlsx or csv. 

Scott J. Moore | Fulton Trotter Architects | BIM Manager, Associate, Architect
Since AC13 | Current versions AC23.7000 & AC26.5002 | BIMCloud Basic | Python, GDL, VBA, PHP, SQL, CSS
Certified Graphisoft BIM Manger (2022)
Win 10, i9-9900K, 32GB, Quadro P2200, 500GB NVMe

Setup info provided by author