Navisworks 2018 Database Link w/ Excel - Primer
For this demo, I am going to use the Gatehouse sample file that ships with Navisworks 2018. You can find the files C:\Program Files\Autodesk\Navisworks Manage 2018\Samples\Gatehouse\. If you have a newer or older version of Navisworks, the process basically works the same.
The Process
In order to connect an external data source such as an Excel spreadsheet or an Access database to a Navisworks model, we need to configure a DataTools within Navisworks. We will then be configuring the database connection. When working with the DataTools in Navisworks, there are a few very important rules to keep in mind. If you are using a 32-bit workstation, then you need to utilize the 32-bit ODBC drivers. 64-bit workstations need to utilize 64-bit ODBC drivers. It is also important to note, that the link to Navisworks is one way. Data can be linked and viewed inside of Navisworks, but the data can’t be updated within Navisworks.
My workstations are all Windows 10 64-bit workstations. My PC’s are using Microsoft Office 365. My Mac running Windows 10 64-bit on parallels using Office 365. The MS Access 2013 runtime drivers can be downloaded here: https://www.microsoft.com/en-us/download/confirmation.aspx?id=39358 If you are using an older version of office the MS Access 2010 drivers can be downloaded from here: https://www.microsoft.com/en-us/download/details.aspx?id=13255.
Data Source Setup
The Gatehouse project includes a sample Excel spreadsheet which is why I am using it as an example. The first thing I did to the file was add three column headers to identify the “Name”, “Date” and “Notes” columns (Figure 1). We are creating these labels to identify the columns.
Navisworks Setup
Our next step, you need to have the ODBC drivers installed, otherwise you will not have the data connection option. With that done, open Navisworks and the Navisworks model that you want to connect your external data source to. In this example, I am going to open the gatehouse model as indicated above. In Navisworks, on the Home tab, Project panel, click on the File Options button (Figure 2). This will open the File Options dialog.
In the File Options dialog, we will start the process of configure our database connection (Figure 3). As shown in Figure 1, click the DataTools tab and then click the New button to create a new DataTools link to open the New Link dialog.
In the New Link dialog (Figure 4), we first want to name the new link connection we are creating. For this example, I am using the name “Gatehouse Example”. We then need to select the ODBC Driver Connection type. We are going to be working with a Microsoft Excel data source, so select the “Microsoft Excel Driver (*.xls, *.xlsx, )” driver. Then click the Setup button to open the ODBC Microsoft Excel Setup dialog.
In the ODBC Microsoft Excel Setup dialog (Figure 5), we will select the version of Excel data file and select the file we will be using. I am using Office 365 so selected “Excel 12” from the Version: dropdown. Select the version of Excel you are using to edit the spreadsheet. Then click on the Select Workbook button to find and select the Excel data file. This connects the excel data file with Navisworks. If you have the default installation of Navisworks 2018 and are using the Gatehouse example, you can find the file at: C:\Program Files\Autodesk\Navisworks Manage 2018\Samples\gatehouse\. Just make sure this file has the headers we added in the section above entitled Data Source Setup.
After you have selected your excel file, click OK to close the ODBC Microsoft Excel Setup dialog.
If you are having issues with your Database Link connecting to Excel, be sure to check the Version of Excel database that you are using.
We are now back to the Edit Link dialog box (Figure 6). If we look in the connection section portion of the dialog, under the ODBC Driver selection, you can see that the connection has been updated. We can now complete the SQL String and Fields section of the dialog.
SQL String
The SQL string is used to specify how the database will be queried. I have found this part of configuring the database connection to be the most difficult. Fortunately, there are a lot of external resources available for creating SQL strings. My personal recommendation is to keep it as simple as possible.
The code that we will be creating for this example is:
SELECT * FROM [Gatehouse_Layerinfo$] WHERE "Name" = %prop("Item","Layer");
We start out our SQL String with SELECT * FROM The “*” is actually a field name, in this particular example, I am using the wildcard to query all the fields.
[Gatehouse_Layerinfo$] This is the Excel sheet tab name (Figure 7) that we want to query in our Excel spreadsheet. The Excel sheet tab name must be spelled just as it is in Excel and should be followed with the “$”.
WHERE "Name" = %prop("Item","Layer"); This is where we are going to define what data we are going to bring from the Excel spreadsheet into Navisworks. For our example, we are going to conditional match the value Layer property data for each element that can be found in the Item tab and match that with a value in the Name column of the Excel spreadsheet.
“Name” refers to column that we will be searching in the Excel file.
%prop is a SQL command that allow you to retrieve model data for conditional matching.
("Item","Layer"); “Item” refers to the Item tab in the Properties Window. “Layer” refers to the internal file property, in this example, the Layer (Figure 8).
In the SQL String section of the Edit Link dialog, enter the following string of text.
SELECT * FROM [Gatehouse_Layerinfo$] WHERE "Name" = %prop("Item","Layer");
The next step in the process is to specify what information we are going to display in the Properties window. In the Field section of the Edit Link dialog (Figure 9), we are going to specify what Field names to list and the display name to use. Using Figure 9 as a reference, add the Field and Display Names.
Note that we are having the Field Name “Name” display as “Layer” in Navisworks. The Name column in our Excel spreadsheet refers to an AutoCAD Layer and rather than changing the column name in Excel, we can have Navisworks use a different Display Name.
After you have entering the Field Names, click OK to close the Edit Link dialog box. Make sure that the “Gatehouse Example” DataTools Link that we just created is checked. This will enable it in our current session. Click OK to close the File Options dialog.
When you select an object in Navisworks, note that the Properties Window has a new tab called “Gatehouse”. That is the name of the DataTools link that we created. Figure 11 shows an example where we have selected a wall and in the Gatehouse tab in the Properties window provides us with the Layer, Finish and Note information from the Excel data file.
If you have gotten this far and it is not working, a lot can go wrong. The first thing to check is the SQL String text. Then check to make sure that your using the correct version of Excel, which might be different than the selection, I made in Figure 5. Also, make sure that the Excel file you are trying to access in Navisworks is not open. That can cause issues as well.
This Gatehouse example is not the most robust example around, but it is a great starting point. Start with something simple so you can make sure that your database connection is working, before trying it on a more complicated project with a lot of data to query. As many of you are aware, CAD and BIM applications are not great data entry applications, so having this type of data linking capabilities is very beneficial.
The Process
In order to connect an external data source such as an Excel spreadsheet or an Access database to a Navisworks model, we need to configure a DataTools within Navisworks. We will then be configuring the database connection. When working with the DataTools in Navisworks, there are a few very important rules to keep in mind. If you are using a 32-bit workstation, then you need to utilize the 32-bit ODBC drivers. 64-bit workstations need to utilize 64-bit ODBC drivers. It is also important to note, that the link to Navisworks is one way. Data can be linked and viewed inside of Navisworks, but the data can’t be updated within Navisworks.
My workstations are all Windows 10 64-bit workstations. My PC’s are using Microsoft Office 365. My Mac running Windows 10 64-bit on parallels using Office 365. The MS Access 2013 runtime drivers can be downloaded here: https://www.microsoft.com/en-us/download/confirmation.aspx?id=39358 If you are using an older version of office the MS Access 2010 drivers can be downloaded from here: https://www.microsoft.com/en-us/download/details.aspx?id=13255.
Data Source Setup
The Gatehouse project includes a sample Excel spreadsheet which is why I am using it as an example. The first thing I did to the file was add three column headers to identify the “Name”, “Date” and “Notes” columns (Figure 1). We are creating these labels to identify the columns.
 | |
