Autolisp Table Export

[seegs]

Hi all,

I need a lisp shortcut to save me a whole lot of data entry.

Basically I have 100 or so drawings, very similar in format. In each drawing there are some tables, with one of the tables containing some key information we need to extract. My thinking was that instead of typing them out all manually I could use lisp and EZscript to open each drawing, select the correct table (using a crossing window?) export the table to a spreadsheet in a specific folder, close the drawing and move on to the next one. (So there would be as many spreadsheets as drawings.)

Thats the first challenge, the second is to combine all those spreadsheets to include all the information. I would then cull out all the irrelevent data.

Too hard?

[BlackBox]

Have you tried using the DATAEXTRACTION command?

[seegs]

Yes, just now. I need it to function through autolisp though, and as far as i can see, DATAEXTRACTION always gives you dialog boxes. I really thought there would be a lisp equivilant to manually right clicking the table and clicking "export"

[seegs]

omg im almost there its as dumb as (command "tableexport" "300,233") but the stupid dialog box keeps coming up no matter what i do! I was hoping (command "tableexport" "300,233" "S:\Filepath") would do but no

[BlackBox]

I'm not on my work laptop, so I don't have AutoCAD to test; but try setting CMDDIA = 0 to 'disable' the dialog... It's worth a shot.

[Lee Mac]

Correct me if I'm wrong, but I thought you could process multiple drawings using DataExtraction.

[seegs]

hmm the CMDDIA does not work, but I see what you mean about DATAEXTRACTION and yes you can set up multiple drawings, so no need for LISP. However when i try to use it, it doesn't seem to extract the actual content from the tables! - everything but. I even made a small lsp to copy the tables and explode them, to then extract the mtext. The only problem is, all the information is jumbled up.

[seegs]

It seems silly you can scan for MText content, but not TABLE content. - Only crap like Lintetype, colour, etc

[irneb]

Yep, DataExtract creates tables from other objects (and in the same process can generate CSV/XLS files). But if you already have a table created from something else it's not as simple.

 

Painful that the TableExport doesn't even allow for FileDia's value. I'd have imagined at least that! And there's no - prefixed version either. So AFAICT your only solution is to create a custom Lisp which would get hold of all the table's cells and then write their values to a CSV file in order. I have seen a lisp which does this for normal Text entities placed in columns ... can't remember the name just now.

[irneb]

Back to the original post, try this command-line version rewrite of the TableExport command:

(vl-load-com)

(defun c:-TableExport (/ en eo fname f row col str)
 (princ "\nSelect a table:")
 (while (not eo)
   (if (setq en (entsel "\nSelect a table: "))
     (progn
       (setq eo (vlax-ename->vla-object (car en)))
       (if (not (eq (vla-get-ObjectName eo) "AcDbTable"))
         (progn
           (princ "Object selected is not a table.")
           (setq eo nil)
         )
       )
     )
     (princ "Nothing selected.")
   )
 )
 (if (and (setq fname (getstring "Enter file for exported data: "))
          (setq f (open fname "w"))
     )
   (progn
     (setq row -1)
     (while (< (setq row (1+ row)) (vla-get-Rows eo))
       (setq col -1 str "")
       (while (< (setq col (1+ col)) (vla-get-Columns eo))
         (setq str (strcat str ",\"" (vla-GetText eo row col) "\""))
       )
       (princ (substr str 2) f)
       (princ "\n" f)
     )
     (close f)
     (princ "\nTable sucsessfully exported.")
   )
 )
 (princ)
)

Note the dash/minus-sign prefix to the command.

 

Only problem is you can't call it directly from lisp, but with some mods even that might be possible.

[seegs]

Thankyou all for your input I really appreciate it. irneb your lsp is perfect, almost. I just put this in: (setq drawingname (strcat (substr (getvar "dwgname") 1 10) ".txt" )) which works fine. The only thing is is that the files only occasionally show up in my documents, and sometimes in my scripts folder (I made a small script to run the lisp and give the coordinates of the table) but half the time nowhere . My noobness is probably showing here. maybe it could be tweaked to have a filepath and maybe two coordinates for a crossing window? as i'm running it through scriptpro so there is no user input.

 

Thanks again

[seegs]

Just to push my luck a bit more: I didn't think far enough ahead as to try and put in the drawing name as a reference to each line of data. What I really need is to have the drawing number in all rows of a new column. This is because at the end, what i do is go into word and insert all .txt files then save the word file as a .txt file, open in excel using comma deliminated..sfjnryjf and sort the data. (ive spent a bit too much time on this good/bad thing theres not much work to do at the moment)

