Read information from a spreadsheet and assign Revit parameter value to the correct object

[Lars]

Hello.

I am looking for a way to form a Dynamo script that ensures that values which are red from a spreadsheet are assigned to the object it is meant for, but I need a little push in the right direction.

I have a Revit project that has several objects (wall, beams, columns and so on), but for this example let us say that my project just contains 10 walls. Each of them is given a unique ID: E.g. Wall-1 to Wall-10. Now I use Dynamo to export the information about these walls to an Excel spreadsheet, and then I send the spreadsheet to a co-worker. He updates the values and send it back to me. And when I want to import the values into my Revit project from the spreadsheet, my problem appears: What if I have made changes to my model (added, deleted or splitted walls) in between my co-worker gets the spreadsheet and sends it back to me , how can I be sure that Wall-3 gets the info for Wall-3 from the Excel sheet and not the info for Wall-4? I have experienced that Revit can change a wall’s position in a list when changes have been made. So, I want a code where I can be a 100 % sure that values are assigned to the correct object.

I think the solution might be to sort both the Excel list and the Revit list by my unique object ID. But I haven’t been able to find a working code for this yet.

Any thoughts?

Best regards
Lars

[jacob.small]

You need to use the GUIDs for selection and tracking for this to work. Write the GUIDs out when you export to excel, and then have the coworker do their stuff (not modifying the GUID values), and use the GUID to select the items in the correct order when you go to push the values from excel back into Revit.

I believe Archi-Lab has a package for the selection, referring only to ID in the name (Select.ByID maybe?).

[Andrew_Hannell]

As an alternative to Jacob’s answer (which is probably the easiest), the other option is to use some kind of lookup- using a unique identifier such as the wall mark. Ideally, the solution should be able to cope with a missing record in either the model or the spreadsheet- so not rely on having exactly the same number of records in both, or on the sort order.

You can achieve this using Dynamo dictionaries, or a database lookup- there are examples of this on the forum.

It depends a bit on how many & what type of parameters (assume they are all instance parameters) are being updated in Excel

Andrew

[Lars]

Thank you very much for your replies!
I’ll give both suggestions a try.

JacobSmall, what does IMT mean?

Andrew, do you have some links to these examples?

To be more specific I want just two instance parameters; Object ID and Object status.
Object ID is set by me and my colleague will set the object status. Both is text parameters.

[jacob.small]

Sorry - in the. My phone loves to autocorrect my frequent abbreviations. Edited the prior post.

[Andrew_Hannell]

The use of dictionaries is covered in various posts, but in a round-about way. The below explains it as simply as possible:
Notes:

• elementID used to match up objects, not GUID
• the spreadsheet contains a record without a matching model element & vice versa

The database option is covered here:

Using SQLite instead of Excel

I have been experimenting with SQLite with Dynamo recently & thought I would share with the group. SQLite is a pretty good & simple option compared to MSSQL or MSAccess. The main benefit as I see it is the data can be well structured & also allows simultaneous reads & writes (plus simple in a standalone file) A significant number of posts on this forum relate to Excel & I think many could be overcome. But I have to admit that the main benefit of Excel is that everyone knows how to use it…! …

Organize an excel register and input into specific parameter fields in model (excel order and parameter order not consistent)

this is how you would do it with the database lookup ~600 records with 38 parameters should not be a problem [image] [20180727%20sqlite%20lookup%20write%20parameter] [image] Mechanical Equipment.sqlite.txt (138 KB)

[Lars]

Thanks for examples, Andrew.

Here is my attempt:

I think this will work, but if anyone sees that it could mix up my data I’m pleased to be informed. I know that one node will give a warning if the length of the Excel list doesn’t equal number of walls with ID number in Revit.

My next step is to add an export function in case there is a change in ID numbers (added, deleted walls).

[Andrew_Hannell]

it looks like it should work

You might consider the Springs node I used rather than the standard ones since you can set a default value where there is not a matching key e.g. “unknown” or “not in spreadsheet” and this would prevent your error.

I don’t quite understand what you are doing with the string replace nodes- the parameter looks like it is numeric

[Lars]

Where can I get the Springs node, or is it something I’ll have to buy?

I’m using the string replace nodes to avoid the comma and all these trailing zeros.

Maybe there is a better way to achieve this?

[Andrew_Hannell]

No you don’t need to buy anything.

In Dynamo, search under Packages for “spring nodes”

If you haven’t discovered the packages yet, you’ll find that there is a wealth of useful stuff & nodes for specific purposes