Extract Excel Data from multiple drawings

[nck5678]

Dear all,

I came across the below LISP routine, which helps me extract the table contents from multiple AutoCAD drawings in an excel.

I further wish to customize this LISP routine to extract a specific table from all the drawings. This table will have a certain common name (Say, e.g. List of Elements). Can anyone suggest how to improvise this change in the LISP routine?

I got this routine from this forum.

(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)

)

)

      )

   )

)

[komondormrex]

hi there,

if you mean by 'common name' a table title you may try following code with correction

(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))))
	(if (= "List of Elements" (cdr (assoc 1 (entget en))))
		(progn
			(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)
)
)
      )
  
   )
)

[Sea-Haven]

If your table always has a "TITLE" property then just get the text in the "TITLE" cell. Then check its value. Else use correct cell address to check.

(vla-GetText objtable 0 0)

[daniel_cadext]

Sorry for the intrusion, if you know a bit of Python and if you’re using a current version of AutoCAD, you can use my Python for AutoCAD wrappers.

This is a task well suited for Python since you can use openpyxl

Here is a sample of how to write every table, from every drawing in a directory (top level files). Each table is added to a new sheet by table handle. You can adapt the routine to look for a specific handle of value of the first table cell  

import traceback
import PyRx as Rx
import PyGe as Ge
import PyGi as Gi
import PyDb as Db
import PyAp as Ap
import PyEd as Ed
import wx

from os import walk
import openpyxl as xl

def getPath() -> str:
    path = ""
    dlg = wx.DirDialog(
        None, "Choose input directory", "", wx.DD_DEFAULT_STYLE | wx.DD_DIR_MUST_EXIST
    )
    if dlg.ShowModal() == wx.ID_OK:
        path = dlg.GetPath()
    return path

def getDwgPaths():
    f = []
    path = getPath()
    if len(path) == 0:
        return f
    for dirpath, dirnames, filenames in walk(path):
        found = [dirpath + file for file in filenames if file.lower().endswith(".dwg")]
        f.extend(found)
        break
    return f

def processDWG(path: str, wb):
    sideDb = Db.Database(False, True)
    sideDb.readDwgFile(path)
    sideDb.closeInput(True)
    ids = sideDb.objectIds(Db.Table.desc())
    processTables(ids, wb)

def processTables(ids: list[Db.ObjectId], wb):
    opt = Db.TableIteratorOption.kTableIteratorSkipMerged
    for id in ids:
        table = Db.Table(id)
        sheet = wb.create_sheet(id.handle().toString())
        cells = table.getIterator(opt)
        for cell in cells:
            xlcell = sheet.cell(row=cell.row + 1, column=cell.column + 1)
            xlcell.value = table.textString(cell)

def PyRxCmd_doit():
    try:
        wb = xl.Workbook()
        for path in getDwgPaths():
            processDWG(path, wb)
        wb.save("WOOHOO.xlsx")

    except Exception as err:
        traceback.print_exception(err)