Vol. X, No. 1

Spring, 1997

Alexei Vranich, a graduate student at the University of Pennsylvania, had completed the fieldwork portion of an excavation project at a temple site in Bolivia. He had carefully surveyed the remains of the temple platform there, using a total station to locate the corners of blocks so that they could be fully modeled. The data with which Mr. Vranich returned - and with which he came to CSA - were in the form of Excel® spreadsheets containing the data points and text identifiers. Mr. Vranich and CSA Director Harrison Eiteljorg, II, found that it was possible to convert the information in the spreadsheets into a form very close to that needed by AutoCAD, paste that information into Word®, modify it a bit more with simple search-and-replace processes, and, in the end, have AutoCAD commands that created the figures automatically. This was so successful that it seems a method that should be explained to others so that they can put it to use.

The following explanation may be too detailed for those who are unfamiliar with AutoCAD and the use of scripts (macros), but the fact that simple processes can transform information from a spreadsheet into automatic drawing processes in AutoCAD should be of interest to all.

First, we should explain that a simple text file with standard AutoCAD commands can be created and used as a macro, called a script in AutoCAD. Let us assume, for instance, that we want to create a line from a point defined by the coordinates 1,1,0 to the point at 3,1,1. We could issue the following set of AutoCAD commands to carry out the process (using <cr> to indicate the return or enter key):

line<cr>
1,1,0<cr>
3,1,1<cr>
<cr>.

If we put the same characters into a text file, saved it with the SCR extension, and used the script command in AutoCAD to call up the file, the program would recognize it as a series of commands to be treated exactly as if typed from the keyboard. So AutoCAD would process the commands as indicated - first the line command would be initiated; then the coordinates of the starting point (plus the carriage return to indicate the completion of the entry); and then the coordinates of the next point (with the carriage return again indicating the completion of the entry). Finally, the last carriage return indicates the completion of the command. Such a can be typed from the keyboard or put into a text file, and the results will be the same.

Those familiar with AutoCAD will realize that the same command could have been issued with a space use instead of each carriage return. The file could have been simply "line1,1,03,1,1 ." The character was used in the foregoing to stand for a space so that the number and positions of the spaces would be clear.

Knowing that such a command sequence could be used, it was a rather simple matter to modify a spreadsheet so that an AutoCAD script could be created. The small portion of one of the spreadsheets we started with shown in Table 1 illustrates the starting point.

There are coordinates for four points for the top of stone 5 in trench M, coordinates for four corresponding points for the bottom of the stone, coordinates for six points for the top of stone 6, and there our example breaks off.

We wanted to make surfaces in AutoCAD, using the points for the top of each stone for one surface and the points for the bottom of each stone for a second one. Then we needed to make the side surfaces. Since we were not certain that the manner in which the data were collected would permit a complete automation, we decided to make the top and bottom surfaces indirectly (using the CSA LISP command, mpface, to make a surface from a 3D polyline). This required that we first make the 3D polylines for the top and bottom of each stone and the create the surfaces. We also discovered that we needed to control the layers and the zoom positions rather carefully; so we included processes to do that.

The first part of the process was to modify the spreadsheet in the simplest way possible, using formulae to fill in things we knew we would need in the script for AutoCAD. For instance, we needed a sequence of numbers for the points; so a new column was added, one with a number sequence, beginning with one and augmenting itself through the table, as shown in Table 2 (labels abbreviated).

At this point, each set of coordinates has a sequence number, but the points are expressed as three separate coordinates in three columns. A formula will combine them so that they will make sense in AutoCAD, three coordinates separated by commas, as in Table 3 (only the first 8 entries shown, and the original columns with x, y, and z values not included).

No other improvements were made initially. In order to make three-dimensional polylines marking the edges of the stones, we needed to add a statement to start the AutoCAD 3dpolyline command, but we also knew that we would want each polyline to be on its own layer; so we added the layer control features at the same time, as shown in Table 4.

To include the command for adjusting layers and the one for starting the polyline, we actually modified the original formula that made a single column of the coordinates; so we could have used a single step here. Now the sequence begins (ignoring the label) with a command to make a new layer, then the number of the layer which serves as its name here, then the command that will freeze all other layers, and a command to draw a 3D polyline using the x-, y-, and z-coordinates of the points. An Excel formula created all the values in column 2. We did not have to know how many points there were on any given stone, since the change from an upper to a lower surface could be detected and used to start the process over.

