Plot Assay Data Using Values from a Spreadsheet

AutoLISP routine inserts blocks with geological sample attributes.

Bob Stirling from Whitehorse in Canada's Yukon Territory works with geographical information systems (GIS) for rock and soil geochemistry. He uses assay information in the form of spreadsheets that contain sample data with blocks representing a variety of rock, soil or silt (or other conditions) and attributes that describe the assay location, identifier and any number of other attributes, depending on the block symbol. Bob requested a routine that would plot the assay data by inserting the blocks and displaying the attribute values from the spreadsheet.

This AutoLISP Solution is a return to a program that first appeared in Cadalyst some time ago, and has been modified considerably to become INS-BLK2.LSP. The new routine not only inserts blocks at designated locations, but also adds any number of attributes contained in each of the block definitions. Attribute values are defined in the spreadsheet data. The number of attributes for each block is defined by the block itself, and the order of the attributes is predefined when the attribute definitions were selected to form the block. The spreadsheet data does not specifically name any attribute tags, but only contains the attribute values, together with the block names and their intended x (east), y (north), and z (elevation) insertion points.

Get the Code
Download the INS-BLK2.LSP and INS-BLK2.DCL files from Cadalyst's CAD Tips site and save them in AutoCAD's Support directory. Use the Appload facility by selecting Tools / Load Application. Then select the INS-BLK2.LSP program from where you stored the downloaded files.

How to Use the INS-BLK2 Code
After you load the code, the system prompts you to enter IB2 to start the program. To see this prompt, you may need to set your Command window size to three lines by dragging the Command window splitter bar appropriately. After you enter IB2, the Import Blocks and Attributes dialog box pops up (figure 1).

Figure 1. The Import Blocks and Attributes dialog box with default selections.

You can choose which delimiter is used in the delimited file (comma or semicolon) and the location of the symbols as the current drawing or an external drawing.

If you select the external drawing followed by the OK button, the Symbols Drawing File dialog box will pop up (figure 2). Navigate to where the symbols file is located and select it as shown. In the example shown, the preview box appears to be empty because the blocks are contained in the drawing but they are not referenced anywhere.

Figure 2. The Symbols Drawing File dialog box.

Click Open to pop up the Import File dialog box (figure 3). Navigate to where the delimited file is located and select it as shown.

Figure 3. The Import File dialog box.

Again, click Open to see the results in AutoCAD. You may need to Zoom Extents as shown (figure 4), which displays nearly a thousand assay points with attribute values.

Figure 4. Assay points in AutoCAD.

The zoomed picture (figure 5) shows some of the symbols and their attributes. An Excel spreadsheet displays some of the data corresponding to the zoomed portion of the AutoCAD drawing (figure 6).

Figure 5. Zoomed assay points in AutoCAD.

Figure 6. Excel spreadsheet data.

The spreadsheet of figure 6 is organized as follows:
Column A: Block name
Column B: X (East)
Column C: Y (North)
Column D: Z (Elevation)
Column E: Attribute 1 (Assay sample number)
Column F: Attribute 2
Column G: Attribute 3
Column H: Attribute 4

The first four columns are always as specified here, but there may be any number of attribute columns, depending on how each block has been defined.

Programming Notes
The program starts as usual with my error handler and system variable functions. In the past I also included a DXF function, but I have given that up in favor of using the more modern object properties in Visual LISP. The function INSBLK2 is the dialog box driver function that calls INIT to initialize the settings in the dialog box. It also retrieves data that has been entered in the dialog box before calling the DO-INSERT function that inserts the blocks and attributes.

DO-INSERT tests that the symbols (blocks) are in the current drawing or in an external drawing, and if necessary, it inserts the external drawing containing the blocks. The Visual LISP method InsertBlock is used here. The choice of delimiter code is obtained before the function DO-DELIM-FILE is called.

All the real action takes place in the DO-DELIM-FILE function. A while loop is used to ensure that all the records from the import (delimited) file are read and acted upon. The first four fields in each record are used to obtain the block name and insert point x (east), y (north), and z (elevation) coordinates. The InsertBlock method is again used as follows:

(setq BRefObj (vla-InsertBlock *modelspace* inspt bname 1 1 1 0))

The InsertBlock method returns the name of the block reference object BRefObj, and this is used to get the attributes associated with it in the next line of code, thus:

(setq attribs (vla-getAttributes BRefObj))

What could be simpler than this object-oriented approach, compared with the alternative of using many lines of code to search the "next" entity and figure out the DXF code using the older standard AutoLISP techniques? After this, the number of attributes attached to the block reference is used to finish the job of inserting the attribute values, as follows:

  (setq sarr (vlax-variant-value attribs))
  (setq num (vlax-safearray-get-u-bound sarr 1))
  (repeat (1+ num)
    (setq
     attrefobj (vlax-safearray-get-element sarr (setq j (1+ j)))
    ) ;_ end of setq
    (setq val (parse str Dcode (setq i (1+ i))))
    (vla-put-TextString attrefobj val)
  ) ;_ end of repeat
 ) ;_ end of while
) ;_ end of do-delim-file

Note that the order of the attribute values is the order that is defined when the blocks were first created, and the attribute tags are not used in the code. This eliminates the need to search and associate a value with its tag, and makes for much simpler programming. The repeat loop above is inside the while loop as shown, and the next line of code is the closing parenthesis of the DO-DELIM-FILE function.

Last Words
The repeat loop uses (1+ num) because the number of attributes associated with the block reference is counted from zero instead of 1. This is common in program languages, but as an engineer, it never ceases to amaze me that the computer science world does not appear to know that the rest of us are accustomed to count from 1 upward, and not from zero. How are we expected to teach our children to count in their prekindergarten years when this start-from-zero nonsense exists? I would be interested to know what our readers think about this.

As always, I look forward to receiving your requests for AutoLISP Solutions. Contact me using the links below.