Introduction

You can extract information from a set of entities using the Data Extraction wizard.

This topic discusses:

• About Data Extraction
• About Linking Microsoft Excel Files
• Using Data Extraction Wizard

About Data Extraction

You can extract information from a set of entities to create bill of materials, schedules, tables of quantities.

The Data Extraction wizard lets you specify a set of entities or blocks and extract properties and attributes information. For example, you can specify a set of bolts from a project and extract the attribute data.

When you create a new data extraction, the software prompts you to save the corresponding settings in a Data Extraction File (.det). The Data Extraction File stores the data source selections, entities and property selection and table formatting. You can use as template a previously created data extraction file that suits your requirements. Using data extraction files saves time because you can reuse the settings of an already existing data extraction.

A Data Extraction File (.det) lets you extract drawing data with the same properties from multiple drawings. You can use as template any previously created data extraction file to perform the same type of extraction for another drawing. Using data extraction files saves time because you can reuse the settings of an already existing data extraction.

Additionally, you can use data extraction files as basis for new data extractions. You can modify the set of drawings, add or remove entities, or specify different properties from which to extract data.

You can export data to the following outputs:

• Table on the current drawing
• External file (.csv or .txt)

About Linking Microsoft Excel Files

You can combine information from an entire Microsoft Excel spreadsheet or only a specified range with data extracted from drawings. For example, you can extract beam lengths from a drawing and then combine them with cost information from an external spreadsheet.

The workflow for including data from an existing Microsoft Excel file into the data extraction table includes the following steps:

• Specify a data link
• Match the data extracted from the drawing with the data from the spreadsheet
• Select the columns from the spreadsheet to merge with extracted drawing data

Using External Spreadsheets with the Data Extraction Wizard

The Data Extraction wizard lets you specify a set of entities or blocks and extract properties and attributes information that you will use later to create bill of materials, schedules, tables of quantities.

The Refine Data page of the Wizard provides access to the Link External Data dialog box where you can set up a link between the extracted data from the drawing and an external spreadsheet. To merge the two sets of data, it is necessary to specify a data matching column in the external spreadsheet to match a column from the data extraction table.

The data matching column creates a relationship between the data extracted from the drawing and the data from the Excel spreadsheet. The data matching column is valid if there is at least one cell matching between the data in the drawing data column and the external data column.

If data match validation process fails, an error message indicates what the problem is.

• No match was found between any values in the drawing data and external data
• The data column in the external data does not contain unique values

To link Data in a Data Extraction Table to an External Spreadsheet:

1. On the Refine Data page of the Data Extraction wizard, click Link External Data.
2. In the Link External Data dialog box, do the following:
   1. Under Data link, specify an existing data link or click Open Data Link Manager to create a new one. See Creating and Managing Data Links.
   2. Under Data Matching, specify the columns to link.
   3. Click Match.
   4. From the Select External Data Columns to Include, select the columns to add to the extracted data.
   5. Optionally, select Use top row of external data as column names to use the top row of the Excel spreadsheet as column names

Using the Data Extraction Wizard

The Data Extraction wizard lets you specify a set of entities or blocks and extract the attribute data. Additionally, you can extract property data from entities.

You can choose to display the result in a table on the drawing or in an external file.

Using the Data Extraction wizard you can do the following:

• Specify the set of drawings or folders
• Filter entities
• Organize the extracted data
• Add information from a Microsoft Excel file to the extracted data
• Specify an output format
• Format the output data in a table

The ExtractData command opens the Data Extraction wizard.

To create a data extraction table:

1. Do one of the following:
   • On the menu, click Insert > Data Extraction.
   • On the BIM Navigator palette, click Extract Data.
   • Type ExtractData.
2. Type ExtractData at the command prompt.
3. On the first page, do the following:
   • Click Create a new data extraction.
   • To use a template, select Use previous extraction as a template.
   • Click Next.
4. In the dialog box, type the name of the .det file and click Save.
5. On the Define data Source page, specify the drawings and folders from which to extract data and click Next.
6. On the Select Entities page, specify the entities from which to extract data and click Next.
7. On the Select Properties page, specify the properties to extract.
8. On the Refine Data page, change the column order if necessary and include columns from a Microsoft Excel (XLSX or CSV) file using the Link External Data dialog box.

   You can preview the result. Click Next.

9. On the Choose Output page, specify the output type.
10. On the Table Style page, specify the format of the data extraction table. Optionally, specify a title for the table. Click Next.
11. On the Finish page, click Finish.

    The table appears attached to the mouse cursor.

12. In the graphics area, specify a point to place the table.

Note: Use the ExportTable command to export tables in a comma separated format (CSV).

To edit an existing data extraction:

1. Do one of the following:
   • On the menu, click Insert > Data Extraction.
   • Type ExtractData.
2. Type ExtractData at the command prompt.
3. On the first page, click Edit an existing data extraction.
4. If there are new drawings in the specified folder, the New Drawings Found dialog box appears. Specify the drawings to include in the specified data extraction and click OK.

Access

Menu: Insert > Data Extraction

Command: ExtractData

Tool Palettes: Modify > Data Extraction

