Export polyline data (area, layer and inner text) to excel

[Anonymous]

Hello, everyone!

I am searching for a lisp that can find all closed polylines in a drawing and export to excel their following information:

-Area of each closed polyline

-Layer in which the polyline resides

-Text inside each polyline

I have a building plan with polylines enclosing each room and the corresponding room text number also inside the polyline. I am looking for a lisp that can export to excel a table like this:

Text inside polyline	Polyline Area (m2)	Layer
"Room_1.1"	10 m2	Room_Layer
"Room_1.2"	5 m2	Room_Layer
"Room_1.3"	5 m2	Room_Layer
-	20 m2	Floor_Layer

The polylines and room text numbers are located in different layers.

One of the polylines (last row in the table) encloses the whole building which means that there is more that one text inside it. For this single polyline, I only need its area and layer (text not needed).

I am fairly new to lisp creation so any help you can provide me would be highly appreciated!

Thanks in advance!

[dlanorh]

Try the attached. The csv files is saved in the same directory as the drawing, with the same name as the drawing. Items are not sorted. Once lisp is loaded type erd on the command line to run. Let me know if it needs tweaking.

[OM805]

Hello,

If given a moment, could you please help with the following? Noticed, that on some polylined spaces a return on the Handle ID comes in with a 1.7E+85 or similar. That is not a typical Handle ID but, can be resolved within the DWG by recreating the polyline or deleting a duplicate.  Would it be possible to add another column flagging this error based on the Handle ID outcome? In other words, this 1.7E+85 would be flagged as an error in the next column. Attached is an example of the current output.

Original:

https://www.cadtutor.net/forum/topic/66695-polyline-complete-details-export-to-csv/?tab=comments#com...

What I'm looking for in the end:

Space Name/Class

Area (sqft)

Perimeter (ft)

Layer

Object Handle ID

Polyline Closed?

Error Found?

Modified (does sqft and ft):

