There’s a spreadsheet built into your drawings.
We use AutoCAD Tables to organize and present data in a structured format. Think of them as spreadsheets for your drawings. Use tables to show things like part lists and schedules. And because they are spreadsheet-like, you can include various data types such as text, numbers, dates and formulas.
This is the second in a series of articles on using tables in AutoCAD. Check out the first part How to Use Tables in AutoCAD.
Data extraction allows you to gather information from the objects within your drawings. Although not dependent on tables, this method allows you to extract the information and compile it into a structured format. If you need to share the extracted information with others, you can save it in an external file.
You can create material lists, quantity takeoffs and schedules. This is based on the information stored in your drawings. And it updates as the objects in your drawings change.
Getting Started
Start by opening the drawing where you want to insert the table. Start the Table command. In the Insert Table dialog, set the Insert Options to ”From object data in the drawing (Data Extraction).”
Data Extraction is wizard driven. Here is a quick summary of the steps:
- Select the data sources, which can be a drawing, multiple drawings or a folder of drawings
- Select the object types to extract from and properties to extract
- Organize and refine the data
- Choose the output
- Set the table style
Step 1: Begin
The first step of the extraction wizard is selecting whether to create a new data extraction or use an existing one. Think of data extractions as templates. Instead of configuring the extraction each time, you can use existing extractions for repeat instances. It retains the settings (data source, object types, etc.) to recreate the extraction. Tables referencing the data extraction (DXE) update as you make changes.
The file format is DXE. You can also use the DXE as a template to perform the same extraction in different drawings. Using a DXE file saves time and is easier.
When creating a new extraction, you can start from scratch or use an existing extraction as the starting point for the new extraction. With new extractions, AutoCAD prompts you for the name and file location when you click Next.
To use an existing extraction, select Edit an existing data extraction. Then browse for and select the DXE. After loading the DXE, you can make changes, much like when adding or removing drawings or changing the object types.
Step 2: Define Data Source
The extraction source is either a set of drawings or objects from a current drawing.
When using the Select Objects option, pick the Select button, which minimizes the dialog, enabling you to select objects from the current drawing.
When using Drawings/Sheet, AutoCAD defaults to including the current drawing. Use Add Drawings to include more drawings. Use Remove to remove the selected drawing (or folder) from the extraction.
Use Add Folder to add folders of drawings, which AutoCAD scans during the extraction. The Add Folder options allow you to have AutoCAD automatically include new drawings to the folder, include the folder’s subfolders, and apply wild cards to limit the drawings included in the scan.
With the folders added, use the right-click menu to access the Folder Settings and to quickly check or uncheck the included drawings.
Use Settings to configure the inclusion of block and XREF information.
Step 3: Select Objects
{DXE-03-Select-Objects.gif}
After scanning the drawings, AutoCAD lists the available objects from which you can extract data. By default, it only lists the objects in use in the drawings.
Use the Display Options to filter the objects. This includes listing only blocks or only non-blocks, displaying only blocks with attributes, and showing all object types (even those not in use).
Using the Objects list, select (check) the type of objects to include with the extraction.
You can change the Display Name to better identify what the object represents. For example, you can rename circles as Holes when the circles in the drawing represents holes.
This also works as a method of grouping different object types. For example, you can rename both circles and ellipses as holes.
Step 4: Select Properties
Next in the process is selecting the attributes (properties). The list is dependent on the object types selected in Step 3. Use the “right-click menu options“ to quickly check or uncheck all.
As with object types, the Display Name is editable so that you can set it to something meaningful.
In this example, only the “Circle object type” is selected. From the Select Properties page, only the center coordinates and diameter are selected and then renamed to build a hole table.
Step 5: Refine Data
AutoCAD now previews the extraction. Click the column header to sort the data ascending/descending by that column. Drag and drop columns to adjust the order of the data. If you need to sort by multiple columns, use the Sort Column options.
The Count and Name columns display by default. The Name column is not always useful, especially when only a limited number of object types are in use. The Count column is only useful when there are multiple instances and a count of the objects is what you want. Both can be turned off with the respective Show checkbox.
Enable (check) Combine Identical Rows when you want to group identical entries. The Count column becomes very useful when you are combining rows.
Right-clicking a column header displays a shortcut menu.
You will find many of the refine data options only in this menu.
- Hide Columns, Show Hidden Columns, and Show All Hidden Columns to manage column display
- Access to the Set Column Data Format dialog for setting the data format for the cells in the column; options include Angle, Currency, Dates, Decimals, Percentages, and Point
- Combine Record Mode to manage how AutoCAD combines identical property rows: Separate or a Sum of values
- Use the Insert Totals Footer options to add a footer row for the selected column; intended for numeric dataset the arithmetic function to Sum, Max, Min, or the Average of the column values
- Copy to clipboard to copy the data, including column names, to the Windows Clipboard
Data extraction can create a lot of data—more than you need in many instances. Use the Filter Options to refine the data to the desired ranges. Do this with greater-than, less-than and equal-to type functions and/or specifically when selecting the values to include.
In this example, I only want to include hole diameters greater than 11. Note the Hole diameter column is formatted for architectural units, but the filter dialog shows the raw values. An icon appears on the column header to show an applied filter.
You can quickly remove filters with the right-click Reset Filter and Reset All Filters menu options.
Formula Columns provide spreadsheet-like abilities to perform calculations on the extracted data. These columns update in real time based on the data they reference.
The Insert Formula Column option inserts the new formula column to the right of the selected column and displays the dialog.
An example of usage is creating a table with quantities and prices. The formula column calculates the total cost and if the quantities change, the total cost updates to match these. Below we apply a price per diameter to calculate the cost of drilling the holes.
Step 6: Output
In Step 6 of the wizard, you decide where the extracted data will go.
Inserting Data Extraction Table into Drawing creates a table in the active drawing that is populated with the extracted data.
The data can also be exported to an external file. Select the Output Data to an External File option and set the data type, file name and file location. You can export to Microsoft Excel (XLS), Microsoft Access (MDB), Comma-Separated (CSV) and Tab-Separated (TXT).
Step 7: Table Style
Step 7, setting the table style, only appears when you insert the extracted data into the drawing as a table. Table Styles manages the appearance of the tables in your drawings. From this page, you can create a new style, use an existing, or create an existing one.
When choosing to create a table, the wizard includes the Table Style page step. You can create new or use an existing table style. Then set the title and optionally choose the formatting for the title, header and data cells style.
Check Use Property Names as Additional Column Headers when you want to insert the Display Names as header titles. When this option is unchecked, no header row will be included.
Step 8: Finish
The last step provides your last chance to go back and adjust the extraction configuration. When you are satisfied, select Finish to insert the table and/or export the extracted data.
AutoCAD inserts the extracted data between the title, header and footer rows (if they exist). Bold lines around the cells show the extracted data boundary.
With data extraction tables, the tooltip displays information about the data extraction.
So, What Happens When the Drawing Changes?
When the data sources change, AutoCAD lets you know that the data is no longer current. Click the link in the tooltip to update the extracted data.
The DXEVAL system variable manages which commands trigger the data check and notification. The value is 12 by default, meaning when you are plotting or publishing a drawing. Adjust this to expand the notification to Open, Save and eTransmit. You can also use DXEVAL to both check and automatically update the links.
To manually update the links, right-click on the table and select Update Table Data Links. Alternatively, with a cell selected, you can pick Update Data Extraction.
Making Changes
You can alter the table by changing the way it looks, adding rows and columns, as well as changing the information in the cells. When updating the table, AutoCAD retains any changes made to the formatting, structure or data.
Adding columns and rows to a data extraction table does not change the extracted data. When picking the table, bold lines highlight the extracted data cells. You can only add columns and rows outside this perimeter. Note that blank cells may appear during an update when you extract fewer columns or rows of data.
By default, AutoCAD locks the extracted data cells to prevent accidental changes. But these cells are unlocked (and available) for formatting changes. By using options in the right-click shortcut menu, you can unlock or lock the cells, allowing or preventing changes to their data and formatting.
To edit the data extraction, follow steps that are similar to what is used when creating a new data extraction.
You can access the wizard directly, without needing to insert a new table, by selecting Extract Data from the Annotate tab. Or for you keyboard warriors types, use “DATAEXTRACTION.” The difference between this and creating a new table is that you want to select Edit an existing data extraction. Then browse for and select the DXE you originally created.
The advantage of this method is that you can access the DXE from any drawing, even if it does not contain the data extracted table.
An even quicker method is to select a cell within the table, right-click, and from the Data Links submenu, select Edit Data Extraction Settings. This skips the browsing for the DXE, taking you directly to the point of selecting the data source.
To break the link and create a static table, use Detach Data Link from the Data Links menu.
In Conclusion
Data extraction gathers and manages information from various objects within your drawings. It is especially valuable for creating schedules, material lists, quantity takeoffs and other data-driven tables. Compared to manually collecting the information, it saves time and reduces the risk of errors. As this approach is template and style driven, it ensures consistency and accuracy in data collection.
