Dynamo not pushing correct pipe data to Civil3D

[Yellowjacket98]

Once again I feel like I am at the end of my journey on a script, but then one little item falls apart…
I have two Dynamo runs that pull data from an Excel spreadsheet and push it into Civil3D. Run A is set up to where I select an individual pipe and it pulls in design data from the spreadsheet – it works like a champ. Granted there are multiple clicks, but it works. However, in Run B the user selects a pipe in a network, and Dynamo populates a list of those parts and pairs them up with flowline data in a dictionary, which I then push into nodes that adjust HGLs and such in the Civil3D file. However, it adjusts pipes randomly… some are spot-on, others are using info intended for other pipes, and still others are based on numbers that are nowhere to be found in the file.
For the spreadsheet, the index (left-hand) column is completely filled (no nulls) and corresponds to pipe names or cells toward the right that contain nothing at all (blank rows between portions of the network for visual clarity). Yes, I used a string called “SPACETAKER” to fill in for null cells.

I might add that it randomly disconnects one or two pipes here and there, but I have a suspicion it will fix once it gets the data ordered properly. Waving a white flag here…
Thanks!

[JacobSmall]

Going out on a limb to say that the time to reproduce the following is upwards of 4 hours worth of work:

1. Civil 3D file with a pipe network to your modeling standards and methods
2. An excel file to reproduce the dictionary method which you built.
3. A graph to reproduce the error and fix it

If someone only had to review the graph, modify it, and provide the modification… that’s potentially as little as fifteen minutes.

The easiest way to get help is to help users help you - that is to give us the capacity to provide the fifteen minutes rather than the 4+ hour effort. Please provide a sample DWG (not necessarily a project, but purge stuff out and delete all but a minimal data set to prove the graph - day three pipe networks of 5 pipes), the excel file, and your current DYN.

[Yellowjacket98]

Jacob,
You make an excellent point as far as putting out enough information to work with ahead of time – I am still new to this and am unfamiliar with what people know off-the-cuff and what takes investigation. That said, I ended up not using this script anyhow, because it ended up taxing my processor to the point of instability. That said, whenever something like this comes up again, I will be sure to put in info so that folks need not waste any more time than necessary.

Thanks!

[Yellowjacket98]

So here is a simplified Dynamo file, CAD file and spreadsheet. I have narrowed down the issue to this (and it is included in the attached examples): Originally I had pipes P10001, P10002 and P10003. Then I interjected a new pipe into the network after pipe P10002; this new pipe became P10003 and the former became P10004. When I run the Dynamo (which as noted in the green instruction blocks is based on the user selecting a pipe from the network), it puts the latest pipe last in order, even though it should be further up the chain. Thus, the flowline changes to the pipe are incorrect (out of order). Is there a type of “sort” filter or something that should be run? If you look closely, everything stays in order (both the Keys and the Pipe Names) until the very end… pipes.dwg (955.7 KB) Pipes - by network2.dyn (106.8 KB) pipes.xlsx (658.6 KB)
This is just a small sample of what I am trying to accomplish, as it is not uncommon for me to interject new pipes, or to remove others, as a project goes along.
As always, any help is greatly appreciated!
(A note – be sure to link up not only the spreadsheet but also the correct sheet by name)…

Jacob, you’ve got some good content on Dynamo there on YouTube – thanks for posting that.

[mzjensen]

Hi @Yellowjacket98,

This is a bit delayed, but I thought I’d offer my thoughts in case you’re still working on this. There were quite a few things that I identified that would make this script a lot cleaner, so instead of talking through all of them, I’m just posting a new version. Give it a try and post any questions you have.

Note that I’m also posting a new Excel file with an additional sheet at the end. It looks like this may be some type of standard company spreadsheet, in which case it may take some work to change, but…I highly recommend that you simplify the data that is being read by Dynamo so that you have a single row of column headers followed by the data entries. In the long run, this is going to be more bulletproof and much easier to work with in Dynamo as opposed to a large group of nodes with the sole purpose of shifting indices, removing nulls, removing/adding indices, etc. As it stands, even a simple change to the spreadsheet (eg. adding a row or swapping the order of columns) would break the whole script.

pipes_MZJ.xlsx (659.5 KB)
Pipes - by network2_MZJ.dyn (57.3 KB)

[KirkWM]

Thanks @mzjensen for the great example on using dictionaries for importing from excel.

[Yellowjacket98]

Thanks for the work on this – it definitely has the fat trimmed out of it.

The reason I did the column shifts and null cancellations is because my intent was to not alter the spreadsheet format our guys use. If they end up doing too much front-end or in-process work to accommodate the Dynamo script, it may negate its time-saving intention. You are 100% correct about the possibility of things falling apart if one column is added or removed.

One possible option is to create a separate sheet in the spreadsheet that reads the other full spreadsheet. The catch there is the insertion or removal of pipes; once a pipe ID changes in the spreadsheet it has to be manually changed in the drawing file. Additionally, numbering itself becomes an issue – you almost need to set up the name with two “place holder” zeroes at the end, so that if you amend a pipe name, you incorporate each addition into this spot as opposed to renumbering every pipe past that point.

I will keep hammering away at this from the Excel point of view and see how I can best use that data. And I will definitely put this .dyn through some trials and see what happens.

Thanks again for all the effort!
CRF

[david_licona]

Hi, just offergin some thoughts. I had a similar problem where I dont wanted to change the excel data manually, so what I did was to create a quick macro in excel to create a new sheet and get the info I wanted to use in Dynamo, and then a DYN graph. It made things a lot less complicated in Dynamo afterwards

[Yellowjacket98]

This actually makes it so so much easier to work with – thank you for the help on that! The catch was figuring out how to remove “blank” lines and then have the second sheet update after changes to the first (macros were the key there).

Thank you!

[Yellowjacket98]

Not sure if I responded to this already, but man this looks fantastic… projects kicked back in so I am just now getting back to it. Cleaner is definitely better… and as I mentioned in my posting a few minutes ago, cleaning up the Excel file is without question the way to go (though I had to learn some VBA).
Thanks for the work on this!