Exctracting measurements/text to excel

[Youri-is-hier]

Can you make a macro that extracts the data you need to excel?

I made a macro that explodes the drawing so you can get all the data since rotated dimensions don’t show their value with data extraction. Then that macro does a data extraction with a template that you have to select yourself and only has a few options selected, this template only shows the data needed from the drawing.

Then after you save your data extraction to excel the macro will undo the explosion so it won’t destroy the drawing.

Would it be possible to automate the data extraction in the macro so you don’t have to find the same template over and over again and it makes the excel without you having to do anything about it.

Or should this be done in a piece of code instead, like making  some autolisp code that automates the process of extracting measurements from an autocad drawing to excel sheet.

Or is there a way better option that I haven’t even considered yet?

This is the macro I was using;

^C^Cexplode;all;;dataextraction;u;u

Thank you for any help you might have about this matter!

[Moshe-A]

@Youri-is-hier hi,

basiclly exploding all objects is bad process. post your dwg for better assessment.

Moshe

[Youri-is-hier]

The reason for exploding the drawing is so the Data Extraction can use the rotated dimensions, when they are exploded they turn into an MTEXT, and i can find that in the data extraction, at its core i would like to get all dimensions needed to excel with automation so you only have to press a button and it will give you a list of dimensions you want to get in an excel sheet

[Sea-Haven]

Ok a dumpit of the rotated dims reveals Measurement (RO) = 0.781016258190123 

(* 0.25 pi) = 0.785398163397448 radians

What was interesting looking deeper was "Angle in UCS XY plane: 45.2510647" hence not true 45. 

(setq obj (vlax-ename->vla-object (car  (entsel "Pick angle dim"))))
(princ (vla-get-measurement obj)) ; answer in radians

So a lisp version of dataextract will do what you want.

For all data extract no 2 are the same so a custom version is required, you need to supply what is required and a sample dwg.

[Youri-is-hier]

Ive been trying to make a lisp that does this.

So far ive got it that it uses a template, but now i dont know how to make it save as a new template with the name of the drawing and location.

If i use the code now i can use the template but it saves it as the same and then it won't use the new data.

How can i make it so it uses a template but then saves it as a new .dxe so it makes a new excel file instead of saving over the old one with the old data?

(setq template-file "C:/Users/youri.wiertz/OneDrive - Filtration Group/Desktop/TEST/extraheer.dxe")

