Importing Properties From Excel By Matching Names Without Occurrence Number & File Extensions

kwalker1 — Thu, 26 Nov 2020 06:52:26 GMT

I am trying to run a SQL String that imports data from excel based on matching names without occurrence number & file extensions at the end of the name.

I have a navisworks file with the Selection Tree populated with the Name and occurrence number at the end (eg. FF - FILTER FRAME ASSEMBLY:1).

I also have an excel file which contains data (on a tab called Inventor3DModelData) with a column called Filename but all values includes the file extension (eg. FF - FILTER FRAME ASSEMBLY.iam)

I am trying to import properties into navisworks from excel based on any names that match (without the occurrence numbers or file extensions).

The SQL String that I have been using in the File Options - Data Tools tab is as follows:

SELECT * from [Inventor3DModelData$] where LEFT("Filename",(CHARINDEX(".","Filename")-1)) = LEFT(%prop("Item","Name"),(CHARINDEX(":",%prop("Item","Name"))-1));

But this is not working for me.

I have previously used the following SQL String without issue if I first export a list of Names (with occurrence numbers) from navisworks then add property data next to this list followed by importing the property data back into navisworks using the code.

SELECT * from [Inventor3DModelData$] where "Filename"=%prop("Item","Name");

But to generate this list requires the browser tree to be opened to the correct level and selected for the Selection Inspector. This is very time consuming in very large models.

Hence if I can match the names (without the occurrence numbers or file extensions) in memory when publishing to a nwd file, then this will be very convenient and efficient. Can anyone see why the first SQL String is not working?

Any help is much appreciated.

Cheers.

Kurt.

Re: Importing Properties From Excel By Matching Names Without Occurrence Number & File Extensions

Anonymous — Mon, 30 Nov 2020 20:29:22 GMT

I have the same problem to solve and we thought similar about using SQL LEFT... See my post in this forum

https://forums.autodesk.com/t5/navisworks-forum/data-tools-link-using-sql-left/td-p/9900036

You you solve please let me know please

topic Re: Importing Properties From Excel By Matching Names Without Occurrence Number & File Extensions in Navisworks Forum

Importing Properties From Excel By Matching Names Without Occurrence Number & File Extensions

Re: Importing Properties From Excel By Matching Names Without Occurrence Number & File Extensions