[Search tip detail and code files using keywords, tip number, author name, etc ]
    For tip to function correctly, you must ensure that the downloaded file name matches the file name
    displayed in the Rename File To field. Please rename downloaded files when necessary.
ALSPSOL0708: Update Attributes from CSV File
Tip# ALSPSOL0708 By Tony Hotchkiss On 01-Jul-2008
Rated By 1 users Downloaded : 3023
Categories : OLE Files, Import, File Data, Export, Attributed Blocks
Software type : AutoCAD
ATT-TABLE-UPDATE.LSP: 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.


To start the program, enter ATU on the Command line, and you will see the Options prompt shown below. The options are the default Export and the Import as shown.

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 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.

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.

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.

Programming Notes
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)))
(car lst)
(cadr (assoc "DEVICE_LOCATION" (nth 1 lst)))
(itoa floor)
(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 at tony.hotchkiss@cadalyst.com.


Average Rating:

User comments
Comment by Marsland,Davd
Posted on 2008-10-21 09:26:10
I'm trying (very hard) to use this script to update the attributes of some blocks. I've built a test document and a csv in the following format, but without success: ADDRESS1,Name,Device_Location,floor,X,Y AB,SQUARE,CD,2,0.00,0.00 My block is called SQUARE, co-ordinates 0,0; and has attribute tags "ADDRESS1" and "Device_Location". By my undertanding ATU > I > *.csv should update the attributes to read "AB" and "CD". I have not yet worked out why I am asked for floor information on export. Neither import nor export are successful, but no errors are reported if the csv is foratted as above. Our company uses AutoCAD 2002. Any help appreciated. Thanks