[Search tip detail and code files using keywords, tip number, author name, etc ]
 
Create Spreadsheets from inside AutoCAD
Tip# 3387 By Jesse Moore On 05-Apr-2010
4.5
Rated By 2 users
Categories : Export
Software type : AutoCAD 2010
Rename File To : No Files to download.
Use an attribute extraction command to put data from your drawing into an external file, an AutoCAD table, or both.

Tipster Jesse Moore thoroughly describes how to get your AutoCAD data into a spreadsheet or table in AutoCAD.

"Tired of the basic table in AutoCAD? Wish you could use Excel instead? Not a problem. Wish you could port some information from a block's attributes? It's as easy as eating pancakes! The hardest decision will be to figure out where you want this information to reside. I like to have it in one file, but maybe you would like it spread out in multiple files. Once you decide that, just follow these basic steps. Stay with me — this is a bit long — and please follow along in AutoCAD. Please note that I'm using AutoCAD 2007; the method, means, and results may vary per version of software.
 
"Open your file and run Eattext (yes, it is the attribute extraction command). A setup wizard will pop up. I like to pick 'Create table or external file from scratch' and then click Next. This is where your previous 'hardest decision' will come into play. I'm going to let you do your own thing here, but I prefer to choose 'Select drawing / sheet sets,' as I like all my information for a project to be in one spot. Whatever you pick, don't forget to click on the additional settings and make sure the settings suit your needs.

"Now on to the next page: here you will select the blocks and the respective attributes you want to put in the table. (Hint: click on 'Exclude blocks without attributes' and also 'Exclude general block properties' if that is what suits your needs.) Click on the next page. Now just click and drag the column were you want it, and make sure you checked AutoCAD Table. You can also set up how the table is sorted by double-clicking on a column's heading (an up or down arrow will appear to correspond with ascending and descending). You can export to an external file that Excel can read now, but for this exercise leave that blank, as we are going to do that later. Click Next. Fill out the overall heading of the table and select any table style that you have previously set up. Click Next and then Finish, and place your table into your AutoCAD file. I just like to keep an AutoCAD file because it makes it easy to update and it also keeps a record of what you have done, as I will demonstrate.
 
"Poof — now you have a table. If you forgot a step or you wonder which options you selected, you can easily go back by right-clicking on the table and selecting Update Table > Edit Extraction Settings, and you will go through the setup wizard with all of your settings. Just be careful: if you change anything, the pages after may not fully reflect your original settings. You can also just update the table by right-clicking on the table and choosing Update Table > Refresh Only.

"If you modify your table (e.g., add formulas, rows, or columns) in AutoCAD, please be careful. Any changes will be deleted once you refresh or change settings. (Basically, it deletes the table and recreates it from scratch every time you refresh or change the settings.) I think it's best to port out that information to Excel. Again, right-click on the table and choose Export. Save the file in a desired location. For me, this exported file is trash, because I have another refined Excel file that I have set up with formulas and appearance. I will open up both the trash Excel file and the refined Excel file, then simply use Windows Copy (Ctrl+C) and Windows Paste (Ctrl+V) to move the attribute information from the trash Excel file to the refined Excel file. Just be careful to paste the information far to the right so that you don't overwrite any refined formulas or appearance, and then … make it so.
 
"Once you have cleaned up the Excel file, it is an easy process to pull it into AutoCAD. Just use Windows Copy and Paste again. You may get a warning from Excel about security and linking info. You can enable the link or disable; I like to disable just because I'm crazy. Pick a location for your table, then change the OLE text size as needed and click OK. Be sure to draw some lines on defpoints to mark where your upper left and right corners are, as you can have troubles in the future with the size of this object if you try to change it. Now just slap the table on a page using viewports, and then you can cut up the table as needed to fit onto the page(s)."
 
 
Notes from Cadalyst Tip Patrol: Wow! Did you get all that? The steps needed are actually easier than they look, and the tool is very powerful. The directions given are for AutoCAD 2007. In AutoCAD 2008, we were given a new command called DataExtraction. The Eattext command now starts the DataExtraction command. It simplifies the process, but does essentially the same thing. It takes data from your drawing and puts it in an external file (like a CSV or XLS file, for example), in an AutoCAD table, or both. If both, the external file is linked to the table so that when one of them is edited, the other will be updated. You don't have to use the table, just export the information. I have used this command to create a color list of my objects, or to create x,y,z files of objects to use elsewhere. The DataExtraction tool can get any bit of information that exists in your file.
 

 

Average Rating:
4.5


User comments
Comment by Mitchell,Liesl
Posted on 2010-10-29 15:27:30
Does this work for AutoCAD 2011? I get stuck at the second step - 'Create table or external file from scratch' . It's looking for a .dxe file, which I don't have or don't know how to create. Having said that, is there another way 2011 Civil 3D to extract data. All I want is x,y coordinates from some blocks!
Comment by Mitchell,Liesl
Posted on 2010-10-29 15:27:32
Does this work for AutoCAD 2011? I get stuck at the second step - 'Create table or external file from scratch' . It's looking for a .dxe file, which I don't have or don't know how to create. Having said that, is there another way 2011 Civil 3D to extract data. All I want is x,y coordinates from some blocks!
Comment by Mitchell,Liesl
Posted on 2010-10-29 15:28:14
Does this work for AutoCAD 2011? I get stuck at the second step - 'Create table or external file from scratch' . It's looking for a .dxe file, which I don't have or don't know how to create. Having said that, is there another way 2011 Civil 3D to extract data. All I want is x,y coordinates from some blocks!
Comment by Mitchell,Liesl
Posted on 2010-10-29 15:28:18
Does this work for AutoCAD 2011? I get stuck at the second step - 'Create table or external file from scratch' . It's looking for a .dxe file, which I don't have or don't know how to create. Having said that, is there another way 2011 Civil 3D to extract data. All I want is x,y coordinates from some blocks!