The last change in Excel was to add a command to make the 3D polylines into bounded surfaces, using CSA's mpface command. We learned that our figures were so close together that we needed to be sure only the layer with the correct polyline and the current drawing layer were on; so the new set of commands had to include layer control again. We also learned - but still do not know why - that we needed to zoom in on the image before invoking the mpface command; so the zoom command was added as well. The command to close the polyline was also added at this point. One formula accomplished the changes that yielded the result shown in Table 5.

The spreadsheet thus created had all the appropriate AutoCAD commands (including the correct numbers of spaces, which can be critical), but it was in spreadsheet format rather than text format. It was copied into an empty Word document (without the labels) and saved as text so that the text alone, without the spreadsheet format, would be saved. The result began as follows (with <cr> used to show the presence of the return character):

Layer Make 1 Freeze * 3dpoly 39.053,-72.05,-1.652<cr>
<cr>
38.992,-72.173,-1.68<cr>
<cr>
38.887,-72.152,-1.705<cr>
<cr>
38.919,-72.038,-1.686<cr>
close Zoom All Layer Make 4 mpface Endpoint 38.919,-72.038,-1.686<cr>
Layer Make 5 Freeze * 3dpoly 39.04,-72.036,-1.74<cr>
<cr>
39.016,-72.167,-1.763<cr>
<cr>
38.916,-72.169,-1.784<cr>
<cr>
38.931,-72.047,-1.769<cr>
close Zoom All Layer Make 8 mpface Endpoint 38.931,-72.047,-1.769<cr>

We then had to remove the unnecessary carriage returns, an easy task, to create this file:

Layer Make 1 Freeze * 3dpoly 39.053,-72.05,-1.652<cr>
38.992,-72.173,-1.68<cr>
38.887,-72.152,-1.705<cr>
38.919,-72.038,-1.686<cr>
close Zoom All Layer Make 4 mpface Endpoint 38.919,-72.038,-1.686<cr>
Layer Make 5 Freeze * 3dpoly 39.04,-72.036,-1.74<cr>
39.016,-72.167,-1.763<cr>
38.916,-72.169,-1.784<cr>
38.931,-72.047,-1.769<cr>
close Zoom All Layer Make 8 mpface Endpoint 38.931,-72.047,-1.769<cr>

Saved as a text file, this is a valid AutoCAD script. It begins with a layer command - setting the current drawing layer to the sequence number we put in the spreadsheet at the beginning of the process and freezing all other layers. The 3dpoly command follows, after which are the coordinates of the polyline, the close command (c), and the mpface command.

The carriage returns, of course, would not show as <cr>; we have used the <cr> here to show that each line was terminated with a carriage return.

This process actually involved three operations in Excel - adding the sequence numbers, making the column with the 3dpoly command and/or the coordinates, and making the column with the mpface command. One process was required in Word, the one that removed the unnecessary carriage returns.

Fig. 1 - The simple block modeled with the AutoCAD script described.

This is not something to be done with a small number of data points, but we had hundreds in the original spreadsheet - and more in other spreadsheets. Not only could we create a script file, but we could break it into pieces, entering only as many blocks at a time as we wanted, thus making the AutoCAD data entry process quick and, at the same time, controlled. Such a process should be a great time-saver for data like these, with processes that are used repeatedly.

This process only made the tops and bottoms of the stones, the sides were done with another of the CSA LISP routine, dblface, when possible. However, since the data points were not always taken in the correct sequence, the sides sometimes had to be modeled individually. Indeed, had we been sure that the sequence as correct, we could have automated making the sides as well.

The actual processes we generated are not as important as the ease with which we did so. Furthermore, having done this with a single file, we know how to do it with any number of additional ones. Of course, other command sequences could be used; the point is that a spreadsheet with survey data is a much more flexible thing than one might suppose. So is an AutoCAD script file.

For other Newsletter articles issues concerning the use of CAD in archaeology and architectural history, consult the subject index.

Next Article: Data From Sardinia to be Available

Table of Contents for the Spring, 1997 issue of the CSA Newsletter (Vol. X, no. 1)

Table of Contents for all CSA Newsletter issues on the Web