Page 1 of 2 12 LastLast
Results 1 to 10 of 16

Thread: Data extraction from tables to XLS

  1. #1
    All AUGI, all the time gfreddog's Avatar
    Join Date
    2003-07
    Location
    Pequannock NJ US of A
    Posts
    641
    Login to Give a bone
    0

    Default Data extraction from tables to XLS

    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
    George V. Fournier Jr., CTS
    Sr. Sales Engineer - Autodesk Product Design Suite Premium 2014
    2006 AUGI DAWG (7th) - NJ AUGI Camp
    Volunteer Firefighter since 1985 & Bagpiper Wanna-be
    With the freedom of expression comes great responsibility


  2. #2
    All AUGI, all the time gfreddog's Avatar
    Join Date
    2003-07
    Location
    Pequannock NJ US of A
    Posts
    641
    Login to Give a bone
    0

    Default Re: Data extraction from tables to XLS

    Bueller Bueller?
    George V. Fournier Jr., CTS
    Sr. Sales Engineer - Autodesk Product Design Suite Premium 2014
    2006 AUGI DAWG (7th) - NJ AUGI Camp
    Volunteer Firefighter since 1985 & Bagpiper Wanna-be
    With the freedom of expression comes great responsibility


  3. #3
    All AUGI, all the time gfreddog's Avatar
    Join Date
    2003-07
    Location
    Pequannock NJ US of A
    Posts
    641
    Login to Give a bone
    0

    Question Re: Data extraction from tables to XLS

    Quote Originally Posted by gfreddog View Post
    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?
    George V. Fournier Jr., CTS
    Sr. Sales Engineer - Autodesk Product Design Suite Premium 2014
    2006 AUGI DAWG (7th) - NJ AUGI Camp
    Volunteer Firefighter since 1985 & Bagpiper Wanna-be
    With the freedom of expression comes great responsibility


  4. #4
    Certifiable AUGI Addict irneb's Avatar
    Join Date
    2015-11
    Location
    Jo'burg SA
    Posts
    4,512
    Login to Give a bone
    0

    Default Re: Data extraction from tables to XLS

    A very quick & dirty lisp:
    Code:
    (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.
    Knowledge is proportional to experience, but wisdom is inversely proportional to ego!
    My little bit of "wisdom": Hind-sight is useless, unless used to improve the next forethought!

  5. #5
    All AUGI, all the time gfreddog's Avatar
    Join Date
    2003-07
    Location
    Pequannock NJ US of A
    Posts
    641
    Login to Give a bone
    0

    Default Re: Data extraction from tables to XLS

    Quote Originally Posted by irneb View Post
    A very quick & dirty lisp:
    Code:
    (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...
    George V. Fournier Jr., CTS
    Sr. Sales Engineer - Autodesk Product Design Suite Premium 2014
    2006 AUGI DAWG (7th) - NJ AUGI Camp
    Volunteer Firefighter since 1985 & Bagpiper Wanna-be
    With the freedom of expression comes great responsibility


  6. #6
    Certifiable AUGI Addict irneb's Avatar
    Join Date
    2015-11
    Location
    Jo'burg SA
    Posts
    4,512
    Login to Give a bone
    0

    Default Re: Data extraction from tables to XLS

    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.
    Knowledge is proportional to experience, but wisdom is inversely proportional to ego!
    My little bit of "wisdom": Hind-sight is useless, unless used to improve the next forethought!

  7. #7
    All AUGI, all the time gfreddog's Avatar
    Join Date
    2003-07
    Location
    Pequannock NJ US of A
    Posts
    641
    Login to Give a bone
    0

    Default Re: Data extraction from tables to XLS

    Quote Originally Posted by irneb View Post
    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?
    George V. Fournier Jr., CTS
    Sr. Sales Engineer - Autodesk Product Design Suite Premium 2014
    2006 AUGI DAWG (7th) - NJ AUGI Camp
    Volunteer Firefighter since 1985 & Bagpiper Wanna-be
    With the freedom of expression comes great responsibility


  8. #8
    Certifiable AUGI Addict irneb's Avatar
    Join Date
    2015-11
    Location
    Jo'burg SA
    Posts
    4,512
    Login to Give a bone
    0

    Default Re: Data extraction from tables to XLS

    That one was a bit &quot;specialist&quot;. 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:
    Code:
    (setq dwg (getvar 'DwgName))
    (TableExport nil &quot;C:/MyData/Tables.CSV&quot; dwg)
    Knowledge is proportional to experience, but wisdom is inversely proportional to ego!
    My little bit of "wisdom": Hind-sight is useless, unless used to improve the next forethought!

  9. #9
    All AUGI, all the time gfreddog's Avatar
    Join Date
    2003-07
    Location
    Pequannock NJ US of A
    Posts
    641
    Login to Give a bone
    0

    Default Re: Data extraction from tables to XLS

    Oh ok. Is there a way to trigger it via LISP?
    George V. Fournier Jr., CTS
    Sr. Sales Engineer - Autodesk Product Design Suite Premium 2014
    2006 AUGI DAWG (7th) - NJ AUGI Camp
    Volunteer Firefighter since 1985 & Bagpiper Wanna-be
    With the freedom of expression comes great responsibility


  10. #10
    Certifiable AUGI Addict irneb's Avatar
    Join Date
    2015-11
    Location
    Jo'burg SA
    Posts
    4,512
    Login to Give a bone
    0

    Default Re: Data extraction from tables to XLS

    Quote Originally Posted by gfreddog View Post
    Oh ok. Is there a way to trigger it via LISP?
    Yes , just type the following at the command prompt or in your lisp:
    Code:
    (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.
    Knowledge is proportional to experience, but wisdom is inversely proportional to ego!
    My little bit of "wisdom": Hind-sight is useless, unless used to improve the next forethought!

Page 1 of 2 12 LastLast

Similar Threads

  1. Replies: 1
    Last Post: 2015-04-29, 01:18 PM
  2. Replies: 0
    Last Post: 2013-04-17, 04:42 AM
  3. Data extraction tables - annoying feature
    By jpo82 in forum AutoCAD Tables
    Replies: 0
    Last Post: 2012-08-23, 10:11 AM
  4. Data extraction
    By paulof in forum AutoLISP
    Replies: 0
    Last Post: 2011-11-29, 06:45 PM
  5. DB + multiple lookup tables + attribute extraction = headache!
    By bcalendine in forum Dynamic Blocks - Technical
    Replies: 1
    Last Post: 2007-12-06, 02:32 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •