AutoCAD

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
Figure 1. Excel spreadsheet contains x,y,z points.
 figure
Figure 2. Export a comma-delimited text file from your Excel spreadsheet.

 

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

Autodesk Technical Evangelist Lynn Allen guides you through a different AutoCAD feature in every edition of her popular "Circles and Lines" tutorial series. For even more AutoCAD how-to, check out Lynn's quick tips in the Cadalyst Video Gallery. Subscribe to Cadalyst's free Tips & Tools Weekly e-newsletter and we'll notify you every time a new video tip is published. All exclusively from Cadalyst!
Follow Lynn on Twitter Follow Lynn on Twitter



Poll
Which file format do you use most often for CAD drawing/model exchange?
Native format
PDF
3D PDF
DWF
STEP or IGES
JT
IFC
Other
Submit Vote