(setq drawing-name (getvar 'dwgname))

(setq drawing-path (vl-filename-directory drawing-name))

(setq create-template (strcase (getstring "Create a new template? [Y/N]: ")))

(if (equal create-template "Y")

  (progn

    (setq output-file (getfiled "Save as" drawing-path "dxe" 4))

    (command "._-DATAEXTRACTION" "_template" template-file "_file" output-file)

    (princ (strcat "Data extraction completed. New template file saved as \"" output-file "\"."))

  )

  (progn

    (setq output-file (strcat drawing-path (vl-filename-base drawing-name) ".dxe"))

    (command "._-DATAEXTRACTION" "_template" template-file "_file" output-file)

    (princ (strcat "Data extraction completed. Template file saved as \"" output-file "\"."))

  )

)

[Sea-Haven]

I dont use Dataextraction like many others use a lisp and make either a csv file or write direct to Excel filling in cells.

In the lisp need to know what you want to export. The same as what your putting into the dataextract.

This is what is desired by someone else as the result. Look at 1st line. This is in my To do list its a custom output.

 

[calderg1000]

Regards @Youri-is-hier 

Try this code...

Edit your angular dimension style to display with higher decimal precisión

(defun c:Rpang (/ f n d of s a ag)
  (setq
    f  (getfiled "ANGLES REPORT" "" "csv" 1)
    d  (getint "\nEnter number of decimals of the Report...:")
    n  1
    of (open f "w")
  )
  (while
    (setq s (car (entsel (strcat "\nSelect Angular Measurement: " (itoa n))))
    )
     (setq
       a  (/ (* 180 (getpropertyvalue s "AngularMeasurement")) pi)
       ag (rtos a 2 d)
     )
     (write-line (strcat "Ang. " (itoa n) ";" ag) of)
     (setq n (1+ n)
     )
  )
  (close of)
  (startapp "explorer" f)
)

[Youri-is-hier]

Okay so now ive been using a script, but i still want to change it since it prompts you with another selection after it selects everything from a layer, and with the undo function at the end, that can cause an error that can delete a measurement.

Here is the code;

; This code was made by Youri.W (date: June 6 2023)

; Explanation: This code is to extract data from your autocad drawing and put it into excel, it will first get to the layer you want

; add any revisions above here as to make it easier to troubleshoot the piece of code, because it may or may not be hard to find why this code may have an error in the future

;

;add any further comments here:

;

(defun c:Selex () ; Here is the name of my Visual Lisp, This is changeable to whatever you would want the name of the command to be.

  ; Here it will select all measurements on the layer 'Maatvoering_Export' the orange layer, it does this by first making the layer 'Maatvoering_Export' as the current layer and then it selects everything on the current layer, after it has everything on the layer selected it will explode the things that are selected, the reason for this is that it cant get rotated and angular dimensions. So by exploding the dimensions it will turn them into something called MTexts, and Mtexts can be exported to excel.

  (setq layerName "Maatvoering_Export") ; This can be any layer you want but it has to exist in the drawing just change the name between the "..."

  (command "-LAYER" "M" layerName "") ; This command makes the name you put in the current layer, M is for make current layer

  (sssetfirst nil (ssget "X" (list (cons 8 (getvar "clayer"))))) ; selects everything

  (command "_.EXPLODE") ; This explodes everything on the curent layer so it can be brought into excel

  (sssetfirst nil (ssget "X" (list (cons 8 (getvar "clayer")))))

  (princ)

  ; Here it will extract your selected text and mtexts and it will put it into an excel file

  (defun _sortsel (sel cl)

    (mapcar 'cdr

            (vl-sort

              (mapcar '(lambda (e)

                         (redraw e 3)

                         (list (_dxf 10 e) (_dxf 1 e) e))

                      (vl-remove-if 'listp (mapcar 'cadr (ssnamex sel))))

              '(lambda (a b) (< (caar a) (caar b))))))

  (defun _dxf (d e)

    (cdr (assoc d (entget e))))

  (defun _get_text_strings (ent)

    (if (eq (cdr (assoc 0 (entget ent))) "MTEXT")

        (vla-get-textstring (vlax-ename->vla-object ent))

        (_dxf 1 ent)))

    (setq collection nil

         inc 0

         _comma (lambda (s) (strcat s))) ; i want to change this

  (while (progn

           (princ (strcat "\nSelect text or mtext for column " (itoa (setq inc (1+ inc))) " "))

           (setq fc (ssget '((0 . "TEXT,MTEXT")))))

    (setq collection (cons (_sortsel fc inc) collection)))

; This is the excel part

  (if (setq collection (reverse collection))

      (progn

        (setq csvfile (strcat (getvar 'dwgprefix) (vl-filename-base (getvar 'dwgname)) ".csv")) ; makes the excel.csv file, it uses the same name as your drawing and places it in the same map

        (setq opf (open csvfile "w"))

        (while (and (car collection)

                    (setq data (mapcar 'car collection)))

          (setq row (apply 'strcat (mapcar '_comma (mapcar '_get_text_strings (mapcar 'cadr data)))))

          (write-line row opf)

          (foreach itm (mapcar 'cadr data) (redraw itm 4))

          (setq collection (mapcar 'cdr collection)))

        (close opf) ; this can give an error if you used the code once and have to excel file open in the background

        (startapp "explorer" csvfile)

        (my-undo 3) ; this repeats the undo function 3 times

        (princ "\nUndo operation performed.")

        (princ)

      )

  )

)

; Here is the undo function, the reason i added an undo function is because i expoded all the dimensions on the layer, and you dont want them to be exploded for multiple reasons.

; Also it repeats the undo 3 times as stated above here

(defun my-undo (count)

  (repeat count

    (command "_UNDO") ; the undo can cause errors like deleting a measurement so id advise to first look at the excel for a second and then get back to the drawing and press enter

  )

) ; end of the code