[irneb]

OK, here's a pure lisp version:

(vl-load-com)

(defun ReadList (lst /)
 (cond
   ((= (type lst) 'Str) (ReadList (read (strcat "(" (vl-string-translate ",;" "  " lst) ")"))))
   ((= (type lst) 'Sym) (eval lst))
   ((= (type lst) 'List) (mapcar (function ReadList) lst))
   (t lst)
 )
)

(defun SelectObjects (pt filter / ss n lst)
 (if pt
   (progn
     (setq pt (ReadList pt))
     (if (= (type pt) 'List)
       (cond
         ((and (>= (length pt) 2) (vl-every (function (lambda (i) (member (type i) '(Int Real)))) pt))
          (setq n  (list (/ (getvar 'ViewSize) (* (getvar 'PickBox) 80.0)) 0.0)
                n  (cons (car n) n)
                ss (ssget "C" (mapcar '- pt n) (mapcar '+ pt n) filter)
          )
         )
         ((vl-every (function
                      (lambda (i)
                        (and (= (type i) 'List) (vl-every (function (lambda (j) (member (type j) '(Int Real)))) i))
                      )
                    )
                    pt
          )
          (cond
            ((= (length pt) 1) (setq lst (SelectObjects (car pt) filter)))
            ((= (length pt) 2) (setq ss (ssget "C" (car pt) (cadr pt) filter)))
            (t (setq ss (ssget "CP" pt filter)))
          )
         )
       )
     )
   )
   (setq ss (ssget "X" filter))
 )
 (if ss
   (progn (setq n (sslength ss)) (while (>= (setq n (1- n)) 0) (setq lst (cons (ssname ss n) lst))))
 )
 lst
)

(defun TableExport (pt fname prefix / lst eo f row col str)
 (if (and (setq lst (SelectObjects pt '((0 . "ACAD_TABLE"))))
          (setq f (open fname "a"))
     )
   (progn
     (foreach en lst
       (setq eo  (vlax-ename->vla-object en)
             row -1
       )
       (while (< (setq row (1+ row)) (vla-get-Rows eo))
         (setq col -1
               str ""
         )
         (while (< (setq col (1+ col)) (vla-get-Columns eo))
           (setq str (strcat str ",\"" (vla-GetText eo row col) "\""))
         )
         (setq str (strcat ",\"" (vla-get-Handle eo) "\"" str "\n"))
         (if prefix
           (setq str (strcat ",\"" prefix "\"" str))
         )
         (princ (substr str 2) f)
       )
     )
     (close f)
   )
 )
)

You can call it with a lisp call in the script like so (note each line gets a prefix column, here I'm sending the DWG filename as prefix):

(TableExport "300,223" "C:\\test.csv" (getvar 'DwgName))

That should select all tables at the point specified. The point can be entered as text like the example, or you could send as a list of XYZ values like thus:

(TableExport '(300 223) "C:\\test.csv" (getvar 'DwgName))

Or using 2 points as a crossing rectangle:

(TableExport '((300 223) (350 400)) "C:\\test.csv" (getvar 'DwgName))

Or even multiple points as a Crossing Polygon selection. Or simply use nil to select all tables in the drawing.

(TableExport nil "C:\\test.csv" (getvar 'DwgName))

 

In each instance the column following the prefix is the table handle. So you can even have multiple tables in the same DWG. Then you can write a lisp which could read the CSV and modify the tables from that ... though I'd leave such to someone else as an exercise

 

Also note I've made the file in append mode. So you can simply call such line directly in a SCR and it would just append the table data to the file. Thus running on several DWGs would combine all their tables into one file. If you want to start from scratch just delete the file.

[seegs]

Your genius is wasted on me . I dunno, i cant seem to make this work. I pasted the above code into a blank lisp file & ran it, it came aback "nil". Then pasted in each code just changing the coords to 341,248. I was checking my c drive for the file(s)?

[seegs]

OMG its done. After a bit of tweaking ive got it to work. Thankyou soooo much irneb

[irneb]

Welcome, glad you got it to work finally!

[fathihvac]

Hello,

irneb Would like to make the code to ask to select autocd table object and then allow us to browse for excel file and add a sheet into it.

[sunil kumar]

Dear Sir , 

Can you sent the  Auto cad Lisp  for the Auto cad table export to excel format

[BIGAL]

Just google lots of answers to this question.