Linking Excel Data Into Navisworks – Part 3 Editing Data in Excel

Back in Part 2 we were able to export data from the items in our Navisworks model, with the critical step of adding a key to each item, a unique identifier that we can use to round-trip that information back into Navisworks.

In this post we are going to open up the exported .CSV file, add some more data, and make some tweaks that will make our lives easier when we pull this back into Navisworks.

The first step is easy: track down the .CSV file I exported and open it up in Excel. Depending on how your Excel is installed, you can probably just double-click on it to open it up. Once it’s open a quick glance tells me that yes indeed, this is the data I selected to export. Everything is working as expected.

A closer glance usually adds a touch of confusion. See that first row? What essentially are the column headers? When I first open this file up, all I see are “Element”, “Item”, “Element”, “Item”, and “Item”. Not very useful to let me know what data is what. But, if I go in and stretch the height of that row, I can now see what’s going on: when the data was exported, Navisworks made the headers the Category and Property for easy reference, but it put a line break in between them, making them sitting on top of each other.

Top row looks weird
Top row looks weird
Top row looks much better
Top row looks much better

This is the first thing I like to clean up in my Excel file. The line break is not good for data integrity and it can make things confusing when I’m trying to get this data back into my Navisworks model. So I go through and rename the headers, simplifying the name and definitely getting rid of the line breaks. I usually get rid of spaces as well, this also makes the importing of the data simpler. The formatting won’t be read over, so I like to make that top row bold, just for a visual cue that it’s column headings.

New names, no spaces, no line breaks
New names, no spaces, no line breaks

This is where I start adding my new data. It does’t matter where I put this column, it does matter what I name it. All these columns need to be named something unique. I also follow the no spaces, no breaks naming convention I laid out before. Keeps things simpler later on down the road.

In our example, we are adding hardware information and keyset info to the doors. So I just add two columns, give them good names and then add my data. I can use any of the Excel functions I need to, I can sort by DoorNum if I want, all I need to make sure is that the top row stays at the top, and the data in the row itself stays intact.

New data added
New data added

I have had weird things happen with formatting and data types when I bring the new data back into Navisworks, so I will typically just set everything to Text, not General in Excel. That seems to make whatever I write in Excel show up just like it in Navisworks.

Now it’s all good, I do a Save As to save this file as a native Excel format file. You can reconnect with the original .CSV format, but I usually have done something (like making text bold) that the .CSV file doesn’t like, so it’s Excel for me.

And that’s pretty much it. This step is probably the easiest. I do like to emphasize cleaning up those column header names; that will make things a lot easier in the next step.

Speaking of which… now that we have our extra data added in the same row as our elements with the unique identifier, the next post will cover linking the Excel sheet back into Navisworks and getting the data to show up.

Advertisements