Text to excel sheet

[Peter K]

For a certain situation it would be rather helpful for a colleague of mine to have a .lsp routine that would collect all the text elements out of each drawing in a certain folder and put them all in a excel sheet (one text element per row).

 

Does anyone know of any routine that would do that?

 

She has autocad 2008 LT (but with LT-extender so she is able to use lisp routines)

 

The drawings are existing and because there are a couple of hundred it wouldn't help to have a routine that would only do it for one drawing at the time.

 

I would be pretty amazed if something would be around that would this (and for free) but hey... it can't hurt to enquire.

 

[Lee Mac]

I believe this can be achieve with LISP - my CAD is messing me around at the moment, but when I get it sorted, I'd be happy to see what I can write for you

 

Are you sure you want EVERY text element extracted? Not just some on a certain layer?

[Peter K]

I believe this can be achieve with LISP - my CAD is messing me around at the moment, but when I get it sorted, I'd be happy to see what I can write for you

 

Are you sure you want EVERY text element extracted? Not just some on a certain layer?

 

The reason for wanting every text element is just so one isn't dependent on the fact if the text element is on the "correct" layer.

Once everything would be in Excel she could sort them out and bring it down easily to the couple of hundred entities that have to be printed out. It is more important to have everything to select from in Excel then it is to have a pre-filtered list because the latter may or may not miss a bunch of elements.

 

Besides that, there's the fact that you would then have to build in the possibility to check for a particular layer, even if it was simply by editing within the routine, instead of having a loop to check all layers within a drawing.

 

Thanks in advance for looking into it.

[Lee Mac]

The reason for wanting every text element is just so one isn't dependent on the fact if the text element is on the "correct" layer.

Once everything would be in Excel she could sort them out and bring it down easily to the couple of hundred entities that have to be printed out. It is more important to have everything to select from in Excel then it is to have a pre-filtered list because the latter may or may not miss a bunch of elements.

 

Besides that, there's the fact that you would then have to build in the possibility to check for a particular layer, even if it was simply by editing within the routine, instead of having a loop to check all layers within a drawing.

 

Thanks in advance for looking into it.

 

I see what you are saying - checking for layers LISP in routines isn't too difficult, but there is always the possibility of human error when a text entity has been placed on the wrong layer.

 

With regards to writing to Excel - I shall probably write the data to a CSV file, which is a type of Excel file that is easier to write to, and takes a lot less coding. From here you can either just copy paste the information to another Excel file, or save the CSV file as an XLS once the extraction is complete

 

Lee

[Commandobill]

Just curious when you say each text element do you mean if a piece of text says "hello world" (generic i know) do you want that on one line or "hello" on one line and "world" on another?

[Lee Mac]

Just curious when you say each text element do you mean if a piece of text says "hello world" (generic i know) do you want that on one line or "hello" on one line and "world" on another?

 

I understood it as each text entity on a new line - but good question bill.

[Commandobill]

This should get the job done.

 

(defun c:ttx ( / xlCells xlSheet xlSheets xlBook xlBooks xlApp column row drac ss ent expo x)
          
 (vl-load-com)
 
 (setq    xlApp       (vlax-get-or-create-object "Excel.Application")
   xlBooks  (vlax-get-property xlApp "Workbooks")
   xlBook       (vlax-invoke-method xlBooks "Add")
   xlSheets (vlax-get-property xlBook "Sheets")
   xlSheet       (vlax-get-property xlSheets "Item" 1)
   xlCells       (vlax-get-property xlSheet "Cells"))
 
 (vla-put-visible xlApp :vlax-true)
 
 (setq column 1 
   row    1
   drac   -1
   ss     (ssget "_X" (list (cons 0 "*TEXT"))))
 
 (repeat (sslength ss)
   (setq ent  (ssname ss (setq drac (1+ drac)))
         expo (vla-get-textstring (vlax-ename->vla-object ent)))
   (if (<= row 65536)
     (vlax-put-property xlCells "Item" row column expo)
     (progn
   (setq row    1
         column (1+ column))
   (vlax-put-property xlCells "Item" row column expo)
   ); /else progn
     ); /if
   (setq row (1+ row))
   ); /repeat
 
 (mapcar (function (lambda(x)
             (vl-catch-all-apply
           (function (lambda()
                   (progn
                     (vlax-release-object x)
                     (setq x nil)))))))
     (list xlCells xlSheet xlSheets xlBook xlBooks xlApp))
 
 (alert "Close Excel file manually")
 
 (gc)(gc)
 
 (princ)
 
 )

magic

[Lee Mac]

Nice one Bill - I'm new to writing to Excel...

 

I thought maybe this to cycle through open docs...

 

