Table Export for multiple drawings to Excel

[Joe.Gio]

Hi All,

Does anyone happen to have a lisp that will batch export tables from multiple drawings into a single Excel file? I've used the 'tableexport' command and that works fine for one at a time but we'd like to be able to do it in a batch. On some drawings there could be up to 2 tables. I haven't been able to find much information online that talks about this.

Thanks for the help all.

Joe

[CodeDing]

@Joe.Gio ,

This SHOULD be able to do it for you. The slow part will be updating any necessary dwg paths.

What this does is exports ALL tables in a drawing, to a SINGLE csv. Then subsequent drawing tables ALSO get added to the SINGLE csv.

Here's the script file you can use (update the lisp location and dwg locations):

FILEDIA
0
OPEN
c:/users/username/desktop/drawing1.dwg
(load "c:\\users\\username\\desktop\\cte.lsp")
CTE
CLOSE
OPEN
c:/users/username/desktop/drawing2.dwg
(load "c:\\users\\username\\desktop\\cte.lsp")
CTE
CLOSE
OPEN
c:/users/username/desktop/drawing3.dwg
(load "c:\\users\\username\\desktop\\cte.lsp")
CTE
CLOSE
FILEDIA
1 

And here's the lisp file you will use (be sure to update the file path to wherever you need). You said you needed all tables in ONE csv, so this will do that.