(defun c:EPD (/ all_data area csv_file d data e ent i len openfile pts s ss sstext st)
  (if (and (setq ss (ssget '((0 . "LWPOLYLINE"))))
	   (repeat (setq i (sslength ss))
	     (setq e	(ssname ss (setq i (1- i)))
		   ent	(entget e)
		   area	( / (vlax-curve-getarea e) (* 144));;/ 144 (or 12 * 12) Imperial
		   len	(vlax-curve-getdistatparam e (vlax-curve-getendparam e));;Length Perimeter
		   data	(mapcar '(lambda (d) (cdr (assoc d ent))) '(8 70 5))
		   pts	(mapcar 'cdr (vl-remove-if-not '(lambda (d) (= 10 (car d))) ent))
	     )
	     (setq all_data
		    (cons
		      (list
			(cond
			  ((null (setq sstext (ssget "_CP" pts '((0 . "TEXT"))))) "NO SPACE TEXT FOUND")
			  ((= (sslength sstext) 1) (cdr (assoc 1 (entget (ssname sstext 0)))))
			  ((substr
			     (apply
			       'strcat
			       (mapcar '(lambda (st) (strcat " | " st))
				       (vl-sort
					 (mapcar '(lambda (s) (cdr (assoc 1 (entget s))))
						 (vl-remove-if 'listp (mapcar 'cadr (ssnamex sstext)))
					 )
					 (function (lambda (a b) (< a b)))
				       )
			       )
			     )
			     4
			   )
			  )
			)
			area
			len
			(car data)
			(if (zerop (logand 1 (cadr data)))
			  "No"
			  "Yes"
			)
			(caddr data)
		      )
		      all_data
		    )
	     )
	     all_data
	   )
	   (setq csv_file
		  (getfiled "Save CSV File"
			    (strcat (getvar 'dwgprefix) (vl-filename-base (getvar 'dwgname)) ".csv")
			    "csv"
			    45
		  )
	   )
      )
    (progn (setq openfile (open csv_file "w"))
	   (write-line
	     "Space Name/Class,Area (sqft),Perimeter (ft),Layer,Polyline Closed?,Object Handle ID"
	     openfile
	   )
	   (foreach itm	(vl-sort all_data '(lambda (a b) (< (cadr a) (cadr b))))
	     (write-line
	       (strcat (car itm)
		       ","
		       (strcat (rtos (cadr itm) 2 2) "")
		       ","
		       (strcat (rtos (/ (caddr itm) 12) 2 2) "");;or /* Divide or Multiply here
		       ","
		       (cadddr itm)
		       ","
		       (cadddr (cdr itm))
		       ","
		       (last itm)
	       )
	       openfile
	     )
	   )
	   (close openfile)
	   (startapp "notepad" csv_file)
    )
  )
  (princ)
)

Regards,

Morey

[kDQCX7]

Hi dlanorh,

This is almost perfect, can you change to export multiple text similar as before?  I usually have several different text inside the rectangles and need all the text within each one.  Is there any what if I select more rectangles it can be added to the CSV rather than overwriting it?

Thank you for your help so far.

[dlanorh]

This shouldn't be a problem. I didn't think about more than one text item per rectangle, but code wise it is relatively simple.

I should be able to append to the file if it already exists, but I haven't  tried this before so don't know the pitfalls if any. I will play with it tomorrow.

[dlanorh]

Try the attached. This now collects all text within or crossing the rectangle. If the file exists it will append any new rectangles. This has only been briefly tested that the concept works.

Please note : It does not account for any duplicate rectangles between sessions. This would only be possible if the object handle was collected and displayed. If the file existed, the handles of existing objects could be accessed and checked.

[OM805]

dlanorh,

How would I go about adding an additional error found column to the following? Any help would be greatly appreciated.

Already Modified (does sqft and ft):

(defun c:EPD (/ all_data area csv_file d data e ent i len openfile pts s ss sstext st)
  (if (and (setq ss (ssget '((0 . "LWPOLYLINE"))))
	   (repeat (setq i (sslength ss))
	     (setq e	(ssname ss (setq i (1- i)))
		   ent	(entget e)
		   area	( / (vlax-curve-getarea e) (* 144));;/ 144 (or 12 * 12) Imperial
		   len	(vlax-curve-getdistatparam e (vlax-curve-getendparam e));;Length Perimeter
		   data	(mapcar '(lambda (d) (cdr (assoc d ent))) '(8 70 5))
		   pts	(mapcar 'cdr (vl-remove-if-not '(lambda (d) (= 10 (car d))) ent))
	     )
	     (setq all_data
		    (cons
		      (list
			(cond
			  ((null (setq sstext (ssget "_CP" pts '((0 . "TEXT"))))) "NO SPACE TEXT FOUND")
			  ((= (sslength sstext) 1) (cdr (assoc 1 (entget (ssname sstext 0)))))
			  ((substr
			     (apply
			       'strcat
			       (mapcar '(lambda (st) (strcat " | " st))
				       (vl-sort
					 (mapcar '(lambda (s) (cdr (assoc 1 (entget s))))
						 (vl-remove-if 'listp (mapcar 'cadr (ssnamex sstext)))
					 )
					 (function (lambda (a b) (< a b)))
				       )
			       )
			     )
			     4
			   )
			  )
			)
			area
			len
			(car data)
			(if (zerop (logand 1 (cadr data)))
			  "No"
			  "Yes"
			)
			(caddr data)
		      )
		      all_data
		    )
	     )
	     all_data
	   )
	   (setq csv_file
		  (getfiled "Save CSV File"
			    (strcat (getvar 'dwgprefix) (vl-filename-base (getvar 'dwgname)) ".csv")
			    "csv"
			    45
		  )
	   )
      )
    (progn (setq openfile (open csv_file "w"))
	   (write-line
	     "Space Name/Class,Area (sqft),Perimeter (ft),Layer,Polyline Closed?,Object Handle ID"
	     openfile
	   )
	   (foreach itm	(vl-sort all_data '(lambda (a b) (< (cadr a) (cadr b))))
	     (write-line
	       (strcat (car itm)
		       ","
		       (strcat (rtos (cadr itm) 2 2) "")
		       ","
		       (strcat (rtos (/ (caddr itm) 12) 2 2) "");;or /* Divide or Multiply here
		       ","
		       (cadddr itm)
		       ","
		       (cadddr (cdr itm))
		       ","
		       (last itm)
	       )
	       openfile
	     )
	   )
	   (close openfile)
	   (startapp "notepad" csv_file)
    )
  )
  (princ)
)

[dlanorh]

---

@OM805 wrote:

dlanorh,

How would I go about adding an additional error found column to the following? Any help would be greatly appreciated.

Already Modified (does sqft and ft):

(defun c:EPD (/ all_data area csv_file d data e ent i len openfile pts s ss sstext st)
  (if (and (setq ss (ssget '((0 . "LWPOLYLINE"))))
	   (repeat (setq i (sslength ss))
	     (setq e	(ssname ss (setq i (1- i)))
		   ent	(entget e)
		   area	( / (vlax-curve-getarea e) 144);;/ 144 (or 12 * 12) Imperial
		   len	(vlax-curve-getdistatparam e (vlax-curve-getendparam e));;Length Perimeter
		   data	(mapcar '(lambda (d) (cdr (assoc d ent))) '(8 70 5))
		   pts	(mapcar 'cdr (vl-remove-if-not '(lambda (d) (= 10 (car d))) ent))
	     )
	     (setq all_data
		    (cons
		      (list
			(cond
			  ((null (setq sstext (ssget "_CP" pts '((0 . "TEXT"))))) "NO SPACE TEXT FOUND")
			  ((= (sslength sstext) 1) (cdr (assoc 1 (entget (ssname sstext 0)))))
			  ((substr
			     (apply
			       'strcat
			       (mapcar '(lambda (st) (strcat " | " st))
				       (vl-sort
					 (mapcar '(lambda (s) (cdr (assoc 1 (entget s))))
						 (vl-remove-if 'listp (mapcar 'cadr (ssnamex sstext)))
					 )
					 (function (lambda (a b) (< a b)))
				       )
			       )
			     )
			     4
			   )
			  )
			)
			area
			len
			(car data)
			(if (zerop (logand 1 (cadr data)))
			  "No"
			  "Yes"
			)
			(caddr data)
		      )
		      all_data
		    )
	     )
	     all_data
	   )
	   (setq csv_file
		  (getfiled "Save CSV File"
			    (strcat (getvar 'dwgprefix) (vl-filename-base (getvar 'dwgname)) ".csv")
			    "csv"
			    45
		  )
	   )
      )
    (progn (setq openfile (open csv_file "w"))
	   (write-line
	     "Space Name/Class,Area (sqft),Perimeter (ft),Layer,Polyline Closed?,Object Handle ID,ID Error"
	     openfile
	   )
	   (foreach itm	(vl-sort all_data '(lambda (a b) (< (cadr a) (cadr b))))
	     (write-line
	       (strcat (car itm)
		       ","
		       (strcat (rtos (cadr itm) 2 2) "")
		       ","
		       (strcat (rtos (/ (caddr itm) 12) 2 2) "");;or /* Divide or Multiply here
		       ","
		       (cadddr itm)
		       ","
		       (cadddr (cdr itm))
		       ","
		       (last itm)
               ","
               ;(if (test for error) "Yes" "No")
	       )
	       openfile
	     )
	   )
	   (close openfile)
	   (startapp "notepad" csv_file)
    )
  )
  (princ)
)

---

I have amended the above lisp to add an extra column ("ID Error") and a line to add "Yes" or "No" to the column depending on a test (if (test for error) "Yes" "No"). This line is commented out at present. The Handle ID is a string but I have no idea as yet how it should be tested.

I have also removed spurious stuff in your area calc (* 144) should be just 144.

I will have a think about the how this should be tested

[OM805]

dlanorh,

Noticed, that on some polylined spaces a return on the Handle ID comes in with a 1.7E+85 or similar. That is not a typical Handle ID but, can be resolved within the DWG by recreating the polyline or deleting a duplicate.  Would it be possible to add another column flagging this error based on the Handle ID outcome? In other words, this 1.7E+85 would be flagged as an error. Attached is quick example.

[dlanorh]

I got all this from a previous post. 😁

I don't know why it is returning a scientific decimal number as a string instead of a hexadecimal as a string.

The test needs to distinguish between the two and unfortunately the (Ee) is also used in hexadecimal so it means searching the string for a decimal point and/or +/-. I just need time to write and test a simple piece of code.

[OM805]

dlanorh,

Just wanted to clarify...  Thanks for all of your help.

Regards,

OM

[kDQCX7]

Hi dlanorh,

You are brilliant 😁.   I am in debt to you a beverage of your choice.  I will give it a thorough test and let you know how I go, so far perfect. 

You have been too kind, your assistance is greatly appreciated.  Thank you.

[dlanorh]

OK, try this version. 🤞

(defun c:EPD (/ all_data area csv_file d data e ent i len openfile pts s ss sstext st)
  (if (and  (setq ss (ssget '((0 . "LWPOLYLINE"))))
            (repeat (setq i (sslength ss))
              (setq e (ssname ss (setq i (1- i)))
                    ent  (entget e)
                    area ( / (vlax-curve-getarea e) 144);;/ 144 (or 12 * 12) Imperial
                    len (vlax-curve-getdistatparam e (vlax-curve-getendparam e));;Length Perimeter
                    data (mapcar '(lambda (d) (cdr (assoc d ent))) '(8 70 5))
                    pts (mapcar 'cdr (vl-remove-if-not '(lambda (d) (= 10 (car d))) ent))
              )
              (setq all_data
                (cons
                  (list
                    (cond ( (null (setq sstext (ssget "_CP" pts '((0 . "TEXT"))))) "NO SPACE TEXT FOUND")
                          ( (= (sslength sstext) 1) (cdr (assoc 1 (entget (ssname sstext 0)))))
                          ( (substr
                              (apply
                                'strcat
                                  (mapcar '(lambda (st) (strcat " | " st))
                                    (vl-sort (mapcar '(lambda (s) (cdr (assoc 1 (entget s)))) (vl-remove-if 'listp (mapcar 'cadr (ssnamex sstext))))
                                      (function (lambda (a b) (< a b)))
                                    )
                                  )
                              )
                              4
                            )
                          )
                    )
                    area
                    len
                    (car data)
                    (if (zerop (logand 1 (cadr data))) "No" "Yes")
                    (caddr data)
                  )
                  all_data
                )
              )
              all_data
            )
            (setq csv_file (getfiled "Save CSV File" (strcat (getvar 'dwgprefix) (vl-filename-base (getvar 'dwgname)) ".csv") "csv" 45))
      )
    (progn (setq openfile (open csv_file "w"))
      (write-line "Space Name/Class,Area (sqft),Perimeter (ft),Layer,Polyline Closed?,Object Handle ID,ID Error" openfile)
      (foreach itm (vl-sort all_data '(lambda (a b) (< (cadr a) (cadr b))))
        (write-line
          (strcat (car itm)
            ","
            (strcat (rtos (cadr itm) 2 2) "")
            ","
            (strcat (rtos (/ (caddr itm) 12) 2 2) "");;or /* Divide or Multiply here
            ","
            (cadddr itm)
            ","
            (cadddr (cdr itm))
            ","
            (last itm)
            ","
            (if (vl-every '(lambda (x) (or (< 47 x 58) (< 64 x 71))) (vl-string->list itm)) "No" "Yes")
          )
          openfile
        )
      )
      (close openfile)
      (startapp "notepad" csv_file)
    )
  )
  (princ)
)

[dlanorh]

The code above is incorrect, but I am unable to edit and save. I didn't adjust my test code to fit. Please replace

(if (vl-every '(lambda (x) (or (< 47 x 58) (< 64 x 71))) (vl-string->list itm)) "No" "Yes")

with

(if (vl-every '(lambda (x) (or (< 47 x 58) (< 64 x 71))) (vl-string->list (last itm))) "No" "Yes")

[OM805]

dlanorh,

Gave it is shot and it does not flag the yes errors.   Attached is the output example. 2 of the 5 should have been yes.

Regards,

OM

[dlanorh]

Is this with the code amended as in post 93 or just using the code in post 92? As I cannot reproduce this error, it is difficult to test.

[dlanorh]

The more I look at this, the more I am convinced that this is a translation error from AutoCAD to Excel or Notepad and that it is seeing the Handles as numbers and not strings.

It is not picking up an error, as there is no error but somewhere the hexadecimal string is being interpreted as a decimal scientific number since 1.70E+83 could be 17E82.

[OM805]

dlanorh,

After further investigation... When ever a Handle ID with the ending two digits shows up as both numeric, signifies an error. The last two digits should be alpha/numeric as shown below.  Hope that helps. 

CSV Results:

Space Name/Class,Area (sqft),Perimeter (ft),Layer,Polyline Closed?,Object Handle ID,ID Error
100A CHASE | Void,3.74,8.67,A-AREA,Yes,17EC5,No <--- Correct: Last two digits are alpha/numeric (C5)
101A CHASE | Void,12.94,15.98,A-AREA,Yes,17EC3,No <--- Correct: Last two digits are alpha/numeric (C3)
102A CHASE | Void,13.78,14.90,A-AREA,Yes,17E84,No  <--- Error: Last two digits are both numeric/numeric (84)

[dlanorh]

Yes and it should only happen when it is preceded by an "E". Is this happening when writing from AutoCAD to CSV or when it goes from CSV to Excel?

[OM805]

That would be AutoCAD to CSV (17E84).  From CSV to Excel it would look like this 1.7E+84.

[dlanorh]

So the problem is CSV to Excel.

Going back to your original code, try this simple fix

(defun c:EPD (/ all_data area csv_file d data e ent i len openfile pts s ss sstext st)
  (if (and (setq ss (ssget '((0 . "LWPOLYLINE"))))
     (repeat (setq i (sslength ss))
       (setq e  (ssname ss (setq i (1- i)))
       ent  (entget e)
       area ( / (vlax-curve-getarea e) (* 144));;/ 144 (or 12 * 12) Imperial
       len  (vlax-curve-getdistatparam e (vlax-curve-getendparam e));;Length Perimeter
       data (mapcar '(lambda (d) (cdr (assoc d ent))) '(8 70 5))
       pts  (mapcar 'cdr (vl-remove-if-not '(lambda (d) (= 10 (car d))) ent))
       )
       (setq all_data
        (cons
          (list
      (cond
        ((null (setq sstext (ssget "_CP" pts '((0 . "TEXT"))))) "NO SPACE TEXT FOUND")
        ((= (sslength sstext) 1) (cdr (assoc 1 (entget (ssname sstext 0)))))
        ((substr
           (apply
             'strcat
             (mapcar '(lambda (st) (strcat " | " st))
               (vl-sort
           (mapcar '(lambda (s) (cdr (assoc 1 (entget s))))
             (vl-remove-if 'listp (mapcar 'cadr (ssnamex sstext)))
           )
           (function (lambda (a b) (< a b)))
               )
             )
           )
           4
         )
        )
      )
      area
      len
      (car data)
      (if (zerop (logand 1 (cadr data)))
        "No"
        "Yes"
      )
      (caddr data)
          )
          all_data
        )
       )
       all_data
     )
     (setq csv_file
      (getfiled "Save CSV File"
          (strcat (getvar 'dwgprefix) (vl-filename-base (getvar 'dwgname)) ".csv")
          "csv"
          45
      )
     )
      )
    (progn (setq openfile (open csv_file "w"))
     (write-line
       "Space Name/Class,Area (sqft),Perimeter (ft),Layer,Polyline Closed?,Object Handle ID"
       openfile
     )
     (foreach itm (vl-sort all_data '(lambda (a b) (< (cadr a) (cadr b))))
       (write-line
         (strcat (car itm)
           ","
           (strcat (rtos (cadr itm) 2 2) "")
           ","
           (strcat (rtos (/ (caddr itm) 12) 2 2) "");;or /* Divide or Multiply here
           ","
           (cadddr itm)
           ","
           (cadddr (cdr itm))
           ","
           (strcat "\'" (last itm))
         )
         openfile
       )
     )
     (close openfile)
     (startapp "notepad" csv_file)
    )
  )
  (princ)
)

This should precede the hex string with an apostrophe and may force it to a string in excel