One of the most common features that is attracting architects, engineers, and construction professionals to learn Dynamo is its ability to import various types of non-BIM information formats into the BIM authoring environment. In this video, we will point Dynamo to an Excel file, read a table consisting of numerical and text data, and gain a basic understanding of how tabular data is structured as nested lists in Dynamo.
- [Voiceover] One of Dynamo's most attractive features to Revit users is its ability import information from outside the BIM environment into a Revit project. One of these formats, and possibly one of the most common ways that architects, engineers, and contractors share information outside of drawings, is by sharing Excel files, which is information that's organized in a tabular form, or in rows and columns. In this video, we'll import the data from an Excel spreadsheet into Dynamo. Now before we do anything in Dynamo, let's take a look at the spreadsheet that we'll be importing.
When we open it up, we can see that it has information about several different types of retail shelving. One column is a Number, or an identifier for each shelf type. Then there's a Description of each shelf type's intended use, either Shirts, Pants, Footwear, or Accessories. Then we have the Width and the Height dimensions and the # of Shelves that each type should have. So why are we interested in bringing this kind of information into Dynamo? Maybe you received this Excel file from your FF&E consultant or retail consultant. And ultimately we're gonna take the data in this Excel file, we'll place a Revit family for each of the types that are described here and we can automatically change the values of Revit family parameters to match exactly what's here in Excel.
This is an excellent workflow to have in your backpocket, especially if information is likely to change over the duration of the project. Instead of comparing old spreadsheets to new ones, we can simply run the Dynamo graph again to update the Revit families with the most up-to-date information. Okay, let's hop back over to Dynamo. I'm gonna create a new homework space by clicking new on our homepage. Let's start this graph off by simply doing a search for any nodes that are related to Excel. This can be a great way to discover nodes that perform functions that you're looking to perform.
It seems that there are two nodes in our library that interact with Excel, ReadFromFile and WriteToFile. In this particular case, we're interested in reading. We'll write to an Excel file in another video. Let's drop that node into our workspace. It's seems this node has three inputs. The first input is specifying where in our computer or where in our server the Excel spreadsheet lives. We also need to identify the name of the worksheet, or the tab in Excel that contains the data that we want to import. The third option is whether we want to import all the data as string data or as text information.
Our Excel file has some text information, but it also has quite a bit of numerical information too. If we were to import that data as strings, we might run into problems down the line if we wanted to use it for mathematical functions, or other types of formulas, because Dynamo would think that those numbers are actually text. So let's keep the default value false for this input. The easiest way to tell Dynamo where our file lives is by using the File Path node, which we can find in the Core, Input section of the node library. By clicking the Browse button, we can navigate on our computer or our server to the location of the file that we want to import data from.
We can tell that Dynamo is looking at the right file using the node preview at the bottom of the node. We'll plug that into the file input. Next we need to use the string input to specify the name of the worksheet in Excel that we want to import data from. If we hop back over to Excel, you'll see that the data we're interested in importing lives under Sheet1, with a capital s and no space between sheet and one. I'm gonna close up Excel, and drop in a string input.
Now we'll go ahead and type Sheet1, exactly as we saw it in Excel with a capital s and no space between sheet and one. We can go ahead and plug that into sheetName. A yellow node means that we've encountered an error. Let's hover over the text bubble icon to read the error message. This is a fairly common error message. It basically means that we provided the wrong data type to one of our inputs. This is actually a really common mistake that I tend to make pretty frequently when I'm bringing in data from Excel. We're actually missing one node between our File Path and our Excel Read node. It's called File.FromPath.
We can find it at the very bottom of the Core section of the Node library, in the File section. Going to do a little bit of node cleanup, dragging my File Path node a little bit to the left. Then we'll hook up our new File.FromPath node and this output should now plug into our file input. You'll notice that the Excel file now reopens. That's actually a good sign, it means Dynamo is actively reading this Excel file. I'm going to navigate back over to Dynamo and you can see that our yellow node is cleared.
It seems that this node fixed the problem. Let's pin down our node preview and see what Excel data looks like once it's brought into Dynamo. Looking at this output might seem a little overwhelming at first. What we have here is a series of nested lists, or a list of lists, meaning that we have a list of ten items and each item has its own list, which we'll call a sublist, of five items each. Let's have Dynamo fill half the screen, so we can compare this output side-by-side with Excel. Comparing the data to Excel, we can see that each Dynamo sublist is a row of data in Excel.
Dynamo has looked at the first cell in each sheet and made its way all the way across the row until it ran out of data and then it started on the next row and repeated the process. You can see that our first list in Dynamo reads Number, Description, Width, Height, # Shelves, which is our first row in Excel. Number, Description, Width, Height, # Shelves. Then our second list in Dynamo starts at the second row in Excel and so on, so forth, all the way through our data. So great, all of the data in a row is grouped together in Dynamo as sublists, but what about the data in the same column? It might seem like the relationship between data in the same Excel column has been broken when we brought it into Dynamo, but actually let's take a closer look.
It's less apparent than the grouping of data in rows, but we can see that each item with an index of zero in Dynamo comes from the first column of Excel. See? Index : Number, S-1, S-2. In Excel, we have Number, S-1, S-2. In the same way, if we compare index : Description, Shirts, Shirts. Description, Shirts, Shirts. And so on and so forth. So even though column data isn't grouped together in Dynamo, all of the data in the same column still share the same index with the respective sublists, so the data is still related, it's just a little bit less apparent.
I'm going to make a housekeeping suggestion before we move forward with this data. Look at the first row, it isn't data per se, but more of a header, or a label, that identifies how the data in the rows below should be used. Let's separate the first item from the rest of the list, which describes the actual retail shelf types in the project. We'll go to the Core list section of the node library, where we'll find lots of nodes that come in handy for working with lists, which we'll cover in more detail in another video. But for now let's drop in the FirstItem and RestOfItems nodes.
Both of these nodes take a single list input. Let's plug the nested list coming out of the Excel Read node into each of these two nodes. Note that these nodes aren't drilling down into the sublists, it keeps all of our sublists intact. Dynamo considers each sublist to be its own item. Let's also try to stop thinking about this data in tabular form like it was in Excel. These aren't a table anymore, they're different lists of different information. But remember they're all still sorted in the same way that they were in Excel and each item is still related to its neighbors in the same row, because they're in the same sublist, and its neighbors in the same column, because they all share the same index.
As one final step, let's group all of these nodes together, so that we can easily identify in the future that they're all working together to import data from Excel. We can add these nodes to a group by selecting all of them together. I'm going to click and drag a window across all the nodes. And then I'll click the Edit pull-down and select Create Group. I'll double-click the group title and name it Import Data From Excel. So we've now been able to import data from an Excel spreadsheet into Dynamo, which with just a couple more steps can easily be matched up with elements in a Revit project.
Or we can analyze and modify this data according to the needs of our project.
- Placing and connecting Dynamo nodes
- Understanding Dynamo's data types
- Performing math functions
- Creating number lists and text strings
- Writing data to an Excel spreadsheet
- Creating points, curves, surfaces, and solids
- Analyzing geometry
- Linking a Dynamo-driven SAT into Revit
- Placing Revit families with Dynamo
- Creating Revit views and sheets with Dynamo