CAD Data to Excel for Asset Management

In the previous post  How to show CAD drawings on a Web Page I talked about posting CAD drawings to a web page for access to the information.

In this post I want to talk about having Data in the CAD file that you can extract to Excel.

In DraftSight you can create attribute blocks with information in them as per screenshot below.

After creating the block you need to save it out of the drawing so that it can be used elsewhere, in Acad this is a WBlock, in DraftSight you save the block as a drawing.( I am not doing a lesson on this but if there is interest I may do one later).

After creating the block you then insert it into the drawing of your choice. You can edit as you put in the block or add one feature (eg Room Number) and then hit Enter until the dialogue for the block stops. If you double click on the block after inserting you get the pop-up box (on the left of image above) and you can edit attributes there.

A little aside

When I was looking to extract data from a PDF there was someone who wanted to show oil rigs, and then attribute data about those rigs. They wanted a simple clean PDF and then extra data. I thought at the time, just make 2 pdf’s, one with the simple data and the other with the other information that you wanted to show. You then had the first map to find your rig, and after finding it, you’d refer to the 2nd map for more in depth data.

So, for Attributes you can show the information or have it hidden. So for a Room Tag, you may just show the room number and name, all the rest of the data (Floor SS, Floor Fin etc) would be too disruptive on the drawing, so you would make all that other data hidden within the attribute.

An alternative method is to put that other information on another layer & turn that layer off on the main drawing, but have a separate VIEW showing the other data. I have demonstrated this below.
Go to the Model view on the right hand side and choose Data view, this shows the data about the room and the furniture is hidden, making the data clearer. The other view 01 shows furniture (have not turned off Data layer as I was having issues with viewports and layer info)

Note. The Room_Tag blocks and text size are a little small, I wanted to get the intent across. I find I can spend hours doing the fiddly bits like text sizing and which layers ON/OFF in specific views.

Download Drawing

Extracting Data to Excel in DraftSight

Once you have put the blocks into the drawing in DraftSight and filled in the attributes (in this case manually- more about that later) you can export from the programme to a text file.

There are 2 export attribute data commands in DraftSight. EXTRACTBLOCKATTRIBUTE and BLOCKATTRIBUTEOUTPUT.

The first asks you for a template file, the 2nd doesn’t, it asks you for a file name and creates a .TXT file. 

Another little Aside

Just another link to some tutorials on DraftSight, for Blocks, go HERE.   This website is a good DraftSight resource.

Another feature that is quite interesting in DraftSight is Tables. There is an article about exporting Tables HERE, unfortunately this is for DraftSight Pro. So instead of putting in Blocks and attributes, you could put your data into Tables and export them, but that’s another process and you’d need the Pro version for export.

Exported Data

So we export a file with attribute data to a TEXT FILE

Next we open Excel and open that TXT file

And this is what we get below:

Note , the data in the green box is the attribute data from the block Room_Tag

So all that data is ordered in the same way for each specific room it pertains to.

The HANDLE is the specific INSTANCE of that Block Room_Tag

Export & Import issues with free & paid versions

Now, for DraftSight Free you can Export Attribute Data, but you cannot import it from a text file.

A common way to populate attribute data in a model is to Export it to Excel (Usually having only filling in a single item in the block (say room number) so you know which block is which, then use excel to fill all the other information in. Then you would IMPORT that data back into the Drawing. I think you can do this with the paid version of DraftSight Pro.(145$US for annual subscription or $299$US for full license) or you could use the BricsCAD classic or AutoCAD full ( not sure if AutoCAD LT lets you import attribute data).  As you can see, if you have the Block HANDLE, it knows where to put the data.

So if you were doing As-Build Drawings with a few attributes, doing it manually in DraftSight is fine, but if you had a lot you’d use a paid version for productivity.

Validation

This data is NOT validated, so if inaccuracies or rubbish data then you have to think how you can check to see if its in the Meta Data format that you require.

Open Spatial have a product called ACDC that can validate your data by pushing the drawing up into the cloud where it is validated against a pre-set register and it sends the drawing back with the errors that need to be rectified before it will accept the drawing. This is opened in a 3D CivilCad package which has a database on it that it compares the information in the attributes with items in the database.

FME have a tool that reads DWG files that can then make an analytic process that it extracts the data and tests it against a pre-set register, I think Christchurch City Council is using this process for its 3 Waters & Roading infrastructure. I’m not sure of their feedback loop to contractors when errors are identified. This tool has a DWG reader.

If you look at a DWG file, you will see its compiled, so you cannot, with a normal text editor, search and find a block attribute by normal name. Which is a pain. So any validation you need to do needs to be done in the text/Excel file.

An example of this is for wall lining, you put GIB, but the database calls this GIBBOARD LINING. As the 2 names are different, the database may not accept this, as it can mess up your search or severely slow it down. You want your database to be as consistent as possible so that it works fast.

You could do an Excel Macro to analyse this, or you could set up a KNIME analytic process to compare, and if certain words were used instead, then it could replace them with the correct data, so if it saw  GIB it would replace with GIBBOARD LINING. This method can pick up the obvious errors but the bigger errors will need to be sent back to the contractor in an Excel/Text format with a Request to Fix before resubmitting.

Personally I find Excel Macros more temperamental than the KNIME processes, and when set-up the Knime processes can be tested quite thoroughly.

Some thoughts

This process is not difficult to setup with making standard blocks for distribution and having a consistent process.

This process requires more manual input than the BIM process. I would recommend a more elaborate version of CAD so that you can use the leverage of Excel to IMPORT attribute data back into the CAD file.

The validation process needs to be setup to ensure that the data is as clean as possible before going into the database.

It is an effective tool to use now as many companies are still using CAD.

I will explore if there are 2 way translators from DWG to say TXT (or maybe plot file to DWG?) maybe that is another process for the validation?