Extraction of item properties from AutoCAD LT to Excel

by Krukarius · 2022-09-29

AutoCAD LT version is quite poor when compared to the full AutoCAD 3D software. It means in practice, that some options aren’t available or require a bigger workaround in order to get to the desired point. One of the issues is the lack of a direct option of exporting attribute data from our drawing directly to Excel, or at least the Comma Separated Value format. This article shows how to reach this stage quite quickly by using the ATTEXT command customized template file and smart additional approach.
Our example will be simple, imagine that you need to extract the details of some premises, which act as the points (or point-looking polygons) in AutoCAD (Pic. 1).

Pic. 1 Point layer block in AutoCAD LT with one item selected. The sample properties have been presented.

We will use some of the properties for the extraction. When you want to alter something before extraction, just go Main Bar -> Block -> Edit Attributes as shown below (Pic. 2).

Pic. 2 AutoCAD LT – Enhanced attribute editor.

The most important here is to match the properties tags with the template, which is needed for the data extraction. When assured, that everything is alright, you can type ATTEXT in the command bar.

The ATTEXT command launches the Attribute Extraction panel, in which we must define the file format as well as the template and output file. Because we need to have our data in Excel, we should go with the very first option, which is Comma Delimited File (CDF). We can also specify the target directory and name of our output file now (Pic. 3).

Pic. 3 Attribute extraction panel in AutoCAD LT.

Pic. 4 Attribute extraction panel in AutoCAD LT with some points selected.

Surely the biggest conundrum is defining the template file, which must be in the .txt format. This is just the format, but the file must be “prepared” for the extraction of specific data from AutoCAD LT. The way of this preparation has been roughly explained in this link, although because this stuff is too general I will base it on my example.
Below I’ve listed the things, which I am going to extract to the text file and further to Excel (Pic. 5).

Pic. 5 Properties estimated to be extracted by ATTXT command. Worth to know the most important columns, which are Tag and Value. The Prompt one is presumed to be the same as the Tag column.

Following the selection above as well as the creation guidelines my template file will look as follows:

BL:NAME C010000
BL – block, NAME – the name of our block C010000 – the length of up to 10 characters with no decimals (C – a symbol for the character, 010 – the number of character field, 000 – the total number of decimals, where 000 then no decimals)

UPRN N012000
UPRN – the property tag, N010000 – the length of 10 numbers with no decimals (N – a symbol for number, 010 – the total number field, 000 – number of decimals, where 000 then no decimals)

ADMIN_AREA C030000
ADMIN_AREA – another property tag, C030000 – the estimated length of up to 30 characters with no decimals (C – the symbol for the character, 030 – the number of character field, 000 the total number of decimals, where 000 then no decimals).

… and so on.

Defining the length of 30 characters (C030) we always can include a shorter name, but it can’t exceed 30 characters.
If you want to execute this data in some specified order, here is the place for doing this. The text file will replicate everything roughly along the order defined in the template file. Just one space is required between the Tag and Value columns.

My final template file looks like this:

BL:NAME C010000
UPRN N012000
ADMIN_AREA C030000
TOWN C030000
STREET C030000
NAME_NUMBER N003000
POSTCODE C009000

and now I can attach it to my Attribute Extraction panel and run it by hitting the OK button. Make sure, that everything has come well by looking at the statement just above the command line. Since the value is other than 0 and matches pretty much our selection, then we can guess that the data has been extracted successfully (Pic. 6).

Pic. 6 Successful attribute extraction in AutoCAD LT.

The template file refers to the specific part of the data in one layer or block. Even if other items will fall inside the selection they won’t be executed simply because of the mismatch between their properties tags and tags provided in the template file. Having “0 records in extract file” means, that the operation was in fact successful, but with no records provided. Double-check the template file against the property tags and try again.
Eventually, your text file should look like this below (Pic. 7).

Pic. 7 Our data was exported from AutoCAD LT to the text file.

We have all the properties considered earlier. The restriction of AutoCAD LT doesn’t allow us to go beyond this point. The CSV export in reality provides the Comma Separated Value data saved just in the .txt file. Because this sort of data is already prepared, in other words, it matches the Comma Separate Value standard it shouldn’t be any issue with its further conversion to the .csv format.
Reaching this point, we can forget about AutoCAD LT and take care of it on our own by utilizing the solution explained below.
The first way is just to launch the Excel Power Query editor by using the Main toolbar -> Data -> Get Data -> From Text/CSV (Pic. 8).

Pic. 8 Launching .txt file by Excel Power Query.

Pic. 9 Loading data by Excel power query – preview.

When the Power Query tool shows us the data preview, we can load it straight to Excel and then save it as a .csv file. However, I doubt you will be satisfied enough, as you have no column titles as well and the strings are bounded with quotes (Pic. 9). For this purpose we need to choose Transform Data and open the Power Query tool.

Pic. 10 Renaming columns in Power Query tool. All the changes can be seen in the “APPLIED STEPS” sidebar on the right.

After changing the names of all the columns, we can get rid of these quotes enclosing all string values. It can be done very quickly by selecting all considered columns by holding Ctrl + mouse click. Any selected column is highlighted in green. Next, we can right-click on any column and choose to Replace values. Finally, all the quotes will disappear quickly (Pic. 11).

Pic. 9 Quick transformation of our data – removing unwanted quotes in string values.

When everything is fine, the very last step is choosing the Close & Load option in the top-left corner just below the File (Pic. 9,3).
Our data will be loaded in Excel shortly thereafter (Pic. 10) which will lose all the styling when saved as the .csv file.

Pic. 10 Our Power Query product in Excel is ready for saving in the .csv format or other.

Another way of doing it is quicker, as it can be executed by Python coding. We can prepare a smallish tool adjusting the names of our columns and by simply double-clicking on the .py file convert our data to the .csv file and sanitize it at once.

import csv

with open('Premises data.txt', 'r') as in_file:
    lines = [line.strip().replace("'","") for line in in_file]
    with open('Premises data.csv', 'w', newline='') as out_file:
        writer = csv.writer(out_file)
        writer.writerow(('Name', 'UPRN','ADMIN_AREA','TOWN','STREET','NAME_NUMBER','POSTCODE'))
        for line in lines:
        writer.writerow(line.split(","))

Make sure, that you have all the indentations required and everything falls in line correctly. Because this formatting code might be slightly wrapped, take a look at the screenshot below (Pic. 11).

Pic. 11 Python code converting the text data extracted from AutoCAD LT to Excel with some sanitization applied.

Our result should look like this below (Pic. 12)

Pic 12 The .csv output with quotes removed (sanitized) converted from the text file by the aforementioned Python code.

As you can see, even if AutoCAD LT is unable to extract the properties data straight to Excel, the further approaches allow you to do so relatively quickly. Apart from the programming way, which is far quicker, when you are not called enough with coding, the Power Query option seems to be quite easy and elegant.

Mariusz Krukar

Links:

Forums:

Youtube:

Extraction of item properties from AutoCAD LT to Excel

You may also like...

How to make an isochrone map in Google MyMaps quickly?

The quickest way of making the speed limit map

Breaking down the Street View permalink

Categories