|
Our next step, you need to have the ODBC drivers installed, otherwise you will not have the data connection option. With that done, open Navisworks and the Navisworks model that you want to connect your external data source to. In this example, I am going to open the gatehouse model as indicated above. In Navisworks, on the Home tab, Project panel, click on the File Options button (Figure 2). This will open the File Options dialog.
 |
| Figure 2 – Accessing the File Options |
In the File Options dialog, we will start the process of configure our database connection (Figure 3). As shown in Figure 1, click the DataTools tab and then click the New button to create a new DataTools link to open the New Link dialog.
 |
| Figure 3 - File Options dialog for creating a new DataTools link |
In the New Link dialog (Figure 4), we first want to name the new link connection we are creating. For this example, I am using the name “Gatehouse Example”. We then need to select the ODBC Driver Connection type. We are going to be working with a Microsoft Excel data source, so select the “Microsoft Excel Driver (*.xls, *.xlsx, )” driver. Then click the Setup button to open the ODBC Microsoft Excel Setup dialog.
 |
| Figure 4 - New Database Connection Link dialog |
After you have selected your excel file, click OK to close the ODBC Microsoft Excel Setup dialog.
If you are having issues with your Database Link connecting to Excel, be sure to check the Version of Excel database that you are using.
 |
| Figure 5 - ODBC Setup Dialog for Excel |
 |
