Linking Excel Data Into Navisworks – Part 4 Connecting Excel to Navisworks

This is the last stop on our journey. We have made selection sets in Navisworks, exported a custom list of data to a .CSV file, opened that file and added more data in Excel, and now it’s time to get that new data to connect up to our items in Navisworks.

We need to get a little technical here. To accomplish this connection, Navisworks relies on your Windows’ PC ODBC connection drivers. This is how Windows manages what databases can be opened, and in this case we will be telling Navisworks that our Excel file is a database.

There is no one size fits all solution, but this step is usually the bumpiest. There are different versions of Windows, and different installers for the ODBC connectors out there, so I cannot tell you how to make sure you have the right one. A couple quick tips:

• Be sure that if you use the 32-bit version of office, you use the 32-bit version of the ODBC driver.
• Be sure that if you use the 64-bit version of Office, you use the 64-bit version of the ODBC driver.
• If you are using the One Click version of the Office 2016 install, you need to grab the 2013 verison of the Access Runtime. Why? I don’t have a good answer for you. All I know is that the 2016 version did not work while the 2013 did.

You can check what ODBC drivers you have installed already by going to the ODBC Data Source Administrator window in Windows. The first tab of “User DSN” should have listed the Excel Files if it’s installed properly.

Mine looks like this

You will need to reach out to your IT folks if you need a hand getting the right version installed and setup. Once that’s good to go, hop back in you Navisworks model.

There are two places you can establish this data connection. The first is right on the Home tab and its the DataTools button. This sets up a data connection for any thing you open in Navisworks. This is not the one we will use, but it could be hugely beneficial to you if you want to always have a connection setup.

We are going to use the DataTools tab found in the File Options window that is accessed by clicking File Options on the Project panel on the Home tab. This connection is for this Navisworks file only. The steps are identical to set up the data connection, so learning it here will work for the other one.

The File Options DataTools tab

Here are the basic steps to start to get our Excel data back into our model:

1. Click “New…” to set up a new DataTool Link.
2. Give it an appropriate Name
3. In the ODBC dropdown, find the correct Excel connection
4. Click “Setup…” and then “Select Workbook…” to track down the Excel file then click OK

That’s the first (and easy) part. Now we need some explanation. The SQL String window is where the bulk of the work happens and it’s also the hardest part. If you know SQL, this will be easy. If you don’t, I am going to show you the string I use and try to break it down to make sense.

SELECT * from [Hardware$] WHERE “GUID” = %prop(“Item”, “GUID”)

“SELECT * from [Hardware$]” means simply grab everything on the tab called “Hardware” in my database, which is the Excel file. You have to put the tab name in brackets and end with a $. That’s important. It’s not going to work if you miss that.

“WHERE “GUID”” is starting to match up our elements. The GUID here refers to the column in our Excel file. So you have to make sure that column name and this spelling match EXACTLY.

“= %prop(“Item”, “GUID”)” wraps things up. So we are matching the GUID from the spreadsheet with a property (%prop) of elements in the Navisworks model that lives on the “Item” tab and is called “GUID”).

I know I have GUID twice and that might be confusing, I just need to remember that the first one is from the Excel file and the second one is the item property. Remember, I can name that column in Excel anything I want, so I could name it “MyGUID” and my command would look like this:

SELECT * from [Hardware$] WHERE “MyGUID” = %prop(“Item”, “GUID”)

The final thing I need to do is set up what Fields I want to bring in. This is simply those new columns I created in my Excel file and what I want them called. There is no “new” button here, you just double-click on the blank area to make a new one. I need to make one for both the columns “Hardware” and “KeySet”, but for “KeySet” I will change the Display Name to “Key Set” so it’s a little more readable.

Once everything is setup, my Edit Link window will look like this:

One I click OK, I can then check the box next to my new DataTools Link and if everything is set up correctly, the data will just be aggregated into my model. So, I select a door and find that I now have a new Properties tab called “Hardware” with two new properties on it.

New tab – new data

That’s a lot of reading for today, so we will have a wrap-up epilogue post coming soon with some tips, pointers and links to other resources that can help you expand your knowledge on this.