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

[jakelucier]

Hello,

What I want to do is a take VERY large list of values from excel and populate the respective Family Parameters in Revit.

• The only field which is consistent between the two sets of data is the Mark Value.
• The List are not sorted in a logical manner from excel.
• The columns are consistent in data type and format.
• The columns in excel and the parameter names match, but are in the wrong orders in the lists.
• It is quite a large data set in excel and I would like to maintain the way it is formatted.

What is the preferred method to map this item = to this item in this list and is there a good example somewhere? I envision something like an Index(Match( Function in Excel. Where it knows This Row is = to this set of data and it is always in a certain order.

I have 38 Parameters, and 38 Columns of Data They are titles the same. But not in the same order when I get it all into Dynamo.

I want to take a singles list of parameters and a List of a category of elements (say mechancical (FCU)) and assign the data into the correct parameter field. So if I have 60 FCU’s and I have 38 parameters per FCU that begins to expand quickly

Image on Left is the Full List of Assets by Column. Image on Right is a snap of the cells from excel.
Note: I need to add an empty list so those numbers in the watch categories are sync’d up. (the field won’t be null in the excel later)
Below is a list of Parameters to be filled in (matches the ones from excel)

Below is snap of the parameters applied in the model and match the excel asset register. (this Mark value is consistent)

[m.rijsmus]

can you show what you have tried ?

[jakelucier]

Yeah, I have updated it atm

[m.rijsmus]

something like this?

Edit:
it was rubbish

sorry

[Andrew_Hannell]

@jakelucier

as an alternative to Marcel’s approach, you might want to use either:

• a Dynamo dictionary, or
• a database lookup

If you search on this forum, you will find some posts on these topics (some from me)
If you want help specifically on your data, please share your Excel file, or something which shows how it is structured

Andrew

[jakelucier]

Thanks, let me try these methods out and see what develops.

[m.rijsmus]

second try

jake.dyn (18.1 KB)
Param.xlsx (9.2 KB)
Paramx.rvt (452 KB)

The trick here is not to organize your excel data, but make your Dynamo data match the excel data

[jakelucier]

Thanks, that package is very helpful. Now it generates a different issue, of course.

For some reason It is not writing the proper excel data to the correct item. I see how it is indexing the items together, but I do not understand how it is calling them or organizing them.

To test it I mapped the same cell I was indexing to see if I could get it to write the same info to a different field. Then the items did not match up. You can see this when I tried to list the index items it was calling upon.

Any thoughts? I have uploaded a sample list of data as well.
Mechanical Equipment Test Upload.xlsx (159.4 KB)

[m.rijsmus]

I can see you are working with Dynamo 2.0
I tested with Dynamo 1.3.2 and had some troubles at first too.
I switched the lists on the Bakery node and then it worked.
I could sort my data in Excel and the outcome did not change.

Normaly i use this method, but you need the ElementUniqueId of the Revit elements in your Excel.
http://www.revit.com.au/1564/

[jakelucier]

let me try 1.3.2 as well, I was in 1.3 earlier this week too. As for switching the lists . . . pretty sure I was trying this too - but let me keep mashing buttons and inputs/outputs. . . .

[Andrew_Hannell]

this is how you would do it with the database lookup
~600 records with 38 parameters should not be a problem

Mechanical Equipment.sqlite.txt (138 KB)

[jakelucier]

Hi Andrew, I am giving SQL a try, having some trouble understanding the codeblock syntax you are using and where I can read up more on how to query the items. Any thoughts?

One thing I have noticed is that you have an SQLite file extension where I can only get a .db or .SQL extension. How can I do that?

Cheers,

[Andrew_Hannell]

Hi Jake

I’m not sure where to start- there is a fair bit to SQL, although you only need to know a tiny amount.

My suggestion is to download an application such as SQLite Studio https://sqlitestudio.pl/index.rvt, and Google/try out some queries on the sample database that I provided
Note that this application can also import data from a CSV file

The file extension does not matter- you can leave it as .txt if you want

For example:
select * from tbl_equipment where [floor code] = "L01" order by [equipment Use]
or
select [serial number] from tbl_equipment where [manufacturer] = "Trane"
the * means all columns
things in square brackets are column names
the ‘where’ and ‘order by’ parts a self explanatory

Please post back if you have a specific question

[jakelucier]

Thanks, I had downloaded SQL Lite and populated the database to give it a try, then when trying to bring it back in I realized I didn’t understand the syntax dynamo would like. The rest of the info is really helpful too.

Thanks

[Andrew_Hannell]

I forgot to say:

In Dynamo, the node is concatenating stuff so that it builds a query for each Mark

"SELECT [model number],[serial number] FROM tbl_equipment where [equipment use] =" + "'" + Mark +"'";

The enclosing single brackets are not strictly required- but any values that contain a space need to be treated this way