Hello everyone,
I have an Excel spreadsheet that I can successfully connect to Dynamo, and I want to select a particular item from that spreadsheet based on a number that changes depending on what object I select in my linked drawing (Civil3D .dwg file). Right now I can successfully select the object in my drawing, but I am unable to work the lists to give me the corresponding data from the chart.
As an example (refer to the attached image), I can successfully pull in the number “200” into the Dynamo canvas, but I am unable to extract out the number “2” for size or “240” for amount. Yes I could put in a number manually that says “200”, but I need it to come from the chart, because I want to select item “201” as well and have it return those numbers. Most importantly, the “size” and “amount” numbers may change over time, as I intend this script to be used multiple times with one ever-changing spreadsheet.
I have tried several things, from transposing the list to shifting the cells, but I am stuck. Does anyone have any ideas on how I can get over this hurdle?
Thank you!
2020-10-22T05:00:00Z
Hi @Yellowjacket98,
You can create a dictionary and then use it to lookup the values.
LookUp.dyn (25.0 KB)
That is perfect! In fact, I didn’t have e-mail set to bring in pictures, so I did not realize you had included the script until just now. But that forced me to research Dictionaries (I had read about them but had shied away) and figure it out on my own.
The one error I came across was it did not like “null”, but it is easy enough to just fill in in the spread sheet cell with a character.
[I’ll chase a rabbit here: When I was in high school in the early 90s our teacher showed us how we could put in a hidden character at the end of a file name, and because we were in DOS, you could not tell there was a gap between the name and the file extension (it was all in columns). The character was <alt> 255… (you have to use the number pad)… it’s basically a space. I have found that you can use this in AutoCAD/Civil3D instead of “_” where spaces are not allowed and it usually works just fine, and makes things look much cleaner. Also, in Sheet Set Manager you can use it instead of the actual spacebar and have better control over the carriage return of text for sheet titles and such. I just tried it out in Excel and you can insert blank spaces there as well – not sure what it will return in Dynamo, but is worth trying out].
The “Transpose” feature makes it very interesting – I told one of my coworkers it’s like playing with a Rubik’s Cube of data.
This made my day, and will potentially save our crew a ton of time. I greatly appreciate it!
CRF
Glad that it worked out for you.
Yes, it gets a bit crazy when you throw in a null value. But seems like you have that thing sorted.
Good Luck.
