Learn Dynamo - Lesson 7: Working with Excel

[@MrDrenahmeti]

I know we are going one level deeper when Gavin says: "So what we wanna do..". Great tutorials, thank you!

[@AussieBIMGuru]

Haha yes one of my catch phrases. I say the word 'essentially' way too much as well.

[@jedsanford7879]

your Crumple Excel nodes are the best for me. Easiest to work with :D

[@AussieBIMGuru]

Glad they help!

[@iftekharkhan8879]

U use a logic node at 4.59 ..can u plz explain...thanku

[@AussieBIMGuru]

There are some easier ways to do this process (I was less experienced in Dynamo at the time), but essentially I am replacing 'empty' parameter values with something we can refer to more easily - in this case the characters $$. I use the logic.if node from the Zebra package here as it works at levels unlike the standard if node. I'm checking which elements equal to nothing, then replace them with $$ if they do by using an if/then/else logic. By working at L1//L1 in levels, we end up essentially flattening our data. I reconstruct these values back into sets equal to the number of elements in this case using the chop node after this. An easier approach would be to work at L2 with our If function.

[@iftekharkhan8879]

@@AussieBIMGuru thanku

[@nassimmehrvarz9375]

Hi, thank you very much for sharing your knowledge! It would be appreciated if you could give me some guidance. My aim at this level is to export elements' properties to an external database. How can I get some selected parameters that are in Revit schedule? (like width, structural material, etc. for several elements at the same time!) The node "Element.GetParameterValueByName" only gives some element parameters. And is there any way to perform all tasks that you did in the tutorial for several elements at the same time? like a list of several categories link to "All Elements of Category" node?! Thank you in advance for your time.

[@AussieBIMGuru]

Some of these parameters will belong to the element's type instead of the element itself. You can get the Element instances 'type' as a parameter value, and then query that types parameters in parallel, then merge the outcome together at the end. There is a pair of nodes from the Rhythm package which can query both instance/type properties of an element as well which might help here. You can collect multiple categories at once by feeding in a list of categories using list.create, but be mindful not all elements contain the same data so you will need to think about how you structure the combined dataset (e.g. will it be one matrix, or many?). If you ask some elements for a data field they do not have, they will return a null value (error) and this will either need to be replaced (Clockwork has a list.replacenull node) or cleaned out.

[@nassimmehrvarz9375]

@@AussieBIMGuru Thanks for your help.

[@bigkieth23]

Hi Mate, big fan of your tutorials, currently working through your Learn Dynamo lessons whilst in lockdown. I have a quick question, what would be the process to read/export from a Revit schedule then import the updated excel data back to the Revit schedule? Thanks for your time.

[@AussieBIMGuru]

Thanks! If you have don't have to use Dynamo, you could use BIMOne's Excel tool; apps.autodesk.com/RVT/en/Detail/Index?id=6290726048826015851&appLang=en&os=Win64 You could also use SheetLink by DiRoots; diroots.com/plugins/sheetlink-revit-to-excel/ Otherwise you could use Dynamo, but would need to make sure the data is returned in the right type (e.g. if it's Yes/No it needs to be a boolean). You could use a node like 'set parameter' to update element data if it's parameter based.

[@joshwhite3590]

Hi, I'm also very new to Dynamo and trying to follow along to create this script. I'm not getting the same information pulled you and also getting some errors. The list.count node only reads "1" and the logic.if node returns a null value. I'm running Revit 2020.2 and Dynamo 2.3.0.7661.

[@AussieBIMGuru]

Hrm it's been a long time since I made this so hard to recall any errors I found, but try using the 'If' node instead of the logic.if node. The count node might need to be set to @L2 in the levels input possibly. I'd suggest using this video to learn excel as well, it's focused at beginners and I made it much later on when I knew more about dynamo: kzbin.info/www/bejne/ip2baKeta9ilesU

[@krishnanvinothkumar8416]

Hi Aussie, May i check is it can use the type parameter import excel date?

[@AussieBIMGuru]

Dates can be brought from Excel to Dynamo if your parameter can store the data type. There is a thread here discussing some ways to deserialize this complex data type: forum.dynamobim.com/t/import-dates-from-excel-and-format-weird-numbers/16996/4

[@krishnanvinothkumar8416]

@@AussieBIMGuru Thank You

[@axol9]

Hi Aussie BIM Guru! I'm new to dynamo and I have tried the same thing that you do, but with Spaces and the following parameters: Name, Number, Specified Supply Airflow, Specified Return Airflow, Design Heating Load, Design Cooling Load. I succesfully export data from Revit to excel, but I could not import back to Revit from excel. The last node "Element.SetParameterByName" has a warning: "The parameter's storage type is not a string". I watched your video at least 3 times, but could not figured out what wrong with it. Could you help me?

[@AussieBIMGuru]

Use the 'object.type' node to check your excel data. Some of those fields will need conversion to numbers I believe, probably the loads and flows. This warning is dynamos way of saying that some of the parameters dont use text as their data type in revit.

[@axol9]

@@AussieBIMGuru Thank You your answer! Well I did in the "easy way". I created shared parameter (Text) in revit and deleted "string to number" nod. Well it's not the best way, but it works at least.

[@aleksandrat373]

When I am using List.IndexOf node I still get "-1" value, no matter how I change data types (integers or strings). It seems this node works with one list and single element, then it returns correct value. However, when I put a list of elements like in your tutorial, it doesn't work, even if I use the levels. Any explanation or workaround? :)

[@aleksandrat373]

It was some strange data formatting while importing from excel. Even if ID is a text value in Excel, Dyn brings it in as a number and while converting to string adds .000000 at the end which needs to be removed.. strange workarounds but works in the end.

[@AussieBIMGuru]

Yes unfortunately excel data is read pretty strangely sometimes. Best to ensure the data types are set in Excel to TEXT before importing to dynamo, then reading as strings as well to be sure.

[@MonicaKadu]

Hello im new to dynamo and trying your scripts and lessons to learn dynamo while creating the above script i got an error after typing the function code block in the beginning for the parameter names the error says - intent expected im not able to move forward with the script

[@AussieBIMGuru]

It means you've likely missed an indent at the end of one of your lines ( ; ). Make sure all your lines with code end with this character.

[@iftekharkhan8879]

And sir...how to arrange the nu i.e room nu is not in sequence...in execel....plz explain this also how to do this i saw here but can't understand.... thanku sir

[@AussieBIMGuru]

In this case you should export their element Id as a column as this will never change. You can then match it when you import data back to Revit to the element ids in the model.

[@iftekharkhan8879]

Thanku for reply sir..have a nice day

[@iftekharkhan8879]

@@AussieBIMGuru hi sir how r u...sir can u plz tell me.some other way how to arrange and match the order or serial nu between Excel and revit...i.e room name and number here....thanku sir

[@AussieBIMGuru]

@@iftekharkhan8879 export it as a column in excel, then when you import data back you can get the ids of all elements to match in revit, get the index of those ids using list.indexof and then get the element at that matching index using getitematindex.

[@iftekharkhan8879]

@@AussieBIMGuru sir ..can u plz make a video ...thanku