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).
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).
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).
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).
Following the selection above as well as the creation guidelines my template file will look as follows:
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 – 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 – 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).
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).
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).
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 as the strings are bounded with quotes (Pic. 9). For this purpose we need to choose Transform Data and open the Power Query tool.
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).
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.
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 look at the screenshot below (Pic. 11).
Our result should look like this below (Pic. 12)
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.
- Knowledge.autodesk.com: How-to-extract-block-attributes-in-AutoCAD-LT.html
- Knowledge.autodesk.com: ATTEXT command
- Knowledge.autodesk.com: To extract attribute information.
- Knowledge.autodesk.com: About setting up an attribute extraction template file
- (AutoCAD LT) Attribute extraction for BOM – tutorials?
Leave a Reply
You must be logged in to post a comment.