Following on from CAD Data to Excel for Asset Management where I created a BLOCK with ATTRIBUTES and exported that information from a CAD file to a TXT file which was then viewed in Excel.
I altered the CASE on a couple of items and deleted one cell so its blank.
I then looked at using a KNIME process to try and validate the Substrate & Finishes strings in the text file, looking to do this outside of the Drawing Environment (as we cannot push the text file data back into the Drawing with the Free Version of DraftSight (But you can with most of the paid CAD programmes))
In the process above:
1- We read the text file
2- We have a Library File (see image below)
3-which we use to compare a column against a list of “strings” (words), if the string is in the first column, it is replaced with the string in the 2nd column.
4. We then chop off all the other columns
5- We then join all the columns together in the same order as the original
6-We then export to an Excel File (which in turn can be saved as a text file
The resulting file looks like this:
You see that the correct referencing has been included for all the elements (note, there is an error, line 6 for Ceilingfin where the PAINT has been substituted by BLANK. This would need to be checked to see why this occured, all part of the validation checking procedure)
After the last post, I felt I needed to check to see if I could set up a validation process on the data.
This was a little BURR that was bothering me. Simon from RTV Tools set up a validation process in the RTV Reporter Pro tool that he built for BIM, so I wanted to just see if I could do a simple validation check on CAD Attribute Data.
This is a very quick/crude example to show how you can search/replace poor quality “STRINGS” with clean data with consistent terminology for the Database for the Asset Management Information System (AMIS).
Obviously there is missing data, this sheet can be sent back to contractor with REQUEST TO FIX and then it can be processed again.
That was one process. A run through to check that the “Naming Convention” was correct, and if not, can be corrected if the terminology is within the library file.
Next, there could be another pass over the data looking at “*” where data has not been included. These could be highlighted.
Next there could be a check on numbers to see that they were roughly of the order of magnitude that they need to be , ie” >0″ and “<“less than total area of space (getting Global Block with rough overall space dimensions such as total Area, Height , max length & max width can give you simple figures to compare to.
All you need to do is plug the output of one process and make it the input of the next process.
I’m not sure if Knime can colour code output, but you could always do a macro in Excel for Conditional formatting to highlight Empty Cells & others with “placemarkers” (Such as “*”, or “x”) and colour them a strong colour to highlight that that particular cell needs to be checked.
So a 2 stage process to check the data would most probably meet most needs, using KNIME & Excel Macros. (Or pay lots of money & get a copy of FME).
The nice thing with KNIME is you set up the process and you can distribute a particular process as a file to others, so that contractors can validate their own work. You would still run the validation in-house as a Quality Assurance (QA) check.
After the data is cleaned, you’d plug it back into the Drawing so that the Attributes were up to date, then use that drawing as the source of data capture for that asset, and that would then be distributed to designers/contractors when there was refurbishment work to be done, so they could update the file to keep the data relevant.
Here are links to files:
- If you go to THIS POST you can download the CAD file with the BLOCK inserted.
- If you go to THIS POST you will see download for DraftSight to get the CAD programme for free.
If you just want to explore the output, here are the files:
- The output file (with a few text changes) from DraftSight- Main input file for Knime Validate Process. BARDATA.txt
- The Compare Library File , for comparing the column entry’s against the selected naming convention. FloorSSCompare.txt
- The output Excel File where the validate process exports the data to. Out.xlsx
- The Knime workflow, you will have to download Knime, install it and then upload this workflow. ValidateCAD.knwf
- You can download Knime for Free from HERE.