Update Attributes from a Comma-Delimited File (AutoLISP Solutions AutoCAD Tutorial)30 Jun, 2008 By: Tony Hotchkiss
Routine imports and exports data between an AutoCAD drawing and an Excel spreadsheet.
Allen Rehberg of Florida asked for a program to make changes to attributes of blocks that are used in a fire alarm drawing. He maintains a Microsoft Excel file with all the details of many types of blocks having different numbers of attributes. This type of data can be extracted from AutoCAD using the data extraction commands in the Tools menu, which can also create a table in the drawing and create an Excel file. However, if changes are made to the attributes of the spreadsheet, the attributes are not changed on the drawing, so I wrote the AutoLISP Solution to both export and import data to and from a comma-delimited file.
The AutoLISP Solution is ATTRIBUTE-TABLE-UPDATE.LSP and ATTRIBUTE-TABLE-UPDATE.DCL, which exports certain types of insert objects with their attributes and x-y coordinates to a comma-delimited file suitable for opening in Excel. The program also makes changes to one of the attributes and deletes any inserts that are not listed in the comma-delimited file.
Download the Code
Download the ATTRIBUTE-TABLE-UPDATE.LSP and ATTRIBUTE-TABLE-UPDATE.DCL files from Cadalyst's CAD Tips site. Save the file in AutoCAD's Support directory. Use the Appload facility by selecting Tools / Load Application, and then use the browser to select the file.
How to Use the ATTRIBUTE-TABLE-UPDATE.LSP Code
The Export/Import options prompt.
If you select Export, you are asked to enter the number of the building floor where the fire alarm blocks are located; then a results file dialog box is displayed as shown below. Navigate to where you want to store the resulting comma-delimited (CSV) file suitable for reading into an Excel spreadsheet.
The Results File dialog box.
The results file has the file extension CSV, and it can be opened with Notepad or Excel. If you choose to open it in Excel you will see the Excel spreadsheet as shown here. Changes may be made to the ADDRESS1 column, and any rows may be deleted as required in order to use the file to make changes to the drawing.
The EXCEL spreadsheet.
After any changes have been made and saved in CSV format, the program can be started again by entering ATU so that you can select the Import option from the Options prompt of Figure 1. You will see the Import file dialog box that prompts you to select the CSV file containing any changes to be made to the drawing.
The Import File dialog box.
After the import file is selected, the changes are made and the Address Changes dialog box is displayed as shown below. Click OK to remove the dialog box and to inspect the drawing to check that the changes have been made correctly. Note that you can display the Address Changes dialog box at any subsequent time in the drawing session by entering (Show Changes), including the parentheses.
The Address Changes dialog box.
The program starts with the error handler and system variable manager that I have used for many years. The next function is ATT-DATA-OUT, which sets the Export/Import options and calls GETBLKS, PRINT-OUT, and DO-IMPORT, depending on which choice is made to export or import CSV files.
GETBLKS produces a list of all INSERT objects that are required to be included. These are specific to Rehberg's request and may not be relevant for general use. You can make suitable adjustments by changing the list of tests in two IF statements, shown here:
(if (and (= (vla-get-HasAttributes blkobj) :vlax-true)
(> (strlen bobjname) 13)
(= (substr bobjname 1 14) "FA_DEVICE_INIT")
) ;_ end of and
(setq inspt (vla-get-InsertionPoint blkobj))
(setq ipt (vlax-safearray->list (vlax-variant-value inspt)))
(setq attribs (vla-getAttributes blkobj))
(setq sarr (vlax-variant-value attribs))
(setq num (vlax-safearray-get-u-bound sarr 1))
(if (and (> num 0)
(< (car ipt) 3000.0)
Here, the second and third lines in this segment of the GETBLKS function set some parameters for including block inserts. They are that the block names are greater than 13 characters in length and that the first 14 characters are FA_DEVICE_INIT. You can change these to search for your own block inserts that have attributes. The second-to-last line of this code segment specifies that only those block inserts that have insertion points less than 3,000" in the x-direction should be considered. Again, you may replace this with your own criteria.
The next function, PRINT-OUT, refers to some specific attributes -- ADDRESS1 and DEVICE_LOCATION -- and you may substitute these for your attribute tags in the following segment of the PRINT-OUT function:
(write-line "ADDRESS1,Name,Device_Location,floor,X,Y" f1)
(repeat (length blklst)
(setq lst (nth (setq i (1+ i)) blklst))
(setq str (strcat (cadr (assoc "ADDRESS1" (nth 1 lst)))
(cadr (assoc "DEVICE_LOCATION" (nth 1 lst)))
(rtos (car (last lst)))
(rtos (cadr (last lst)))
) ;_ end of strcat
) ;_ end of setq
(write-line str f1)
) ;_ end of repeat
The function DO-IMPORT opens the import CSV file and calls the function DO-ADDRESS to read each line of the CSV file and return a list of the block insertion points and of the changes to be listed in the Address Changes dialog box. If you wish to customize this part of the code for your own use, you should keep the structure of the CSV file as shown in Figure 3 (the Excel spreadsheet), adhering to the following criteria:
- The first field is the attribute value to be changed (ADDRESS1 in this program).
- The second field is the block insert name.
- The third field is another attribute value equivalent to the DEVICE_LOCATION.
- The fourth field is the floor number.
- The last two fields are the x and y locations of the block inserts.
The attribute tags mentioned above also appear in these last two functions and may be changed accordingly.
As always, I look forward to receiving your comments and requests for AutoLISP Solutions. Please contact me using the links below.