• Manufacturing
• AEC
• GIS
• CAD Management
• Cadalyst Magazine
  • Purchase Latest Issue
  • Cadalyst Access Program
  • Magazine Archives (2004-2009)
• E-Newsletters
  • Tips & Tools Archives
  • CAD Manager's Newsletter Archives
  • AEC Newsletter Archives
  • MCAD Newsletter Archives
  • GIS Tech News Archives
  • HP Workstation Innovation News
  • Dell Tech Insider
  • Harry's Code Class Archives
  • Subscribe E-newsletters
• CAD Software & Hardware Reviews
• Columns
  • "CAD Manager" Column, by Robert Green
  • "Circles & Lines" AutoCAD Tutorial, by Lynn Allen
  • "Editor's Window" Editorials, by Nancy Spurling Johnson
  • "Learning Curve" AutoCAD Tutorial, by Bill Fane
  • "User Profile" Column
  • "Viewpoint" Guest Column
• CAD Software Tutorials
  • All CAD Tutorials
  • "Circles & Lines" AutoCAD Tutorial, by Lynn Allen
  • "Learning Curve" AutoCAD Tutorial, by Bill Fane
• CAD Tips & Tools
  • Cadalyst CAD Tips - Tips and Downloadable AutoCAD Code
  • Video Tips Gallery
  • Tips & Tools Weekly Newsletter Archives
  • CAD Software Tutorials
  • Cadalyst Benchmark Test
• CAD Video Gallery
  • All Cadalyst Videos
  • All-in-One Video Tips by Curt Moreno (Sponsored by HP)
  • AutoCAD Video Tips by Lynn Allen
• CADspeed: Hardware Blog
• Cadalyst Benchmark Test
• CAD Events Calendar
• CAD Cartoons
• Reader Polls
• Subscriber Services
• Job Search
• Cadalyst Store
• Advertising

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.

 

 

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.

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

 

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

 

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.