Required a lsp to make automatic topo survey by creating layer point text from one excel

[sdhara.hit]

Required a autocad lsp that can create text and point from excel file also make sure that text & point will be in same layer as text value or mention layer name. also need to crate that layer name from excel. Its to make automatic topo survey.

[autoid374ceb4990]

What exactly does "2d topo survey" look like?  The data you posted has no elevation data. If you just want 2D points drawn that should be no problem.

[pendean]

---

@sdhara.hit wrote:

Its too much high level thinking....

---

[diagodose2009]

Using Excel directly,  is very hard, because today exists too many version CAD program/s (Cadian, AutoCad, TurboCad) and parser coordinates from *excel to Drawing.CAd.; is too hard for everyone.

Only AutoCAd have between 70...100 versions Acad, and compatibility with MsExcel

is bad-lock for All.

M/r. sdhara.hit do not need import Excel .coo to Drawing, and extend Excel function/s

with  surveyour's hi-tech-function/s.

 [code]

My solution I can parser Excel coordinates files as profesionally surveyour, for she.

https://youtu.be/hrW-o7Ln4EA

https://youtu.be/AyqJf8csmXk

If she need, then she send PM tom me, perhaps together can research our'solutions.

[/code]

[autoid374ceb4990]

If the OP has Excel the XLS file can be saved as a CSV (comma delimited) file to be read easily by a LISP program.  Also the coordinate file the OP posted contains layer names with a space.  I am using a really old version of AutoCAD (R14) and spaces are not allowed in layer names, perhaps this is not true in later versions??

diagodose2009i: how do you calculate volumes with no Z coordinates?

[john.uhden]

@autoid374ceb4990 ,

I think Excel provides the choice of a tab or a comma as a delimiter when creating a .CSV, so not to worry about spaces.

[Sea-Haven]

Ok simplest way is read Excel direct, then insert a block with attributes, easy done. 

Again post a Excel we should not have to dummy up data for testing. 

What to you want at insertion point, a "Point" a "X" etc.

[sdhara.hit]

How can I delete this post? I want to draw just point. but from excel . with same layer define in excel in AutoCAD.

To write an AutoCAD LISP program that draws points from Excel and ensures the points are placed on the same layer as described in the Excel file, you can follow these steps. This involves reading data from an Excel file, extracting point coordinates and layer information, and then drawing points on the specified layers in AutoCAD.

First, ensure you have the required Excel file. The file should have the coordinates and layer information in columns. For example, let's assume the Excel file has the following columns:

• X: X-coordinate of the point
• Y: Y-coordinate of the point
• Z: Z-coordinate of the point (optional, can be set to 0 if not used)
• Layer: Layer name for the point

Here's the LISP program to achieve this:

lisp

Copy code

