Jump to content

export data point to excel file

iyant

By iyant
in AutoLISP, Visual LISP & DCL

 Share

Recommended Posts

iyant Apprentice

iyant

Posted
Posted

need help

how do I export the data points XY of CAD using LISP into excel file for example XY.xls

and placed into a specific cell sheet, eg X = Y = cell cell c2 d2

 

thank you

hanhphuc Mentor

hanhphuc

Posted
Posted (edited)
iyant said:
need help

how do I export the data points XY of CAD using LISP into excel file for example XY.xls

and placed into a specific cell sheet, eg X = Y = cell cell c2 d2

 

thank you

 

The easiest way is to

save as *.csv comma seperated format

then open with excel

example: 

;V1.1:  04/12/2014
;PXYZ test.csv saved to dwgprefix
;add numbering PXYZ format
;index i 1+ incremental
;notified by alert
;optional: add Text numbering
;optional: open notepad
;optional: open directory


(vl-load-com)

(defun c:P2F ( / *error* p i ss fn pt )
;hanhphuc 2014
  (defun *error* (msg)
    
    (if	(not (wcmatch (strcase msg) "*CANCEL*,*EXIT*"))
      
      (princ (strcat "\nError: " msg))
      
    )
    
    (if	p
      
      (close p)
      
    )

    (princ)
    
  )
  
  (if
    (and

      (setq i  0
	    
	    ss (ssget ":L" '((0 . "POINT")))
	    
	   )
      
	   (setq fn

		  (strcat (getvar "dwgprefix")

			  "PXYZ test.csv") ; rename you file
		 )
      
	   (setq p (open fn "w"))
      
      )
    
    (progn
					;(setq i 0) 
      (repeat

	(sslength ss)
	
	(

	 (lambda (str)
	   
	   (foreach x

		    (setq pt

			   (vlax-get
			     
				 (vlax-ename->vla-object (ssname ss i))
				 
				 'coordinates
				 
			       )
			  
		      )
	     
	     (setq str (strcat str (rtos x 2) ","))
	     
	   )
	   
	   (write-line 
       
       (strcat (itoa (setq i (1+ i))) "," str) 
       
       p)
	   
	 )
	  
	  ""
	  
	)
	
	(entmakex

	  (list

	    	'(0 . "TEXT")
		
	     	(cons 1 (itoa i))
		
		(cons 10 pt)
		
		(8 . "EXPORTED")
		
		(cons 40 (getvar 'TEXTSIZE))
		
	    )
	  
	)
	
      )
      
      (if p
	
	(close p)
	
      )
      
      (alert
	
	(strcat
	  
	  "\nSyabas! Saved in \""
	  
	  fn
	  ;"\"\nOK -> \"PXYZ test.csv\" -> Right-Click -> Open With -> EXCEL"
	)
      )
      
;;;(command "_SHELL" (strcat "explorer \"" (getvar "dwgprefix") "\"")) ;<-- optional
      
      (vl-cmdf "_START" (strcat "EXCEL \"" fn "\""))
      
      (startapp "notepad" fn)
      
      ;; <--optional open as notepad preview

    )
    
    (alert "\n:-( knp xde POINT ?! ")
    
  )

  (princ)

)
 

 

As i notice if you are using Land Desktop, not sure this works in LDD? why not using utility export point?

try look at LDD forum

 

HTH

Edited by hanhphuc
color tags removed
stevesfr Rising Star

stevesfr

Posted
Posted
The easiest way is to

save as *.csv comma seperated format

then open with excel

example:

(defun c:TEST (/ *error* p i ss fn) 
;hanhphuc 2014
(defun *error* (msg)
(if (not (wcmatch (strcase msg) "*CANCEL*,*EXIT*"))
(princ (strcat "\nError: " msg))
) ;_ end of if
(if p
(close p)
) ;_ end of if
(princ)
) ;_ end of defun

(if (setq ss (ssget ":L" '((0 . "POINT"))))
(progn (setq p (open (setq fn [color="red"](strcat (getvar "tempprefix") "XY test.csv")[/color])"w")) ; you can change file name 
(repeat (setq i (sslength ss))
((lambda (str)
(foreach x (vlax-get (vlax-ename->vla-object (ssname ss (setq i (1- i)))) 'coordinates)
(setq str (strcat str (rtos x) ","))
) ;_ end of foreach
(write-line str p)
) ;_ end of lambda
""
)
) ;_ end of repeat
) ;_ end of progn
) ;_ end of if
(close p)
(alert (strcat "\nSaved in "fn)) ; <-- notify file name
(princ)
) ;_ end of defun

 

As i notice if you are using Land Desktop, not sure this works in LDD? why not using utility export point?

try look at LDD forum

 

HTH

 

using AC2008 I get the following error from the program:

Select objects:

Error: ActiveX Server returned the error: unknown name: "COORDINATES"

 

I believe this is a bug somehow undefined ? or is it not available in AC2008?

Tharwat Grand Master

Tharwat

Posted
Posted

returned the error: unknown name: "COORDINATES"

Try to add (vl-load-com) to the program and test it again .

stevesfr Rising Star

stevesfr

Posted
Posted
Try to add (vl-load-com) to the program and test it again .

 

Tharewat, thanks for the reply, but adding (vl-load-com) did not help the program to execute.

Perhaps others have had success while I am doing something wrong.

JSYoung81 Apprentice

JSYoung81

Posted
Posted 

(DEFUN C:Exportpnt (/       FILE    FILENAME	       ANS     CNT
	       PT      X       Y       Z       LINE    DESC
	       LINECOUNTER     LINEDATA	       LEN     OSM
	      )
 (SETQ OSM (GETVAR "OSMODE"))

 (COMMAND "OSMODE" 0)
 ;;
 ;; THIS WILL SET THE FILE NAME WE ARE WRITTING TO AS WELL AS CHECK
 ;; TO MAKE SURE THAT WE ARE WRITTING AT THE END OF THE FILE
 ;;
 (SETQ LINECOUNTER 0)
 (SETQ FILE (GETSTRING "\nNAME OF POINTS FILE TO OPEN:..."))
 (SETQ FILENAME (OPEN FILE "A"))
 (CLOSE FILENAME)
 (SETQ FILENAME (OPEN FILE "R"))
 (WHILE
   (SETQ LINEDATA (READ-LINE FILENAME))
    (SETQ LEN (STRLEN LINEDATA))
    (SETQ LINECOUNTER (+ 1 LINECOUNTER))
 )
 (CLOSE FILENAME)
 (SETQ FILENAME (OPEN FILE "A"))
 (INITGET "Yy Nn")
 (SETQ
   ANS	(GETKWORD "\n ADD DESCRIPTIONS? Yes OR No ?:...")
   CNT	(GETINT "\nENTER NUMBER OF POINTS TO EXPORT:...")
   PT	(GETPOINT "\nSELECT POINT TO EXTRACT:....")
 )


 ;;
 ;; THIS BEGINS THE REPEAT LOOP TO EXTRACT THE POINT INFORMATION
 ;;
 (REPEAT CNT
   (IF
     ( = ANS "Yy")
      (SETQ DESC (GETSTRING "\nENTER DESCRIPTION:..."))
   )
   (SETQ
     X		  (RTOS (CAR PT))
     Y		  (RTOS (CADR PT))
     Z		  (RTOS (CADDR PT))
     LINECOUNTER (+ 1 LINECOUNTER)
   )
   (IF	(= ANS "Yy")
     (SETQ LINE (STRCAT (ITOA LINECOUNTER) "," Y "," X "," Z "," DESC))
     (SETQ LINE (STRCAT (ITOA LINECOUNTER) "," Y "," X "," Z))
   )
   (WRITE-LINE LINE FILENAME)
   (SETQ PT (GETPOINT "\nSELECT NEXT POINT TO EXTRACT:..."))
 )					;END OF REPEAT LOOP

 (CLOSE FILENAME)

 ;; RESET THE OSNAP MODE

 (COMMAND "OSMODE" OSM)
 (PRINC)
)

 

Will save the file in the directory of the DWG

BIGAL Grand Master

BIGAL

Posted
Posted

The routine looks for Autocad "Points" if they are in fact LDD Points then it will probably not work, as mentioned above need different code for LDD.

JSYoung81 Apprentice

JSYoung81

Posted
Posted
The routine looks for Autocad "Points" if they are in fact LDD Points then it will probably not work, as mentioned above need different code for LDD.

 

You just click and it will store the XY values, doesn't have to be a cogo point.

Tharwat Grand Master

Tharwat

Posted
Posted

how do I export the data points XY of CAD using LISP into excel file for example XY.xls

and placed into a specific cell sheet, eg X = Y = cell cell c2 d2

 

Have a go with this program and let me know .

 

(defun c:Test (/ i s e p o f)
 ;; Tharwat 04.12.2014	;;
 (princ "\n Select points to export coordinates to new excel file ")
 (if (and (setq s (ssget '((0 . "POINT"))))
          (setq f (getfiled "Save to " (getvar 'DWGPREFIX) "csv" 1))
          (setq o (open f "w"))
     )
   (progn
     (repeat 2 (write-line "" o))
     (repeat (setq i (sslength s))
       (setq e (entget (ssname s (setq i (1- i))))
             p (cdr (assoc 10 e))
       )
       (write-line
         (strcat ";;" (rtos (car p) 2) ";" (rtos (cadr p) 2))
         o
       )
     )
     (close o)
   )
 )
 (princ)
)

hanhphuc Mentor

hanhphuc

Posted
Posted

Thanks Tharwat,BIGAL,JSYoung81,stevesfr for assisting thread very appreciated. :thumbsup:

i slept early last night :oops:

 

Error: ActiveX Server returned the error: unknown name: "COORDINATES"

sorry. missing (vl-load-com) code updated in post#2

note: this code only Exports acad AcDbPoint, NOT AECC point!

 

The routine looks for Autocad "Points" if they are in fact LDD Points then it will probably not work, as mentioned above need different code for LDD.

 

i came across your old thread

AECC_COGO_POINT , should be different method property for LDD & C3D

 

You just click and it will store the XY values, doesn't have to be a cogo point.

hi JSYoung81 we meet again :)

yes your code works because it just creates new incremental points, here are similar threads by

Lee

VVA

  • 1 month later...
iyant Apprentice

iyant

Posted
  • Author
Posted
Have a go with this program and let me know .

 

(defun c:Test (/ i s e p o f)
 ;; Tharwat 04.12.2014	;;
 (princ "\n Select points to export coordinates to new excel file ")
 (if (and (setq s (ssget '((0 . "POINT"))))
          (setq f (getfiled "Save to " (getvar 'DWGPREFIX) "csv" 1))
          (setq o (open f "w"))
     )
   (progn
     (repeat 2 (write-line "" o))
     (repeat (setq i (sslength s))
       (setq e (entget (ssname s (setq i (1- i))))
             p (cdr (assoc 10 e))
       )
       (write-line
         (strcat ";;" (rtos (car p) 2) ";" (rtos (cadr p) 2))
         o
       )
     )
     (close o)
   )
 )
 (princ)
)

 

 

 

 

Tharwat

user-offline.png

This code can be changed in order to automatically enter into an excel file that we have to make before?

Tharwat Grand Master

Tharwat

Posted
Posted

I don't see any respect to reply after two months to people whom trying to help you for free . :glare:

  • 9 years later...
CADWORKER Enthusiast

CADWORKER

Posted
Posted
On 12/4/2014 at 7:43 AM, Tharwat said:

 

Have a go with this program and let me know .

 

  

(defun c:Test (/ i s e p o f)
 ;; Tharwat 04.12.2014	;;
 (princ "\n Select points to export coordinates to new excel file ")
 (if (and (setq s (ssget '((0 . "POINT"))))
          (setq f (getfiled "Save to " (getvar 'DWGPREFIX) "csv" 1))
          (setq o (open f "w"))
     )
   (progn
     (repeat 2 (write-line "" o))
     (repeat (setq i (sslength s))
       (setq e (entget (ssname s (setq i (1- i))))
             p (cdr (assoc 10 e))
       )
       (write-line
         (strcat ";;" (rtos (car p) 2) ";" (rtos (cadr p) 2))
         o
       )
     )
     (close o)
   )
 )
 (princ)
)
 

 

cad we add z "x,y,z" and layer to this code.

Steven P Grand Master

Steven P

Posted
Posted

Z is easy, add  (rtos (caddr p) 2) to this line

  

(strcat ";;" (rtos (car p) 2) ";" (rtos (cadr p) 2))

 

to be

  

(strcat ";;" (rtos (car p) 2) ";" (rtos (cadr p) 2) ";" (rtos (caddr p) 2))

 

Layer you'll need to add something line 

(setq lay (cdr (assoc 8 e)) )

 

maybe in here 

       (setq e (entget (ssname s (setq i (1- i))))
             p (cdr (assoc 10 e))
             lay (cdr (assoc 8 e)) ;;Layer
       )

 

and add lay into the (strcat..... ) line above like this:

  

(strcat ";;" (rtos (car p) 2) ";" (rtos (cadr p) 2) ";" (rtos (caddr p) 2) ";" lay)

 

  • Thanks 1

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Unfortunately, your content contains terms that we do not allow. Please edit your content to remove the highlighted words below.
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • Create New...