See also Terry Rawkins Script profile with Excel.

Tony Hotchkiss, Ph.D., P.Eng., is professor of CAD/CAM/CAE at the State University of New York College at Buffalo and a Cadalyst contributing editor. Contact him at 42 15th St., Buffalo, NY 14213, 716.883.8790, e-mail tony.hotchkiss@cadalyst.com or visit his web site. This article originally appeared in the April 2003 issue of Cadalyst Magazine.

Import 3D points from Excel
Create AutoCAD polylines from point data stored in spreadsheets.
by Tony Hotchkiss

Two readers want to import Excel spreadsheet data into AutoCAD. David Rodrigues wants to bring a DOS text file saved from an Excel spreadsheet into AutoCAD. John Kuenzle of Waukesha, Wisconsin, needs to create 3D polylines from x,y,z coordinates stored in an Excel spreadsheet. IMPORT-3D-POLY.LSP takes a set of x,y,z coordinates from a comma-delimited text file and creates a 3D polyline.

A comma-delimited text file is a file type you can save directly from an Excel spreadsheet. Figure 1 shows a sample Excel spreadsheet, and figure 2 shows the corresponding comma-delimited text file.

Note:
This Article does not tell us how to make a comma delimited file from a Excel spreadsheet (except telling us to leave the top line blank).

The result of running IMPORT-3D-POLY.LSP appears in figure 3. More than a thousand 3D points went into creating this 3D polyline in the form of a helical coil.

Figure 1. Excel spreadsheet contains x,y,z points.

Figure 2. Export a comma-delimited text file from your Excel spreadsheet.

Figure 3. Polyline created in AutoCAD from imported spreadsheet data.

HOW TO USE IMPORT-3D-POLY.LSP
Download the file from the Get the Code section and save it in the AutoCAD support directory. Alturnatively you can download the lisp import-3d-poly.lsp directly or use import-3d-poly.txt to view it and take it off the screen. The command to run it is PL3. You might want to save it into your support folder as PL3.LSP which is the command to run it and is quicker to load using the keyboard). From the AutoCAD tools menu, choose Load Applications, or enter Appload at the AutoCAD Command prompt. In the Load/Unload Applications dialog box, select the IMPORT-3D-POLY.LSP file from the support directory where you installed it, then click Close. After you load the program, AutoCAD prompts you to enter PL3 to start.

Figure 4. import-3d-poly.lsp displays this 3D Points File dialog box.

IMPORT-3D-POLY.LSP displays the 3D Points File dialog box shown in figure 4, where you can locate the text file that contains the required points. No further user interaction is required. The 3D polyline is drawn on the current layer.

PROGRAMMING NOTES
IMPORT-3D-POLY.LSP starts with my error handler and system variable management functions, after which the function (poly3d) is used to call (get-plist) and (make-3dpolyline). (get-plist) uses the standard (getfiled) function to display the 3D Points File dialog box, and skips over the first line of the text file because it is assumed there is a header in the file. A (while) loop reads subsequent lines from the file by calling (get-pt) for each line of text. The (get-pt) function parses a text string and returns a list of x, y, and z coordinates converted to real numbers. The code for (get-pt) is:

(defun get-pt (str1)
 (setq comma (chr 44)
str2 ""
count 1
i   0
 ) ;_ end of setq
 (repeat 2
  (repeat (strlen str1)
   (setq char (substr str1 (setq i (1+ i)) 1))
   (if (/= char comma)
(setq str2 (strcat str2 char))
(progn
 (if (= count 1)
  (progn
   (setq x (atof str2))
   (setq str1 (substr str1 (1+ i)))
   (setq i 0)
   (setq count 2)
   (setq str2 "")
  ) ;_ end of progn
  (progn
   (setq y (atof str2))
   (setq str1 (substr str1 (1+ i)))
   (setq z (atof str1))
  ) ;_ end of progn
 ) ;_ end of if
) ;_ end of progn
   ) ;_ end of if
  ) ;_ end of repeat
 ) ;_ end of repeat
 (setq pt (list x y z))
) ;_ end of get-pt

  get-pt.lsp
  Download this code as a lisp file.

  get-pt.txt
  View this code as a text file.

  x-y-quadratic.txt
  View this code as a text file.
  Copy->Paste into Autocad
  Support folder to give a set
  of test co-ords (12 only).
  (From T.Rawkins work in
  AcadScripts -Customising.)

LAST WORDS
Keith Belt of Escondido, California, requested a way to create a BOM (bill of materials) from an Excel spreadsheet. The easy way is to highlight the BOM data in Excel (figure 5), then click on Excel's Copy button. Next, open an AutoCAD drawing, and from the Edit menu select Paste Special. Use the %PRODUCT Entities choice from the Paste Special dialog box (figure 6).

The function (make-3dpolyline) uses the (entmake) function to create a 3D polyline from a list of x,y,z coordinates. The 3D polyline in AutoCAD is the old-style polyline that has three sections: a polyline entity, a set of vertex entities, and a (seqend) entity.

Figure 5. Highlight the Excel data for your BOM.

Figure 6. Select Paste Special’s %PRODUCT Entities.

Then select an insertion point in AutoCAD. The BOM appears as separate line and text entities (figure 7).

Figure 7. BOM presentation appears in an AutoCAD paper space layout with an inserted drawing title block.

The grid lines come in only if they are displayed in Excel. In figure 7, I added a rectangle around the outside of the BOM entities to complete the picture, shown here in a paper space layout with an inserted drawing title block.

^TOP OF PAGE