| Figure 6 - Edit Link |
SQL String
The SQL string is used to specify how the database will be queried. I have found this part of configuring the database connection to be the most difficult. Fortunately, there are a lot of external resources available for creating SQL strings. My personal recommendation is to keep it as simple as possible.
The code that we will be creating for this example is:
SELECT * FROM [Gatehouse_Layerinfo$] WHERE "Name" = %prop("Item","Layer");
We start out our SQL String with SELECT * FROM The “*” is actually a field name, in this particular example, I am using the wildcard to query all the fields.
[Gatehouse_Layerinfo$] This is the Excel sheet tab name (Figure 7) that we want to query in our Excel spreadsheet. The Excel sheet tab name must be spelled just as it is in Excel and should be followed with the “$”.
 |
| Figure 7 |
WHERE "Name" = %prop("Item","Layer"); This is where we are going to define what data we are going to bring from the Excel spreadsheet into Navisworks. For our example, we are going to conditional match the value Layer property data for each element that can be found in the Item tab and match that with a value in the Name column of the Excel spreadsheet.
“Name” refers to column that we will be searching in the Excel file.
%prop is a SQL command that allow you to retrieve model data for conditional matching.
("Item","Layer"); “Item” refers to the Item tab in the Properties Window. “Layer” refers to the internal file property, in this example, the Layer (Figure 8).
 |
| Figure 8 – Navisworks Properties Window |
SELECT * FROM [Gatehouse_Layerinfo$] WHERE "Name" = %prop("Item","Layer");
The next step in the process is to specify what information we are going to display in the Properties window. In the Field section of the Edit Link dialog (Figure 9), we are going to specify what Field names to list and the display name to use. Using Figure 9 as a reference, add the Field and Display Names.
Note that we are having the Field Name “Name” display as “Layer” in Navisworks. The Name column in our Excel spreadsheet refers to an AutoCAD Layer and rather than changing the column name in Excel, we can have Navisworks use a different Display Name.
 |
| Figure 9 - Inserting the Field and Display Names |
After you have entering the Field Names, click OK to close the Edit Link dialog box. Make sure that the “Gatehouse Example” DataTools Link that we just created is checked. This will enable it in our current session. Click OK to close the File Options dialog.
 |
| Figure 10 - DataTools |
 |
| Figure 11 - Foundation Selected with External Data Displayed |
If you have gotten this far and it is not working, a lot can go wrong. The first thing to check is the SQL String text. Then check to make sure that your using the correct version of Excel, which might be different than the selection, I made in Figure 5. Also, make sure that the Excel file you are trying to access in Navisworks is not open. That can cause issues as well.
This Gatehouse example is not the most robust example around, but it is a great starting point. Start with something simple so you can make sure that your database connection is working, before trying it on a more complicated project with a lot of data to query. As many of you are aware, CAD and BIM applications are not great data entry applications, so having this type of data linking capabilities is very beneficial.
Comments
Post a Comment