Import 3D Points from Excel (AutoLISP Solutions)

1 Apr, 2003 By: Tony Hotchkiss

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.

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

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))
(if (= count 1)
(setq x (atof str2))
(setq str1 (substr str1 (1+ i)))
(setq i 0)
(setq count 2)
(setq str2 "")
) ;_ end of 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.

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.

About the Author: Tony Hotchkiss

AutoCAD Tips!

Lynn Allen

In her easy-to-follow, friendly style, long-time Cadalyst contributing editor and Autodesk Technical Evangelist Lynn Allen guides you through a new feature or time-saving trick in every episode of her popular AutoCAD video tips. Subscribe to the free Cadalyst Video Picks newsletter and we'll notify you every time a new video tip is published. All exclusively from Cadalyst!

Follow Lynn on TwitterFollow Lynn on Twitter

Which device do you typically use to read content?
A desktop computer / tower workstation
A tablet
A smartphone
A laptop or mobile workstation
I regularly use both a desktop computer and a smartphone for this purpose
I regularly use another combination of devices for this purpose
I prefer to print out articles from the website and read them on paper
Submit Vote

Download Cadalyst, Fall 2015

Download Cadalyst Magazine Special Edition