Defining the Data Source for Data Extraction

The Define Data Source page of the Data Extraction wizard lets you extract data from individual entities, all entities from a drawing, a set of drawings, or all drawings from a specified folder with or without subfolders.

You can do the following:

• Extract data from all entities of one or more drawings. The Data Extraction wizard takes into account all entities from that drawing, including hidden entities.
• Extract data from specific entities based on a selection set
• Extract data from all drawings of a specified folder
• Filter the output using additional settings from the Data Extraction wizard. For example, you can include or exclude entities from blocks and external references.

As you work on your project, you drawings might modify or more drawings may be added to the project folders. You can choose to receive a notification that the data was changed or to automatically include new drawings in the data extraction.

To specify a set of entities from the current drawing:

1. On the menu, click Insert > Data Extraction (or type ExtractData).
2. In the Data Extraction wizard, click Next repeatedly until you reach the Define Data Source page.
3. Specify Select entities in the current drawing.
4. Click Specify entities , specify the entities in the graphics area and press Enter to complete the selection.

   The dialog box closes temporarily until you select entities from which to extract data.

5. Click Next.

To specify drawings and folders for the data extraction:

1. On the menu, click Insert > Data Extraction (or type ExtractData).
2. In the Data Extraction wizard, click Next repeatedly until you reach the Define Data Source page.
3. Specify Drawings.
4. Optionally, select Include current drawing.

   The current drawing file appears in the Drawing files and folders list.

5. Under Drawing files and folders, use the following options to add additional drawing files, add complete folders, and remove files from the list.
   • Add Folder. Adds a folder to the list. You can specify all drawings from the folder or only a subset.

     In the Add Folder Options dialog box, specify options:

     • To specify a folder, click Browse. Navigate to the folder to include and click Open.
     • Optionally, specify the settings:
       • Automatically include new drawings. Takes into account new drawings for data extraction when you add them to the folder. When new drawings are detected, the New drawings found dialog box displays the list of new drawings that were added to the folder after you performed the extraction.
       • Include subfolders. Adds drawing files located in subfolders relative to the position of the current drawing file to the list.
       • Use wild-card characters to select drawings. Lets you specify a subset of drawings from the specified folder. Type wildcards (? for a single character, or * for a string of characters) to specify the matching drawing names.
   • Add File. Adds a specified drawing file to the list.
   • Remove. Removes drawing files or folders from the list.
6. Optionally, click Settings. The More Settings dialog box appears.

   The specified folders and drawings appear in the Drawing files and folders list.

More Settings

In the More Settings dialog box, you can set the following options:

• Extract entities from blocks. Includes block entities.
• Extract entities from references. Includes entities and blocks from externally referenced drawings (References).
• Include references in block counts. Includes entities.
• Entities in model space. Includes all entities from the Model mode and excludes all entities from the Sheet mode.
• All entities in drawing. Includes all entities from the Model and all Sheets, including drawing information.

Selecting Entities for Data Extraction

The Select Entities page of the Data Extraction wizard lets you specify the types of entities and drawing information to take into account for the data extraction table.

Entities list

Displays only entities that exist in the specified drawing. By default, the list includes entities from both, model and layout mode, including entities from Blocks and external References. To change the default settings, access Settings from the Define Data Source page.

For each entity, the following information is available:

• Entity. Displays each entity by its name.
• Display Name. Displays the default name of the entity. Optionally, you can provide an alternative name for each entity. Right-click a line in the list and select Edit display Name from the context menu. The specified name will appear in the data extraction table.
• Type. Specifies whether the entity is a block or not.

Right-clicking a line displays the following options:

• Check All. Selects all entities from the list.
• Uncheck All. Clears all entities from the list.
• Invert Selection. Reverts the current selected entities in the properties list.
• Edit display Name. Lets you modify the entity display name in the specified row.

You can filter the list using the Display options.

Properties of the entities that you select in the list of entities appear in the Select Properties page.

Clicking a column head reverses the order.

Display options

Lets you filter the list of entities.

• Display all entity types. Displays all entities of all types in the entities list.
• Display blocks only. Displays only blocks in the entities list.
• Display non-blocks only. Displays only entites that are not blocks.
• Display blocks with attributes only. Displays only blocks that have attributes.
• Display entities currently in-use only. Displays only entities that exist in the specified drawings.

Preview

Displays the preview image of a specified Block type entity.

Selecting the Properties to Include in the Data Extraction

The Select Properties page of the Data Extraction wizard lets you specify the properties to extract and display in the data extraction table.

Properties

Displays the list of properties from the entities specified on the Select Entities page. You can filter the property list according to the categories.

To display properties in the extraction table, select the checkbox in front of the corresponding property.

Right-clicking a line or a column displays a context menu with options, such as:

• Check All. Selects all properties from the properties list.
• Uncheck All. Clears all properties from the properties list.
• Invert Selection. Reverts the current selected properties in the properties list.
• Edit display Name. Lets you modify the property display name in the specified row.

Clicking a column header reverses the sort order.

Each row displays the following information:

