Data extraction from tables to XLS

[gfreddog]

We've had great luck extracting block attribute data to tables and external XLS files but now for something completely different...

I have several tables in a drawing and I would like to collect all of the data from those tables and export it to an XLS File.

Haven't had any luck with the Data Extraction tool.

Any ideas or suggestions?

TIA

[gfreddog]

Bueller Bueller?

[gfreddog]

We've had great luck extracting block attribute data to tables and external XLS files but now for something completely different...

I have several tables in a drawing and I would like to collect all of the data from those tables and export it to an XLS File.

Haven't had any luck with the Data Extraction tool.

Any ideas or suggestions?

TIA

Well I've been experimenting on some different fronts with no luck.

Anyone aware of a LISP, an app, a macro, or 3rd party tool that can help?

[irneb]

A very quick & dirty lisp:

(vl-load-com)

(defun c:Tbl2Csv (/ block eo f n row col str val)
  (setq n 0)
  (vlax-for block (vla-get-Blocks (vla-get-ActiveDocument (vlax-get-acad-object)))
    (vlax-for eo block
      (if (eq (vla-get-ObjectName eo) "AcDbTable")
        (progn
          (setq n (1+ n) row -1 f (open (strcat (getvar 'DwgPrefix) (vl-filename-base (getvar 'DwgName)) "-Table" (itoa n) ".CSV") "w"))
          (while (< (setq row (1+ row)) (vla-get-Rows eo))
            (setq col -1 str "")
            (while (< (setq col (1+ col)) (vla-get-Columns eo))
              (setq val (vl-catch-all-apply 'vlax-invoke (list eo 'GetCellValue row col)))
              (cond
                ((= (type val) 'STR) (setq str (strcat str ",\"" val "\"")))
                ((= (type val) 'INT) (setq str (strcat str "," (itoa val))))
                ((= (type val) 'REAL) (setq str (strcat str "," (rtos val))))
                (t (setq str (strcat str ",")))
              )
            )
            (write-line (substr str 2) f)
          )
          (close f)
        )
      )
    )
  )
  (princ)
)

It simply saves each table found in the drawing to a CSV file named the same as the drawing with a suffix of -Table1, -Table2, ... TableN.

It would be possible to directly save into an XLS(x) file, but that's a "bit" more programming.

[gfreddog]

A very quick & dirty lisp:

(vl-load-com)

(defun c:Tbl2Csv (/ block eo f n row col str val)
  (setq n 0)
  (vlax-for block (vla-get-Blocks (vla-get-ActiveDocument (vlax-get-acad-object)))
    (vlax-for eo block
      (if (eq (vla-get-ObjectName eo) "AcDbTable")
        (progn
          (setq n (1+ n) row -1 f (open (strcat (getvar 'DwgPrefix) (vl-filename-base (getvar 'DwgName)) "-Table" (itoa n) ".CSV") "w"))
          (while (< (setq row (1+ row)) (vla-get-Rows eo))
            (setq col -1 str "")
            (while (< (setq col (1+ col)) (vla-get-Columns eo))
              (setq val (vl-catch-all-apply 'vlax-invoke (list eo 'GetCellValue row col)))
              (cond
                ((= (type val) 'STR) (setq str (strcat str ",\"" val "\"")))
                ((= (type val) 'INT) (setq str (strcat str "," (itoa val))))
                ((= (type val) 'REAL) (setq str (strcat str "," (rtos val))))
                (t (setq str (strcat str ",")))
              )
            )
            (write-line (substr str 2) f)
          )
          (close f)
        )
      )
    )
  )
  (princ)
)

It simply saves each table found in the drawing to a CSV file named the same as the drawing with a suffix of -Table1, -Table2, ... TableN.

It would be possible to directly save into an XLS(x) file, but that's a "bit" more programming.

I've been playing around to get it into 1 xls file with no luck

We usually can have 10 to 20 "tables" in one drawing so I need to try to find a way to get it all into on XLS...

[irneb]

Here's an alternative version saving multiple tables into one CSV file.
http://www.cadtutor.net/forum/showth...l=1#post449306

A column is prefixed at each line with the table's handle, and optionally you could have another prefixed column with any text you want. It's meant to be run through multiple drawings at once, so you would usually have the DWG name as the 1st column.

[gfreddog]

Here's an alternative version saving multiple tables into one CSV file.
http://www.cadtutor.net/forum/showth...l=1#post449306

A column is prefixed at each line with the table's handle, and optionally you could have another prefixed column with any text you want. It's meant to be run through multiple drawings at once, so you would usually have the DWG name as the 1st column.

Ok I think that is what I really need but it might be lack of coffee or the head-cold I'm fighting off but how do I trigger this?

[irneb]

That one was a bit "specialist". The TableExport function requires 3 parameters:

1. A point or list of points, to use as a crossing selection. Or nil to select all tables in the current DWG.
2. The filename of the CSV
3. Nil, or the text to place in the very 1st column on each line.

This is meant as a lisp callable defun for running it inside a script on multiple DWGs in sequence. The SCR could have been something like this:

(setq dwg (getvar 'DwgName))
(TableExport nil "C:/MyData/Tables.CSV" dwg)

[gfreddog]

Oh ok. Is there a way to trigger it via LISP?

[irneb]

Oh ok. Is there a way to trigger it via LISP?

Yes , just type the following at the command prompt or in your lisp:

(TableExport nil "C:/MyData/Tables.CSV" "MyPrefix")

Change the path & filename to the CSV as needed, and the "MyPrefix" can either be replaced with nil if you don't want a prefixed column, or to any other piece of text or a variable containing text.