(defun c:DrawPointsFromExcel ()
;; Function to read data from Excel and draw points in AutoCAD
(defun ReadExcelData (filePath / excelApp workbook worksheet rows data)
(setq excelApp (vlax-get-or-create-object "Excel.Application"))
(vlax-put-property excelApp 'Visible :vlax-false)
(setq workbook (vlax-invoke-method excelApp 'Workbooks 'Open filePath))
(setq worksheet (vlax-get-property workbook 'ActiveSheet))
(setq rows (vlax-get-property worksheet 'UsedRange))
(setq data (vlax-get-property rows 'Value2))
(vlax-invoke-method workbook 'Close :vlax-false)
(vlax-release-object workbook)
(vlax-release-object excelApp)
data
)

;; Function to draw a point in AutoCAD
(defun DrawPoint (x y z layerName)
(command "._-LAYER" "M" layerName "")
(setvar "clayer" layerName)
(command "._POINT" (list x y z))
)

;; Main function to read Excel data and draw points
(defun DrawPointsFromExcelMain ()
(setq filePath (getfiled "Select Excel File" "" "xlsx;xls" 0))
(if filePath
(progn
(setq excelData (ReadExcelData filePath))
;; Assuming the first row is headers, so start from the second row
(setq rowCount (length excelData))
(setq i 2)
(while (<= i rowCount)
(setq row (nth (1- i) excelData))
(setq x (atof (nth 0 row)))
(setq y (atof (nth 1 row)))
(setq z (atof (nth 2 row)))
(setq layerName (nth 3 row))
(DrawPoint x y z layerName)
(setq i (1+ i))
)
)
)
(princ)
)

;; Execute the main function
(DrawPointsFromExcelMain)
(princ)
)

How to Use the LISP Program

1. Save the LISP code above into a file with the .lsp extension, e.g., DrawPointsFromExcel.lsp.
2. Open AutoCAD.
3. Load the LISP program using the APPLOAD command.
4. Run the program by typing DrawPointsFromExcel in the command line.
5. Select the Excel file when prompted.

But chat gpt lsp code not working.

[sdhara.hit]

sample excel

[autoid374ceb4990]

So now your Excel file does not have point names?

I have an old LISP program that I wrote many years ago that will read your file, but you would have to save the XLS file as a CSV ( space delimited text file) for it to work.  Send me a private message if you are interested. 

[sdhara.hit]

THANKS GUYS FOR YOUR VALUE ABLE ADVICE, ITS THE TIME OF AI,  NOW HE MAKE AS I WANTED, CHECK THIS

(defun c:TopoImport (/ file line data ptNo x y z layName pt)
(setq file (getfiled "Select CSV File" "" "csv" 0))
(if file
(progn
(setq f (open file "r"))
(read-line f) ; Skip the header row (A1, B1, etc.)

(while (setq line (read-line f))
(setq data (csv-split line ","))

(setq ptNo (nth 0 data)
x (atof (nth 1 data))
y (atof (nth 2 data))
z (atof (nth 3 data))
layName (nth 4 data)
pt (list x y z))

;; Create the layer if it doesn't exist
(if (not (tblsearch "LAYER" layName))
(command "-layer" "m" layName "")
)

;; Draw the Point on the specified layer
(entmake (list
'(0 . "POINT")
(cons 8 layName)
(cons 10 pt)))

;; Draw the Text (Point Number) on "TEXT" layer
(if (not (tblsearch "LAYER" "TEXT"))
(command "-layer" "m" "TEXT" "c" "2" "" "") ; Creates Yellow Text Layer
)
(entmake (list
'(0 . "TEXT")
(cons 8 "TEXT")
(cons 10 pt)
(cons 40 0.2) ; Text Height: Adjust as needed
(cons 1 layName)))
)
(close f)
(princ "\nImport Complete.")
)
)
(princ)
)

;; Helper function to split CSV strings
(defun csv-split (str delim / pos lst)
(while (setq pos (vl-string-search delim str))
(setq lst (cons (substr str 1 pos) lst)
str (substr str (+ pos 2)))
)
(reverse (cons str lst))
)

IN THIS LSP, I FIXED THE TEXT , AND IT TAKE IN ONE SINGLE LAYER, 

[Sea-Haven]

Not sure how missed the Excel file post would have done the direct read from Excel, maybe later today.

[Sea-Haven]

Give this a try, make sure you have correct Excel open.

; Read points from excel and layer name for point
; By AlanH Feb 2026


; Count will be 0 if no excel open but if no workbooks also may return same value. Nil names. 
; So a double check count /=0 and wb not ""
; Try to get or create Excel instance
  
(defun AH:openxl ( / )
(princ "\nOpening Excel...")

(setq myxl (vl-catch-all-apply 'vlax-get-or-create-object '("Excel.Application")))
  (if (vl-catch-all-error-p myxl)
    (progn
      (prompt "\nError: Could not start Excel.")
      (exit)
    )
  )
(if (=  (vlax-get-property  (vlax-get-property myXL 'WorkBooks) 'count) 0)
    (vlax-invoke-method (vlax-get-property myXL 'WorkBooks) 'Add)
)
(vla-put-visible myXL :vlax-true)
(vlax-put-property myxl 'ScreenUpdating :vlax-true)
(vlax-put-property myXL 'DisplayAlerts :vlax-true)
(princ)
)

; get active range 
(defun ah:getrangexl ( / lst UR CR RADD )
  (setq lst '())
  (setq myrange (vlax-get-property (vlax-get-property (vlax-get-property  myxl "ActiveSheet") 'UsedRange) 'address))
  (setq lst (_csv->lst58 myrange))
  (setq st (vl-string-subst "" "$" (vl-string-subst "" "$" (nth 0 lst) )))
  (setq end (vl-string-subst "" "$" (vl-string-subst "" "$" (nth 1 lst) )))
  (setq row1 (cadr (columnrow st)))
  (setq 1astrow (cadr (columnrow end)))
  (setq lastcol (car  (columnrow end)))
)

; thanks to Lee-mac for this defun 
; www.lee-mac.com
; 44 is comma 9 is tab 34 is space 58 is colon
(defun _csv->lst58 ( str / pos )
	(if (setq pos (vl-string-position 58 str))
		(cons (substr str 1 pos) (_csv->lst58 (substr str (+ pos 2))))
		(list str)
    )
)

; ColumnRow - Returns a list of the Column and Row number
; Function By: Gilles Chanteau from Marseille, France
; Arguments: 1
;   Cell$ = Cell ID
; Syntax example: (ColumnRow "ABC987") = '(731 987)
;default to "A1" if there's a problem
;-------------------------------------------------------------------------------
(defun ColumnRow (Cell$ / Column$ Char$ Row#)
  (setq Column$ "")
  (while (< 64 (ascii (setq Char$ (strcase (substr Cell$ 1 1)))) 91)
    (setq Column$ (strcat Column$ Char$)
          Cell$ (substr Cell$ 2)
    )
  )
  (if (and (/= Column$ "") (numberp (setq Row# (read Cell$))))
    (list (Alpha2Number Column$) Row#)
    '(1 1)
  )
)

; Alpha2Number - Converts Alpha string into Number
; Function By: Gilles Chanteau from Marseille, France
; Arguments: 1
;   Str$ = String to convert
; Syntax example: (Alpha2Number "ABC") = 731
;-------------------------------------------------------------------------------
(defun Alpha2Number (Str$ / Num#)
  (if (= 0 (setq Num# (strlen Str$)))
    0
    (+ (* (- (ascii (strcase (substr Str$ 1 1))) 64) (expt 26 (1- Num#)))
       (Alpha2Number (substr Str$ 2))
    )
  )
)

;;	Thanks to fixo
(defun getcell2 (row column / )
  (setq cells (vlax-get-property  (vlax-get-property myxl "ActiveSheet") "Cells"))
  (setq cell (vlax-get (vlax-variant-value  (vlax-get-property cells "Item" row column)) 'value))
)

(defun ah:chklay (layn / )
  (if (tblsearch "LAYER" layn)
    (setvar 'clayer layn)
    (command "-layer" "m" layn "")
  )
  (princ)
)


;;;;;;;;;;;;;;;;;; starts here ;;;;;;;;;;;;;;;

(defun c:topo ( / myxl X Y Z lay pt row lastrow lastcolumn)

(setvar 'pdmode 34)

(AH:openxl)
(ah:getrangexl)
(setq row 1)
(repeat (- lastrow 1)
  (setq X (getcell2 (setq row (1+ row)) 1))
  (setq Y (getcell2 row 2))
  (setq Z (getcell2 row 3))
  (setq pt (list x y z))
  (setq Lay (getcell2 row 4))
  (AH:chklay lay)
  (command "point" pt)
)
(command "zoom" "E")
(princ)
)
(c:topo)