In this tutorial, we'll explore the different ways in which you can transfer geometry and BIM data from Dynamo to Excel (and back!) using Speckle.
Dynamo has already a couple of nodes that let you export and import Excel files, so why bother using Speckle?
Well, there are quite a few advantages:
- data is stored in Speckle, not in files. Access it whenever you want it, wherever you want it
- no need to worry about losing data or overriding files, each time you send a permanent commit is saved automatically
- you can filter data before it's actually received in Excel
- personally, I find the built-in nodes a bit finicky!
To follow this tutorial you'll need the following:
- Dynamo (I'm using 2.6 inside Revit 2021)
- Excel (I'm using Excel Web)
- a Speckle account (you can get one at https://speckle.xyz/)
- the Speckle Connector for Dynamo (install it via Manager)
- the Speckle Connector for Excel (install via Office store, instructions)
- a BIM model (I'm using the sample architectural one that comes with Revit)
Method 1: Pre-filtering data in Dynamo
This first method is ideal if you already know what data you'll need in Excel or if you have old definitions using the built-in nodes that you'd like to update by using Speckle.
Simply create the logic to extract some data and structure it in a list of lists, where each sub-list corresponds to a row in Excel.
Here's a sample that takes room information from Revit. Both images show the same logic, but the second is more concise:
Now, it's just a matter of sending these nested lists to Speckle. Either create a new stream or pick an existing one's URL and plug it and our data in a sender:
After clicking the send button, you'll be able to view the data online instantly:
Right click the send node to view the stream online
In our online viewer, you can see the data just sent. In the future we will introduce a tabular interface to see this in a more natural way.
Here's a link to the commit if you want to check it out.
In Excel, we just need to add the stream to our document and receive this commit, since the data structure is quite simple, we won't be asked to filter it.
The data will be inserted where our cursor is.
Let's now update the Dynamo script and send again, for instance by adding a new parameter, like the room perimeter:
In Excel we now click "receive last selection", we'll get the updated data set in the previosly selected cell:
Method 2: Filtering data in Excel
This second method is great if you don't know yet what data you'll need in Excel, we'll be sending Revit elements with all their properties and then filter the data we need in Excel.
Update the Dynamo script to just send the rooms:
Now in Excel, if we follow the same steps as before, we'll be prompted to filter the data before it's written in the spreadsheet. This is because it's a much larger data set than before.
See the gif below for how to do that, and notice that under the parameters item we have available all the parameters belonging to a Revit room, indexed by their internal Revit name.
For a list of internal parameters names, see this page.
The result achieved is the same as with the previous method, the only difference is that now we also get headers with the property names.
We hope you enjoyed this tutorial and found it useful!
Speckle is an Open Source project and we really ❤️ feedback, so if you have any questions, comments, critiques, or praises please let us know on our community forum.