Modelling Piling from Microsoft Excel using Revit & Dynamo

Happy New Year to everyone and straight back into the deep end with more Dynamo! Way back in 2013 I reviewed the Excel Model generation tool that allowed the creation of Revit elements based on a spreadsheet. This allowed the modelling of various objects but was limited to generation of only geometry and did not allow the user to add data to the objects.

Link to YouTube Video: YouTube Tutorial

I used this tool to create a Revit piling layout derived from AutoCAD geometry. See Post Below.

https://revitstructureblog.wordpress.com/2013/03/17/modelling-revit-piling-from-microsoft-excel/

I have since used Dynamo to achieve a better method for the setting out of piling as well as setting the Pile number and even loading.

In this post I will step you through the Dynamo node so that you can create a similar routine. Let’s start with the raw Excel data. In my example I have the Pile Number, the Easting and Northing Coordinates, the Level, Depth (Length of Pile) and the Pile Diameter.

The following image below shows how to select the Excel file and then pass the filename and sheet name into the Excel.ReadFromFile node. Note that I have used a Code Block for the Excel Sheet Name but you can of course use the string node instead.

The next stage is to remove the unwanted headers from the Excel data. The List.DropItems node is removing the first list. Note that list is a ‘nested’ list so will remove the [0] index as shown below.

Next the transpose node is used to sort the data into the correct columns. The current data is stored as rows. The List.Transpose node converts rows to columns as shown below.

The next task is to get all the required data into separate lists so that we can later ‘feed’ this into the nodes to create the piles and also add data to each Revit Element. Dynamo has a node called List.GetItemAtIndex which gets the relevant data. In the below example I am getting the data from index 1 which is the Eastings Coordinates. Index 0 is the first column which is the Pile Number.

This then leads me onto the next problem. By default the piles will be modelled from the origin (0,0,0) and Revit has a geometry limit of 20 miles which will clearly be broken by the large numbers (This project is around 200 miles from the OS base). In the below image you can see the ‘effect’ of geometry modelled a long way from the origin. This is referred to as graphical degradation and will affect the visual and also the accuracy of snaps etc.

In the Project I have set the Project Base Point of a known Pile coordinate

I have then subtracted these values from the Eastings and Northings to give me coordinates relative to my project base point. In the below example I have the known Easting and Northing setting out point set. I then pass this value into a code block (marked with red boxes). The Code block is taking two variables and then subtracting the set out from the real coordinate. The nodes with the blue frames are the original coordinates.

The next step is to then model each pile based on a pre-defined Foundation Family. In this example I have created a round pile with an instance parameter to control the Diameter and another to control the depth. I have named this family ‘Concrete Pile’. The FamilyTypes node will list all loaded Families in your current Revit project. We then use the FamilyInstance.By.Coordinates node to generate each family from the two code blocks that generate the X and Y position and the third which is the ‘raw’ Z levels.

It is now time to make use of the ‘metadata’ such as the Pile Number and the Diameter by utilising the Element.SetParameterByName node. This needs the elements from our FamilyInstance.By.Coordinates node and also a string for the Parameter Name. Again I have used a Code Block for this example. In the image below I am using the instance parameter ‘Mark’ to read the Pile number and then add this into each Pile. This is then repeated for the Pile diameter and if required, the loading.

So anther interesting use for Dynamo in a structural project. I will produce a video tutorial to accompany these notes in the next day or so.

Enjoy,

LawrenceH