(defun c:CTE ( / ss fName f cnt e tbl r c cR cC tmp txt)
;CustomTableExport
(if (not (setq ss (ssget "_X" '((0 . "ACAD_TABLE")))))
  (progn (alert "No Tables Found") (exit))
);if
(setq fName "c:\\users\\username\\desktop\\CustomTableExport.csv")
(if (setq f (open fName "a"))
  (progn
    (repeat (setq cnt (sslength ss))
      (setq e (ssname ss (setq cnt (1- cnt))))
      (write-line (strcat (getvar 'DWGNAME) " - Table " (itoa (1+ cnt))) f)
      (setq tbl (vlax-ename->vla-object e))
      (setq r (vla-get-rows tbl) c (vla-get-columns tbl))
      (setq cR 0)
      (repeat r
	(setq cC 0 txt "")
	(repeat c
	  (setq tmp (vlax-variant-value (vlax-variant-change-type (vla-getcellvalue tbl cR cC) 8)))
	  (if (= (1- c) cC)
	    (progn (setq txt (strcat txt (if tmp tmp ""))) (write-line txt f))
	    (setq txt (strcat txt (if tmp tmp "") ","))
	  );if
	  (setq cC (1+ cC))
	);repeat c
	(setq cR (1+ cR))
      );repeat r
    );repeat cnt
    (close f)
  );progn
);if
(prompt "\nCTE Complete..")
(princ)
);defun

Hope this helps! Best,

~DD

[Joe.Gio]

Hi DD,

Thanks for sending that through. I've played a little with the LISP, it create the csv file but I have to go into each drawing and run it. Is there a way that it will go through all the drawings in a folder and export the tables?

Also with the table it creates in the csv file, it's not populating as it would do using the normal 'Export' option. There's text missing, columns are in the incorrect place etc. Not sure what would be causing that?

Thanks again for the help DD.

Regards,

Joe

[CodeDing]

Ok,

If the script doesn't do it for you then I would recommend looking into THIS.

And yeah it might need some tweaking. Can you perhaps post a drawing with one of your tables in it? That would help greatly.

[Joe.Gio]

Hi DD,

I've attached a sample drawing, I've just pulled everything off and left a random table in there twice.

Haha I use Lee Macs site a fair bit looking for lisps we can use, he's done some great things on his page and other forums that I've looked up. It was the first place I went to see if something existed for this.

Thanks DD.

Joe

[dbhunia · Accepted solution]

Hi,

You can try this with SDI mode.

(defun c:Exp_Table ( / sh folder folderobject result en eo fname f row col str n qt NO)
   (vl-load-com)
   (setq sh (vla-getInterfaceObject (vlax-get-acad-object) "Shell.Application" ))
   (setq folder (vlax-invoke-method sh 'BrowseForFolder 0 "Browse The Folder Containing Drawings" 0))
   (vlax-release-object sh)
   (if folder
      (progn
		(setq fname (getstring T "Enter file path to exported data: "))
		(if (setq f (open fname "a"))
			(progn
				(setq SDI_Val (getvar "SDI") LISPI_Val (getvar "LISPINIT"))
				(vl-cmdf "SDI" 1)
				(vl-cmdf "LISPINIT" 0)
				(setq FolderObject (vlax-get-property folder 'Self))
				(setq result (vlax-get-property FolderObject 'Path))
				(vlax-release-object folder)
				(vlax-release-object FolderObject)
				(setq Files_Folder (vl-directory-files result "*.dwg"))
			
				(command "save" (strcat (getvar "dwgprefix") (getvar "dwgname")) "Y")
				(setq n 0)
				(while (< n (length Files_Folder))
					(command "fileopen" (strcat result "\\" (nth n Files_Folder)))
					(setq qt 0)
					(if (setq ss (ssget "_X" '((0 . "ACAD_TABLE"))))
						(progn
							(repeat (setq NO (sslength ss))
								(setq en (ssname ss (setq NO (1- NO))))
								(setq eo (vlax-ename->vla-object en))
								(princ (strcat "\n" (getvar 'DWGNAME) " - Table - " (itoa (setq qt (1+ qt))) "\n\n\n") f)						
								(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)
								)
							)
						)
					)			
					(setq n (+ 1 n))
				)
				(vl-cmdf "SDI" SDI_Val)
				(vl-cmdf "LISPINIT" LISPI_Val)
				(close f)
			)
		)
      )
	  
   )
)

Also check <THIS>

[Joe.Gio]

Hi Debashis,

Thanks for helping with the LISP. I've tried it a couple of different ways but it always seems to stop at 'Enter file path to exported data:'. I put in the folder location and it just comes up with 'nil'. I tried just making a copy of the folder to my desktop and still had the same result. Is there something I might be doing wrong? I also tried '\\' for folder separators as I know that's how lisp usually handles them but still the same result.

Thanks again for the help.

Regards,

Joe

[dbhunia]

@Joe.Gio 

First thing keep only one drawing in opened condition for that Particular AutoCAD session (i.e. SDI Mode).

And follow as per the the attached video.

[Joe.Gio]

Hi Debashis,

Thanks for the video, I see where I was going wrong. I wasn't setting a file name to save it to, I was just trying to select a folder location to save it in. Also some of our locations won't allow me to save in so I had to find one that would work.

It's working great now. I just had 2 last questions.

1 - Do you know why it's outputting that additional information '\pt20,85,92;' instead of just the text in the cell like the 'TABLEEXPORT' command does?

2 - After I run the lisp I can't close the drawing, I need to go through task manager and close AutoCAD then relaunch.

It's great that it's working now, it gives us something we can use. The video was a great helping understanding what was happening.

Thanks,

Joe

[dbhunia]

---

1 - Do you know why it's outputting that additional information '\pt20,85,92;' instead of just the text in the cell like the 'TABLEEXPORT' command does?  (I also noticed that, this is happening with your particular type(as in Sample.dwg) of Table otherwise working great)..... any way you can replace this "\pt20,85,92;"with "" in *.csv (excel) file.

2 - After I run the lisp I can't close the drawing, I need to go through task manager and close AutoCAD then relaunch. (This happened due to not for resting back the SDI Mode to MDI Mode. If this happened simply type "SDI" in command line and enter "0"(zero) and press enter)

[Joe.Gio]

Hi Debashis,

You're right it seems to be something with the table, it's weird as it works with the Tableexport command fine but I've tried your lisp with someone elses table and it comes up differently. Like you mentioned though I can just find and replace easily to clean it up.

As for the SDI command I did try that before setting it to '0' after running the LISP but I can't actually do anything in the drawing at all. I can't enter any commands, draw anything or even close AutoCAD without going through Task Manager. Is it worth trying to set it back to '0' in the LISP?

If I can't fix it with the SDI the lisp will still save a lot of time as it is even if I need to close AutoCAD.

Thanks,

Joe

[dbhunia]

I did not actually you totally......

send video ....... or sample ....

[Joe.Gio]

I've attached a screen shot, sorry I don't have anything that'll let me record a video.

It looks like the last command is SDI but then I can't type anything in the command bar or click on anything anywhere.

[dbhunia]

1. first place 2-3 drawing file in a folder for test.....

2. stretch the command bar

3. run the application on that folder(containing 2-3 drawing)

4. take snap like below & send it.....

[Joe.Gio]

Hi Debashis,

Please find the screen shot attached.

Thanks,

Joe

[Joe.Gio]

On another note I've fixed the text showing additional information. I don't know why but I just went through each cell one at a time, not doing anything but just selecting it and for some reason that's removed the extra information so when I export the table it comes up correctly now.