Import 3D Points from Excel (AutoLISP Solutions)

Create AutoCAD polylines from point data stored in spreadsheets.

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

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.

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

Figure 5. Highlight the Excel data for your BOM.

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.

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

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

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.