CAD Forum - tips, tricks, discussion and utilities for AutoCAD, Inventor, Revit and other Autodesk products []
CZ | EN | DE
Login or
  Visitors: 6598

CAD tip CAD tip # 13522:

Question CAD 
 %  platform  category 
Q - question

LookupXLS - populating DWG texts from Excel spreadsheets.

A - answer LookUpXLS is a freeware LISP utility by Arkance Systems designed to automate AutoCAD DWG drawing texts. It can automatically edit or add drawing texts (e.g. block attributes) from codebooks and other Excel tables (.XLSx, .CSV).

You can use this "text-feeder" to lookup and fill (replace or append) text/attributes in a DWG drawing from a conversion table in Excel (e.g. price lists, code lists, product catalogs, phone books, lease agreements, IP addresses, etc.). In the Hyperlink mode you can also attach hyperlinks to DWG objects.

The tool processes any XLS or CSV spreadsheet where some (source) column stores the values to be searched for (e.g. order number, room number) and another column or columns (target) contains the values to be transferred to the DWG. Sheets and columns are specified in the application. They can be specified arbitrarily, so you can, for example, search for a value in column D and fill in the selected attributes in the DWG file with the corresponding values of columns B and G from Excel.

You can replace/append selected texts (directly search and replace its value) or block attributes. For blocks, you need to specify the name of the source attribute (you can either pick it or specify the name with a wildcard mask) and the name of the target attribute (again, either pick or specify the name), or a comma-separated list of attribute names can be specified.

A "lookup" is then performed for the selected DWG objects, i.e. filling/replacing the values from the XLS table. E.g. for blocks with a specific part number the price, SAP-code, fire resistance code, material description etc. are added.

The mapping parameters (filename, sheet, columns, attribute names) are saved in the DWG file, so that the next time you use it (to update the values) you just need to confirm the parameters and re-select the objects.

The timestamp of the last update from the lookup table is also checked and compared with the time of the XLS file change (file timestamp) - and if the XLS is newer, a warning is displayed, prompting you to lookup-update the drawing.

The XLS "price list" file is also noticed inside the DWG file so that this table is automatically transmitted at the same time as the DWG when the eTransmit function is used. This happens only if a LISP variable is preset:
(setq _lookupXLSaddlink T)

See samples:

Download the LookupXLS utility from Download and load the VLX file into AutoCAD using APPLOAD. Then type the LookupXLS command to lookup and fill in the values. Confirm the saved values or select the XLS/CSV data source and specify the source column and target column(s) of the table. When working with block attributes, select attributes by picking or by name (mask, list). Select the objects to be edited by window or mask of the block name (even dynamic). Requires installed MS Excel.

The Lookup1 helper command displays lookup values for the specified source text.

The LookupCheck helper command checks the XLS table for timestamp (time-currency).

Sample DWG drawing and XLSx table attached in the downloaded ZIP archive.

You can predefine default values using LISP variables:

(setq __AR:srcAtt "OrderNo") ; source attribute
(setq __AR:tgtAtt "Price")   ; target attribute
(setq __AR:bmask "*")        ; mask of selected blocks
(setq __AR:default "")       ; default value when not found in the table
(setq __AR:XLSline 1)        ; number of the first row in the table (with field names)
100% *  CAD 
31.10.2022    25656×  

See also:
Tip 11898:Automatic schedule - number of blocks in a drawing.
Tip 7465:How to import XYZ coordinates as DWG points with elevation labels?
Tip 6366:Inserting and updating time and date in an AutoCAD drawing.
Tip 820:How to link block attributes with an Excel sheet?

Back   All CAD Tips

Have we helped you? If you want to support the CAD Forum web service, consider buying one of our CAD applications, or our custom software development offerings, or donating via PayPal (see above). You may also add a link to your web - like this "fan" link: CAD Forum - tips, utilities, blocks for Autodesk products
CAD:    OS:    Categ: 
Text:  FAQ glossary   

Publish interactive 3D PDF models from AutoCAD, Inventor or Revit with Share3D More info

Please use these tips at your own risk.
Arkance Systems is not responsible for possible problems that may occur as a result of using any of these tips.