Spreadsheet/List manipulation inside of Dynamo


#1

Are there any resources available to manipulate incoming spreadsheet data?

I’m importing cable descriptions for post stress concrete. There is a variety of data (string, integer, double). i need to capture the point data (double) as well the description data (anchor type, size, etc…). I do need to organize the data into lists for geometry and table creation. I’m having difficulty finding the right tools to find the data and then isolate it for further refinement and utilization.

I’m looking for code snipets, and any guidelines for taking apart the spreadsheet. the incoming data is column labeled so I do transpose the data upon import. However, it creates a number of levels in the lists that make it difficult to understand how and what I should be going after.

Any help would be appreciated!

Larry H.


#2

When you transpose Excel data you should only have L3, L2 and L1 for list levels. This image might help if you think of the levels from tranposed Excel data as:

a list containing = all of your excel data (@L3)
a list = your excel data grouped by column listed by row (@L2)

Depending on how your headers are (any merged cells, headers in between data rows, etc.) you may need to remove list items before transposing (remove rows). Further isolating/refining/utilizing the data is a matter of working with lists. If you haven’t already I recommend doing the Dynamo Primer - here is the section on Lists: http://dynamoprimer.com/en/06_Designing-with-Lists/6_designing-with-lists.html

And here is the Dynamo Dictionary section on nodes pertaining to lists (it will show example images/files of how each node works): http://dictionary.dynamobim.com/#/Core/List

Also looking over previous posts in this forum that pertain to reading/writing Excel could also help since most will have example images and explanations of the process. Hope this helps :slight_smile:


#3

List manipulation is quite doable, and in many ways but the finer points can be confusing.

As @awilliams pointed out, you should check out the dynamo primer. This is the best source to get to know this stuff. In fact if you haven’t already you should do Every exercise and read every page, cover to cover, doing confusing parts twice. Otherwise you run the risk of missing some basics and putting yourself in the deep end before you know the basics, which will only lead to headaches and frustration.


#4

Hi Amy,

I have completed a number of instructional videos and am a trying to get some useful output. I’m completely aware of my novice basis. However, I’m not even getting the tutorials to work out! I’m not sure if code changes have occurred that would complicate the tutorial examples.

The following is the second example graph from the designing with lists section 2 tutorials. I am using the provided download graph and simply added the requested List.GetItemAtIndex , and code block that specifies the 0 index. (I get how indexing and arrays work.) The output of the List.GetItemAtIndex is null. As expected there is no preview for the single line at point 0. The tutorial clearly shows everything is hunky and dory. There is a lovely line at points specified by the PoinAtParameter node for each circle generated.

I guess it’s me but it feels like this programming language is kinda sketchy. You should be able to run a basic tutorial with success. That should be goal of every tutorial. You want the learner to come away feeling confident. It’s critical. I am not coming away from a day’s worth of effort feeling confident.

Thanks for trying!
Larry H


#5

Preview the output of the Line.ByStartPointEndPoint node. I’d wager that’s not a list based on the results you’re seeing.

Also the best way to share an image of a graph is with the camera export button, on the top right of the window, just outside the workspace.


#6

Hello Again,

Thanks for the pushing the primer on me. It has proved to be a great way to move ahead with Dynamo. I’ve completed the List section and the code block section. I am particularly intrigued by the code blocks as my experiences have been with text-based languages. Although, I really like to be able to try alternatives and have immediate feedback through the node mechanism.

Okay, I am importing from an excel spreadsheet that provides x,y,z locations for a duct system in a concrete floor. This is not a duct in the HVAC world. Rather a route by which steel tendons are added to floor system after a pour.

I am working to create a solid mass in the floor that can be passed to Navisworks for collision detection with other mechanical systems passing through the floor.

I have successfully managed to create a wire layout from the geometry. However, I am having a difficult time positioning the Duct profiles in order to sweep along the wire path. The wires criss-cross the slab and therefore inserting profiles at the Start point is not enough. In order to successfully sweep a solid I need to position those profiles normal to the wire path.

I’ve created both planes and direction vectors based on the point data, but I need some List and Geometry manipulation to properly position the duct. Some of the layout lines require profiles in the X-Z plane others in the Y-Z plane. (ie: crise-cross). A method to test each point through a while loop of length =List.Count would seem reasonable.

Thanks for your help and patience. I have a shot of the graph attached.

Regards;
Larry H.


#7

Can you give a sketch of the layout you are after? If you have a ‘path’ than is a polycurve or series of curves youcan use a Curve.PlneAtParameter to get a plane to host your profile to, and then perform the sweep rather easily. Example files would also help.


#8

Hi Jacob,

Rather than send a large Revit file, I’ve created 2 snips.

  1. Overall view of the wires paths
  2. Profiles in YZ plane with a line=vector direction attached.

You’ll notice that I have attempted to rotate the geometry in the graph, but because I’m taking the whole enchilada of a list and not testing for directionality (X-Zplane, Y-Zplane) prior to profile construction either one set of profiles or the other will land up parallel to the sweep path.

What I not so artfully asked in my post; Is there a way to test for this directionality, and what is the recommended method to test the list. While, if, case ?? I get how to do this in text based languages, but not so sure in Dynamo… I’m sure it is relatively simple, but the only example I’ve seen is on the forum and leveraged a code block.

Larry H.


#9

Curve.PlaneAtParameter will work here. After you make the planes set your profile based on that. If you’re actually using circles as shown you can quickly create the profiles with the circle.byplaneradius node.


#10

Hi Jacob,

i tried Curve.PlaneAtParameter. input curve=polyCurve.ByPoints, 0 parameter (startpoint). The resulting operation returned null. I have created a line geometry at the startpoint/endpoint as well (for visualization). I tried that as input as well and the resultant was null.

You may have noticed that I created a Plane.ByOriginNormal from the individual startpoints. I did try that as input to Ellipse.ByPlaneRadii with equal result.

I am using Eliipses as input. Using circles as the input wouldn’t provide the correct feedback as to orientation. (A circle is equal across two axis.) The ellipse gives you a major Axis as well as minor Axis to understand exactly how Dynamo is orienting the profile. These are not the actual duct geometries. One is, indeed, round the other is oval. (Rectangle with filleted corners). I anticipated that orienting everything correctly would be a sticky wicket and went after that problem first.

However, your solve for the roof type issue will come in handy here as I’ll have to have a fairly robust logic routine to read the duct type from the incoming spreadsheet and draw the “real” duct.

I’m not sure I’m using the right inputs for curve type. Would a polycurve be the correct input or is this node looking for a NURBS curve?

Thanks Again,
Larry H.


#11

Hi Jacob,

Hold on a minute sir… I switch to “Manual” for, once again, visualization sake… and Dynamo worked exactly as expected. It returned null… DOH!

I am getting results now for Curve.PlaneAtParameter. Not sure if they work, please forgive. I have a number of approaches I want to try now that NULL is out of the picture.

Thanks.
Larry H.


#12

BOOM! Thanks!