• Property. Displays object properties from objects selected on the Select Entities page. The property list is filtered according to the filter categories that are selected. Properties are the same as those listed in the Properties palette.
• Display Name. Displays the default name of the property. Optionally, you can provide an alternative name. Right-click a line in the list and select Edit display name from the context menu. The specified name will appear in the data extraction table.
• Category. Displays the category from the Properties palette that includes the property.

Category filter

Displays a list of categories based on the property list. Unchecking a category filters the Properties list.

To select the properties to display in the table:

1. Optionally, filter the Properties list using the categories from the Category filter.
2. In the Properties list, select the properties to extract.

   Click Clear All to cancel the selections or Select All to select all properties.

3. To change the display name of a property, right-click the corresponding line and select Edit display name from the context menu. Next, type the new name in the cell.

Organizing and Refining Extracted Data

The Refine Data page of the Data Extraction wizard lets you modify the structure of the data extraction table to organize and refine data before exporting it to a table or to an external file.

Clicking the columns header lets you sort the entries based in that column values. Sequential clicks will switch between ascending and descending order.

You can do the following:

• Combine identical rows. Groups identical rows in the table and updates the Count column accordingly.
• Show count column. Displays or hides the Count column.
• Show name column. Displays or hides the Name column.

Link External Data displays the Link External Data dialog box. Allows you to link external Microsoft Excel files.

Full Preview displays the preview of the table in a dialog box.

Link External Data Dialog Box

The Link External Data dialog box lets you link data from a Microsoft Excel file to the data extraction table.

Data Link

Select a data link

Displays a list of established links to external data files. To attach a new Microsoft Excel file to the drawing, click Select data link.

Open data link manager

Displays the Data Link Manager dialog box. You can create new data links to Microsoft Excel files that can be used as presets.

Data matching

Drawing data column

Displays a list of extracted property columns.

External data column

Displays a list of column names from the linked Microsoft Excel file.

Match

Performs a match check between a drawing data column and an external Microsoft Excel file data column by comparing the data in the specified drawing data key column and the external data key column. The match is successful if the data is unique across all rows in the external data key columns, and there is at least one data match between the drawing data and the data in the spreadsheet. If these conditions are not met, a warning message is displayed.

Case sensitive

Takes letter case into account when comparing data in the drawing columns with data in the externally linked columns.

Ignore the prefix and suffix white space

When enabled, the data match validation process ignores the blank spaces in front of and after the entries in the compared columns.

Additional external data columns for data extraction table

Select external data columns to include

Lists the columns from the data extraction table by order in which they appear in the external Microsoft Excel file. You can specify the columns to include at the end of the data extraction table.

Use top row of external data as column names

Uses the top row of data in the data link as the column name for the linked data. When disabled, columns are labeled Column 1, Column 2 and so on.

Setting the Data Extraction Output

The Choose Output page of the Data Extraction wizard lets you output the extracted data to a table within your drawing, to an external file, or both.

The following options are available:

• Insert data extraction table into drawing

  When the data extraction is complete, the software prompts you to insert the table into the current drawing.

  By default, the cells of the data extraction table are locked. To enable editing, unlock individual cells or the entire table. To display the locking and unlocking options right click the cell to unlock.

  Note: After placing the data extraction table on the drawing you can use the ExportTable command to export the data into .csv format

• Output data to external file

  You can export the data from the data extraction table to the following file formats:

  • Comma-separated file (.csv format). CSV (Comma-separated Values) is an ASCII file format that stores tabular data for spreadsheet or database software. Commas separate cell values. Strings are enclosed by single quotation marks.
  • Text file. A .txt file is a standard text document in which the fields of the data extraction table are separated by tab characters. The .txt file does not include formatting information.

To export a data extraction table to an external file:

1. Type ExtractData at the command prompt.
2. In the Data Extraction wizard, click Next repeatedly until you reach the Choose Output page.
3. Select Output data to external file and click Browse.
4. In the dialog box, specify the following:
   • Save in. Navigate to the folder where you want to store the file.
   • File name. Type a name for the file.
   • Save as type. Specify .csv or .txt type.
5. Click Save.
6. On the Finish page, click Finish.

   The command generates the external file of the specified type.

To insert a data extraction table in the current drawing:

1. Type ExtractData at the command prompt.
2. In the Data Extraction wizard, click Next repeatedly until you reach the Choose Output page.
3. Select Insert data extraction table into drawing and click Next.
4. On the Table Style page, do the necessary settings and click Next.
5. On the Finish page, click Finish.

Formatting Data Extraction Tables

The Data Extraction page of the Data Extraction wizard controls the appearance of the data extraction table. This page appears only if you specified Insert data extraction table into drawing on the Choose Output page.

You can use a table style to format the data extraction table.

To specify the table style and format:

1. On the Table Style page, under Table style, select a TableStyle or click View table styles to create or edit a TableStyle.

   An image of the choosen TableStyle displays in the preview area.

2. In Cell styles, set:
   • First row. Select a cell style (Title, Header, or Data).
   • Second row. Select a cell style (Title, Header, or Data).
   • All other rows. Select a cell style (Title, Header, or Data).
   • Optionally, select Use property names as additional column headers.
3. Click Next.
4. On the Finish page, click Finish.