(defun c:txt2xl (/ fPath fName ofile doc ss)
 (vl-load-com)

 (setq fPath "C:\\")
 (setq fName "test.csv")

 (if (vl-file-directory-p fPath)
   (progn
     (setq ofile (open (strcat fPath fName) "a"))
     (vlax-for doc (vla-get-Documents
                     (vlax-get-Acad-Object))        
       (if (setq ss (ssget "_X" '((0 . "MTEXT,TEXT"))))
         (mapcar
           (function
             (lambda (x)
               (write-line x ofile)))
           (mapcar
             (function
               (lambda (x)
                 (cdr (assoc 1 (entget x)))))
             (mapcar 'cadr (ssnamex ss)))))
       (setq ss nil))
     (close ofile))
   (princ "\n<!> Filename Does not Refer to Valid Directory <!>"))
 (princ))
     

[Commandobill]

Nice one Bill - I'm new to writing to Excel...

 

I thought maybe this to cycle through open docs...

 

I must not have read that he wanted to use it on multiple drawings of course i could just ask freerefill to post his batch program then they could use the two of them so that he wouldnt need to open all his drawings first...

 

Either way your code is nothing less then i expected from you Lee except the fact that you used (cdr (assoc 1 (entget x))) instead of the vla-get-textstring very un 'lee' like

[Lee Mac]

I must not have read that he wanted to use it on multiple drawings of course i could just ask freerefill to post his batch program then they could use the two of them so that he wouldnt need to open all his drawings first...

 

Either way your code is nothing less then i expected from you Lee except the fact that you used (cdr (assoc 1 (entget x))) instead of the vla-get-textstring very un 'lee' like

 

Well, I just thought DXF'ing was slightly easier - still only one line, and quicker than converting all to vla-objects for the sake of it

[Peter K]

The idea was that in case one had "hello world" as wel as "goodmorning sunshine" that it would be put into a cell (.csv or .xls) as "hello world" and another cell, in the next row, "goodmorning sunshine"

 

Thank you both for the fast reply

 

Commandobill, your code is exactly what is needed, except for the fact that it only does one drawing. Which means that perhaps I can work around that by using a macro or so in excel to append all data in each seperate .xls file to one large .xls file.

 

Lee Mac, it does mention each text item twice if the drawing is located on the desktop. The solution is simple, namely to not put the drawings on the desktop, not even in a folder on the desktop. In other words, PEBCAK.

Once I put the testing drawing directly on the C: It only reproduced the text elements once.

 

It does append the data it extracts from each subsequent drawing in to the test.csv file when one runs the routine manually in additional drawings.

(FYI, I tried it with 2 drawings located in one folder directly on the C: path)

 

Will test it out more when I have more time.

 

Thank you kindly again to you both. Much appreciated.

[Lee Mac]

The idea was that in case one had "hello world" as wel as "goodmorning sunshine" that it would be put into a cell (.csv or .xls) as "hello world" and another cell, in the next row, "goodmorning sunshine"

 

Thank you both for the fast reply

 

Commandobill, your code is exactly what is needed, except for the fact that it only does one drawing. Which means that perhaps I can work around that by using a macro or so in excel to append all data in each seperate .xls file to one large .xls file.

 

Lee Mac, it does mention each text item twice if the drawing is located on the desktop. The solution is simple, namely to not put the drawings on the desktop, not even in a folder on the desktop. In other words, PEBCAK.

Once I put the testing drawing directly on the C: It only reproduced the text elements once.

 

It does append the data it extracts from each subsequent drawing in to the test.csv file when one runs the routine manually in additional drawings.

(FYI, I tried it with 2 drawings located in one folder directly on the C: path)

 

Will test it out more when I have more time.

 

Thank you kindly again to you both. Much appreciated.

 

Yes, I have set it append to the existing file - this can be changed if necessary

 

 

PS> I'd never heard of PEBCAK... had to look it up - great phrase

[Peter K]

No,no,no. No change needed. This does exactly what I understood she wanted.

Especially when I combine it with the batch routine I found on

http://www.cadtutor.net/forum/showthread.php?t=9942

 

The one from Commandobill I will also keep because it makes it easier to check for certain conditions/elements/values in one drawing.

 

Once again, my (and her) thanks to the both of you.

 

Have a nice one.

[Commandobill]

Hey Lee. I ran your code and it seems instead of going through the open drawings and making the selection set all it does is make the same selection set per each drawing you have open. Kinda like it's on repeat without going to the other open drawings.

[Peter K]

I noticed that as well but as long as one only has one drawing open when running the routine it works.

 

Seeing as that the intended purpose is using it on a whole bunch of drawings, more then one can have open all at the same time, I don't have an issue with it.

 

The end user will just have to keep that in mind as one of the rules when using it. It's even better that way because then there's no possibility of adding elements of drawings that one didn't want included but does have open for another reasons. The worst that can happen is that multiple instances of the same elements will be added to the .csv.

[Commandobill]

Well if all of the text objects were on model space and you wanted to use Lee's trick of getting all the objects from all open dwgs you could go with this

 

(defun c:txt2csv (/ fPath fName ofile docs mdlspc drac mdl)
 (vl-load-com)

 (setq fPath "C:\\")
 (setq fName "test.csv")

 (if (vl-file-directory-p fPath)
   (progn
     (setq ofile (open (strcat fPath fName) "a"))
     (vlax-for docs (vla-get-documents (vlax-get-acad-object))
   (setq mdlspc (vla-get-modelspace docs))
   (setq drac 0)
   (vlax-for mdl mdlspc
     (if (or (= (vla-get-objectname mdl) "AcDbMText")
         (= (vla-get-objectname mdl) "AcDbText"))
       (write-line (vla-get-textstring mdl) ofile)
       )
     (setq drac (1+ drac))
     )
   )
     )
   )
 (close ofile)
 (princ))

 

 

edit: 100th post.... sweet...

[Peter K]

LOL

 

First she didn't have any choice but to do some serious tedious labour that would take hours with lots of possibilities to produce errors and now.... she'll have too many possibilities to choose between.

 

Commandobill, your version of the routine has been copied, saved and filed for future reference. Thanks.

[Peter K]

(edit : congrats on your 100th post)

[Lee Mac]

Thanks Bill,

 

I hadn't tested mine, so it was only theoretical, but thank you for refining it

[Commandobill]

Thanks Bill,

 

I hadn't tested mine, so it was only theoretical, but thank you for refining it

 

lol honestly i half expected you to come back and tell me how it's not a good thing to go through all of the objects in model space and test to see if they are text objects... lol your welcome. I also figured you hadnt tested it i read your other posts about your computer =/

 

did you fix that?