Topic: Generate Index Text from Excel Spreadsheet.

[DIW_CADtech]

The client is forcing us to use their antiquated template for all index sheets  It is basically an array of individual pieces of center aligned text.  They send us and we modify an excel spreadsheet, but the spreadsheet is not in the same order as the index sheet.  Furthermore, one would like to use a table, but the client wants NO MTEXT in the drawing.  We've already agreed to this. 

I could use a table.. then format.. then explode.. then rearrange. This still takes a great deal of time.

The thing is the log file is an excel spreadsheet and it never changes in format.  Would it be possible to Script the placement of the text and pull the text from the cells in the spreadsheet?

I have to do this and re-do this several times throughout the design process and it's a tremendous pain in the keester.

The spacing is always the same vertically and for the most part horizontally, although the distance from one column to the other varies.

Each individual piece of text(not mtext) is aligned center.

column   "X"
1   0.5000"
2   1.2500"
3   2.6895"
4   4.5411"
5   5.6375"
6   6.2121"
7   7.8273"
8   11.4238"
9   14.4935"
10   16.00"

The first "Y" is at 9.85" and each following row is .2" lower than the previous

Example DWG
https://dl.dropboxusercontent.com/u/20790817/theswamp/index/SOME_DRAWING_INDEX_TEMPLAT.dwg

Example XLS
https://dl.dropboxusercontent.com/u/20790817/theswamp/index/SOME_PROJ_LOG.xls

Example Completed Index
https://dl.dropboxusercontent.com/u/20790817/theswamp/index/58-03-0296.dwg

I don't mind copying and pasting the header info.. takes a few min..

Manually entering 300 lines and 10 columns is a pain.  I often have to do this multiple times throughout a project because the order will change, or drawings will be added or removed from the project log.

In any case this seems like something that both should and could be scripted.  Especially since each piece of text is formatted exactly the same.

The main problem being that the columns in the DWG are in a different order than the columns in the XLS.

Sometimes the project is 20 DWG's  and once 1300 dwgs.

The engineers always update the EXCEL LOG, but neglect to inform me so I can update the INDEX.  If I could somehow find a LSP routine that would allow me to clear the text and then repost from a log file I can choose.. that would be freek'n awesome. 

I have spent at least 6 to 10 hours of each project filling these things out and then backchecking for fat finger mistakes...

At my wits end....   Please HELP
PLEASE do not suggest TABLES, OLE objects  OR MTEXT..  those avenue's are not available to me.


I am not apt at writing LSP.  BUt I am pretty good at figuring out what I'm looking at...  Just not good enough to write or debug. 

[ronjonp]

Can you post a drawing with before & after?

[DIW_CADtech]

I revised the above text...  and added some links.

[danallen]

Can't you keep a master file that has mtext or table, then explode for sending to client? And/or use Dotsoft excel to cad tools? http://www.dotsoft.com/xl2cad.htm

[DIW_CADtech]

Forgetting the fact that the colomn order is different from the log to the index, Tables are not an option because it leads to individually re-adjusting position of text..  special characters like slashes tend to reposition the text, resize cells and can change the location of the MTEXT.  Furthermore, I have to explode the MTEXT and I loose the center anchor as everything defaults to left.  so my positioning is whacked. 

I end up resetting TEXT alignment and manually repositioning text...  Not so bad if you have 20 rows.. but when you have 300 or 600 or even 1300 rows, fixing text position and alignment is NOT something i want to spend my time doing.  OR RE-DOING. 

Every log file is formatted exactly the same,
and so are these indexes.

SHoudl be a way to read column X from XCEL then script inserting text then follow suit on the next column. 

I'm trying to avoid having to track, maintain, reformat, re-adjust any intermediate files.   This always leads to more issues.  I want to go from the log... directly to CAD.

Take a look at the files posted you will see what I mean.. feel free to try your table idea. I have done it and I can never get it perfect.  I got a steak dinner for you if you can make it line up properly in less than a min.  I've tried this...  it is just too many steps away from point and click.. too much manual work. In my current method I am already doing a lot of manual work.  I'm looking for a method around the manual placement adjustment.  These pieces of text always have the same anchor points and center alignment.. it's just a matter of grabbing the data from XCEL and droping the text in the predetermined location.

I have scripted the entry before using a database.. simple code, word and the mail merge function... All of this works, but takes a great deal of time to set up... and is tantamount to programming.


Also, I can not have LINKS in the drawing it must be 100% stand alone.  The program you mention seems great at taking what is formatted in excel and generating linework in AUTOCAD and linking...  that's great.. but my LOG in excel looks nothing like my INDEX in CAD.  So again.. manual formatting and relocating text.  In fact I don't want any formatting to carry over from Excel. 

[danallen]

I don't have a trick for excel to text, but within excel you could use a pivot table to rearrange the information, see attached. I imagine you could tweak this pivot table excel format to be a good match to CAD, then use Dotsoft tool to import/link.

[DIW_CADtech]

Current method is a multi step process.

I start by copying the spreadsheet into a new spreadsheet.  Then in the new spreadsheet I have a separate tab that grabs data from the first tab.   

So in the Second Tab all the columns are in the correct order.

Then I select that data and copy

Switch to Autocad and Paste as a table

I strip all formatting from the table

I set the text size 1/2 what the final size needs to be...  (this is just for convenience)

Then I set the cell height to .2 which is exactly the spacing on the final table.

Then I explode the table and delete any borders.

Then I drag each column close to it's respective column  in the final layout.

At this time all the pieces of text are layed out in the final layout, but they all have a value of "~"

Then I use a LSP command STX to swap data from each MTEXT to the TEXT I already have formatted and laid out.  (got this from LEE MAC)
(it is a sweet LSP)

I select all MTEXT and delete

Finally, I do a search and replace and replace all "~" with nothing (eliminating any cells that should be blank)

This method works, and means no fat fingering, and all the text are in exactly the correct location and they match the log exactly.

But this is tedious... and for any more than one sheet.. it takes forever.  For me at least this has the lowest amount of error.

[danallen]

I imagine Dotsoft's tool would speed things up and you could tweak your excel to minimize or eliminate much CAD work. The pretty low price would easily be made up in the hours you've mentioned. In the past I used a tool called Spanner and could get tables synchronized well.

[DIW_CADtech]

again.. tables.. can't use em
Client cad standards.. and exploding changes the location of the insertion points on all the MTEXT..

Tables are not a solution.

[danallen]

what happens to tables when you save as R12 version and reopen?

and in Bricscad v14, nothing moves/changes when you explode a table into lines & mtext

[danallen]

also Dotsoft says lines & text, not tables. Email Terry Dotson, he is very reachable and offers free evaluation
http://www.dotsoft.com/xl2cad.htm

[DIW_CADtech]

Again the problem will be control of the location of the text.  The client wants the text in very exacting locations.  I'm not sure that can be dictated from Excel.  No matter how advanced the software, the cells in Excel are determined by pts not inches or mm.. and the fonts are TTF's or other.. not SHX or AutoCAD Fonts.  I don't want to introduce that into the drawing. 

I will check out the link and see what the capabilities of the software are.

Again, speaking to tables... our client doesn't tolerate Mtext.   When you explode a table, all the text becomes MTEXT.  I then have to explode it... Assuming the Mtext has an alignment of bottom center, when you explode it the text doesn't move, but by default, the anchor point becomes "LEFT" not center.  I have to manually change the text from "LEFT" to "CENTER" and then re-align all the text to their expected positions.  That's a lot of work for 10 columns by 300 dwgs.  Rea-positioning 3000 pieces of text is not my idea of a fun time.

So tables are not an option.

Again, I will check out the software you recommend.. but my goal is not to transfer any formatting from the LOG to the CAD file. Just the data from each cell.

[ronjonp]

Maybe an easy way would be to save the log as a csv ( so it's just text ) file then parse the data. Then it's just placing the text in the right place.

[DIW_CADtech]

essentially that's what I am manually doing now.  I create the array of formatted individual text with a value of "~", and by clicking from an exploded table I transfer the data from MTEXT to TEXT.

but the log is always exactly the same format.  so I am wondering why a program couldn't be written to start at a specific X,Y in CAD start a piece of TEXT with a specific Style, and alignment of center. and pick from a cell .. say A:1 and drop in the data.. then move on to the next programed cell and preprogramed X,Y and do the same.   

This is a process I would use on every one of our client's projects.. the data will different on each project, but the Excell spreadsheet format and data locations will be exactly the same. 

I know what data is in what column.  that will never change.. what I don't know is how many rows.. so maybe that would be a variable I'd have to enter? 
Prompt for Project Log:  (navigate to file --> click open)
Prompt:  How many drawings in the set? 300

drops A:4 thru  A:304 as text in AutoCAD from  a specific X,Y in .2" intervals going down.. then follows suit with C:4   at a different X,Y  and D:4   and so on and so fourth. 

This is what I'm looking for. 

The text will always be of the same client provided style, height, layer, and alignment (center). 

So it should be easy to program a LSP for this.  I just don't have those programming skills.

 

[DIW_CADtech]

I have found this CODE, but i don't know how to modify it.. or call functions from it to perform the task I'm trying to accomplish

Code: [Select]

;-------------------------------------------------------------------------------
; Program Name: GetExcel.lsp [GetExcel R4]
; Created By:   Terry Miller (Email: terrycadd@yahoo.com)
;               (URL: http://web2.airmail.net/terrycad)
; Date Created: 9-20-03
; Function:     Several functions to get and put values into Excel cells.
;-------------------------------------------------------------------------------
; Revision History
; Rev  By     Date    Description
;-------------------------------------------------------------------------------
; 1    TM   9-20-03   Initial version
; 2    TM   8-20-07   Rewrote GetExcel.lsp and added several new sub-functions
;                     including ColumnRow, Alpha2Number and Number2Alpha written
;                     by Gilles Chanteau from Marseille, France.
; 3    TM   12-1-07   Added several sub-functions written by Gilles Chanteau
;                     including Cell-p, Row+n, and Column+n. Also added his
;                     revision of the PutCell function.
; 4    GC   9-20-08   Revised the GetExcel argument MaxRange$ to accept a nil
;                     and get the current region from cell A1.
;-------------------------------------------------------------------------------
; Overview of Main functions
;-------------------------------------------------------------------------------
; GetExcel - Stores the values from an Excel spreadsheet into *ExcelData@ list
;   Syntax:  (GetExcel ExcelFile$ SheetName$ MaxRange$)
;   Example: (GetExcel "C:\\Folder\\Filename.xls" "Sheet1" "L30")
; GetCell - Returns the cell value from the *ExcelData@ list
;   Syntax:  (GetCell Cell$)
;   Example: (GetCell "H15")
; Function example of usage:
; (defun c:Get-Example ()
;   (GetExcel "C:\\Folder\\Filename.xls" "Sheet1" "L30");<-- Edit Filename.xls
;   (GetCell "H21");Or you can just use the global *ExcelData@ list
; );defun
;-------------------------------------------------------------------------------
; OpenExcel - Opens an Excel spreadsheet
;   Syntax:  (OpenExcel ExcelFile$ SheetName$ Visible)
;   Example: (OpenExcel "C:\\Folder\\Filename.xls" "Sheet1" nil)
; PutCell - Put values into Excel cells
;   Syntax:  (PutCell StartCell$ Data$) or (PutCell StartCell$ DataList@)
;   Example: (PutCell "A1" (list "GP093" 58.5 17 "Base" "3'-6 1/4\""))
; CloseExcel - Closes Excel session
;   Syntax:  (CloseExcel ExcelFile$)
;   Example: (CloseExcel "C:\\Folder\\Filename.xls")
; Function example of usage:
; (defun c:Put-Example ()
;   (OpenExcel "C:\\Folder\\Filename.xls" "Sheet1" nil);<-- Edit Filename.xls
;   (PutCell "A1" (list "GP093" 58.5 17 "Base" "3'-6 1/4\""));Repeat as required
;   (CloseExcel "C:\\Folder\\Filename.xls");<-- Edit Filename.xls
;   (princ)
; );defun
;-------------------------------------------------------------------------------
; Note: Review the conditions of each argument in the function headings
;-------------------------------------------------------------------------------
; GetExcel - Stores the values from an Excel spreadsheet into *ExcelData@ list
; Arguments: 3
;   ExcelFile$ = Path and filename
;   SheetName$ = Sheet name or nil for not specified
;   MaxRange$ = Maximum cell ID range to include or nil to get the current region from cell A1
; Syntax examples:
; (GetExcel "C:\\Temp\\Temp.xls" "Sheet1" "E19") = Open C:\Temp\Temp.xls on Sheet1 and read up to cell E19
; (GetExcel "C:\\Temp\\Temp.xls" nil "XYZ123") = Open C:\Temp\Temp.xls on current sheet and read up to cell XYZ123
;-------------------------------------------------------------------------------
(defun GetExcel (ExcelFile$ SheetName$ MaxRange$ / Column# ColumnRow@ Data@ ExcelRange^
  ExcelValue ExcelValue ExcelVariant^ MaxColumn# MaxRow# Range$ Row# Worksheet)
  (if (= (type ExcelFile$) 'STR)
    (if (not (findfile ExcelFile$))
      (progn
        (alert (strcat "Excel file " ExcelFile$ " not found."))
        (exit)
      );progn
    );if
    (progn
      (alert "Excel file not specified.")
      (exit)
    );progn
  );if
  (gc)
  (if (setq *ExcelApp% (vlax-get-object "Excel.Application"))
    (progn
      (alert "Close all Excel spreadsheets to continue!")
      (vlax-release-object *ExcelApp%)(gc)
    );progn
  );if
  (setq ExcelFile$ (findfile ExcelFile$))
  (setq *ExcelApp% (vlax-get-or-create-object "Excel.Application"))
  (vlax-invoke-method (vlax-get-property *ExcelApp% 'WorkBooks) 'Open ExcelFile$)
  (if SheetName$
    (vlax-for Worksheet (vlax-get-property *ExcelApp% "Sheets")
      (if (= (vlax-get-property Worksheet "Name") SheetName$)
        (vlax-invoke-method Worksheet "Activate")
      );if
    );vlax-for
  );if
  (if MaxRange$
    (progn
      (setq ColumnRow@ (ColumnRow MaxRange$))
      (setq MaxColumn# (nth 0 ColumnRow@))
      (setq MaxRow# (nth 1 ColumnRow@))
    );progn
    (progn
      (setq CurRegion (vlax-get-property (vlax-get-property
        (vlax-get-property *ExcelApp% "ActiveSheet") "Range" "A1") "CurrentRegion")
      );setq
      (setq MaxRow# (vlax-get-property (vlax-get-property CurRegion "Rows") "Count"))
      (setq MaxColumn# (vlax-get-property (vlax-get-property CurRegion "Columns") "Count"))
    );progn
  );if
  (setq *ExcelData@ nil)
  (setq Row# 1)
  (repeat MaxRow#
    (setq Data@ nil)
    (setq Column# 1)
    (repeat MaxColumn#
      (setq Range$ (strcat (Number2Alpha Column#)(itoa Row#)))
      (setq ExcelRange^ (vlax-get-property *ExcelApp% "Range" Range$))
      (setq ExcelVariant^ (vlax-get-property ExcelRange^ 'Value))
      (setq ExcelValue (vlax-variant-value ExcelVariant^))
      (setq ExcelValue
        (cond
          ((= (type ExcelValue) 'INT) (itoa ExcelValue))
          ((= (type ExcelValue) 'REAL) (rtosr ExcelValue))
          ((= (type ExcelValue) 'STR) (vl-string-trim " " ExcelValue))
          ((/= (type ExcelValue) 'STR) "")
        );cond
      );setq
      (setq Data@ (append Data@ (list ExcelValue)))
      (setq Column# (1+ Column#))
    );repeat
    (setq *ExcelData@ (append *ExcelData@ (list Data@)))
    (setq Row# (1+ Row#))
  );repeat
  (vlax-invoke-method (vlax-get-property *ExcelApp% "ActiveWorkbook") 'Close :vlax-False)
  (vlax-invoke-method *ExcelApp% 'Quit)
  (vlax-release-object *ExcelApp%)(gc)
  (setq *ExcelApp% nil)
  *ExcelData@
);defun GetExcel
;-------------------------------------------------------------------------------
; GetCell - Returns the cell value from the *ExcelData@ list
; Arguments: 1
;   Cell$ = Cell ID
; Syntax example: (GetCell "E19") = value of cell E19
;-------------------------------------------------------------------------------
(defun GetCell (Cell$ / Column# ColumnRow@ Return Row#)
  (setq ColumnRow@ (ColumnRow Cell$))
  (setq Column# (1- (nth 0 ColumnRow@)))
  (setq Row# (1- (nth 1 ColumnRow@)))
  (setq Return "")
  (if *ExcelData@
    (if (and (>= (length *ExcelData@) Row#)(>= (length (nth 0 *ExcelData@)) Column#))
      (setq Return (nth Column# (nth Row# *ExcelData@)))
    );if
  );if
  Return
);defun GetCell
;-------------------------------------------------------------------------------
; OpenExcel - Opens an Excel spreadsheet
; Arguments: 3
;   ExcelFile$ = Excel filename or nil for new spreadsheet
;   SheetName$ = Sheet name or nil for not specified
;   Visible = t for visible or nil for hidden
; Syntax examples:
; (OpenExcel "C:\\Temp\\Temp.xls" "Sheet2" t) = Opens C:\Temp\Temp.xls on Sheet2 as visible session
; (OpenExcel "C:\\Temp\\Temp.xls" nil nil) = Opens C:\Temp\Temp.xls on current sheet as hidden session
; (OpenExcel nil "Parts List" nil) =  Opens a new spreadsheet and creates a Part List sheet as hidden session
;-------------------------------------------------------------------------------
(defun OpenExcel (ExcelFile$ SheetName$ Visible / Sheet$ Sheets@ Worksheet)
  (if (= (type ExcelFile$) 'STR)
    (if (findfile ExcelFile$)
      (setq *ExcelFile$ ExcelFile$)
      (progn
        (alert (strcat "Excel file " ExcelFile$ " not found."))
        (exit)
      );progn
    );if
    (setq *ExcelFile$ "")
  );if
  (gc)
  (if (setq *ExcelApp% (vlax-get-object "Excel.Application"))
    (progn
      (alert "Close all Excel spreadsheets to continue!")
      (vlax-release-object *ExcelApp%)(gc)
    );progn
  );if
  (setq *ExcelApp% (vlax-get-or-create-object "Excel.Application"))
  (if ExcelFile$
    (if (findfile ExcelFile$)
      (vlax-invoke-method (vlax-get-property *ExcelApp% 'WorkBooks) 'Open ExcelFile$)
      (vlax-invoke-method (vlax-get-property *ExcelApp% 'WorkBooks) 'Add)
    );if
    (vlax-invoke-method (vlax-get-property *ExcelApp% 'WorkBooks) 'Add)
  );if
  (if Visible
    (vla-put-visible *ExcelApp% :vlax-true)
  );if
  (if (= (type SheetName$) 'STR)
    (progn
      (vlax-for Sheet$ (vlax-get-property *ExcelApp% "Sheets")
        (setq Sheets@ (append Sheets@ (list (vlax-get-property Sheet$ "Name"))))
      );vlax-for
      (if (member SheetName$ Sheets@)
        (vlax-for Worksheet (vlax-get-property *ExcelApp% "Sheets")
          (if (= (vlax-get-property Worksheet "Name") SheetName$)
            (vlax-invoke-method Worksheet "Activate")
          );if
        );vlax-for
        (vlax-put-property (vlax-invoke-method (vlax-get-property *ExcelApp% "Sheets") "Add") "Name" SheetName$)
      );if
    );progn
  );if
  (princ)
);defun OpenExcel
;-------------------------------------------------------------------------------
; PutCell - Put values into Excel cells
; Arguments: 2
;   StartCell$ = Starting Cell ID
;   Data@ = Value or list of values
; Syntax examples:
; (PutCell "A1" "PART NUMBER") = Puts PART NUMBER in cell A1
; (PutCell "B3" '("Dim" 7.5 "9.75")) = Starting with cell B3 put Dim, 7.5, and 9.75 across
;-------------------------------------------------------------------------------
(defun PutCell (StartCell$ Data@ / Cell$ Column# ExcelRange Row#)
  (if (= (type Data@) 'STR)
    (setq Data@ (list Data@))
  )
  (setq ExcelRange (vlax-get-property *ExcelApp% "Cells"))
  (if (Cell-p StartCell$)
    (setq Column# (car (ColumnRow StartCell$))
          Row# (cadr (ColumnRow StartCell$))
    );setq
    (if (vl-catch-all-error-p
          (setq Cell$ (vl-catch-all-apply 'vlax-get-property
            (list (vlax-get-property *ExcelApp% "ActiveSheet") "Range" StartCell$))
          );setq
        );vl-catch-all-error-p
        (alert (strcat "The cell ID \"" StartCell$ "\" is invalid."))
        (setq Column# (vlax-get-property Cell$ "Column")
              Row# (vlax-get-property Cell$ "Row")
        );setq
    );if
  );if
  (if (and Column# Row#)
    (foreach Item Data@
      (vlax-put-property ExcelRange "Item" Row# Column# (vl-princ-to-string Item))
      (setq Column# (1+ Column#))
    );foreach
  );if
  (princ)
);defun PutCell
;-------------------------------------------------------------------------------
; CloseExcel - Closes Excel spreadsheet
; Arguments: 1
;   ExcelFile$ = Excel saveas filename or nil to close without saving
; Syntax examples:
; (CloseExcel "C:\\Temp\\Temp.xls") = Saveas C:\Temp\Temp.xls and close
; (CloseExcel nil) = Close without saving
;-------------------------------------------------------------------------------
(defun CloseExcel (ExcelFile$ / Saveas)
  (if ExcelFile$
    (if (= (strcase ExcelFile$) (strcase *ExcelFile$))
      (if (findfile ExcelFile$)
        (vlax-invoke-method (vlax-get-property *ExcelApp% "ActiveWorkbook") "Save")
        (setq Saveas t)
      );if
      (if (findfile ExcelFile$)
        (progn
          (vl-file-delete (findfile ExcelFile$))
          (setq Saveas t)
        );progn
        (setq Saveas t)
      );if
    );if
  );if
  (if Saveas
    (vlax-invoke-method (vlax-get-property *ExcelApp% "ActiveWorkbook")
      "SaveAs" ExcelFile$ -4143 "" "" :vlax-false :vlax-false nil
    );vlax-invoke-method
  );if
  (vlax-invoke-method (vlax-get-property *ExcelApp% "ActiveWorkbook") 'Close :vlax-False)
  (vlax-invoke-method *ExcelApp% 'Quit)
  (vlax-release-object *ExcelApp%)(gc)
  (setq *ExcelApp% nil *ExcelFile$ nil)
  (princ)
);defun CloseExcel
;-------------------------------------------------------------------------------
; ColumnRow - Returns a list of the Column and Row number
; Function By: Gilles Chanteau from Marseille, France
; Arguments: 1
;   Cell$ = Cell ID
; Syntax example: (ColumnRow "ABC987") = '(731 987)
;-------------------------------------------------------------------------------
(defun ColumnRow (Cell$ / Column$ Char$ Row#)
  (setq Column$ "")
  (while (< 64 (ascii (setq Char$ (strcase (substr Cell$ 1 1)))) 91)
    (setq Column$ (strcat Column$ Char$)
          Cell$ (substr Cell$ 2)
    );setq
  );while
  (if (and (/= Column$ "") (numberp (setq Row# (read Cell$))))
    (list (Alpha2Number Column$) Row#)
    '(1 1);default to "A1" if there's a problem
  );if
);defun ColumnRow
;-------------------------------------------------------------------------------
; Alpha2Number - Converts Alpha string into Number
; Function By: Gilles Chanteau from Marseille, France
; Arguments: 1
;   Str$ = String to convert
; Syntax example: (Alpha2Number "ABC") = 731
;-------------------------------------------------------------------------------
(defun Alpha2Number (Str$ / Num#)
  (if (= 0 (setq Num# (strlen Str$)))
    0
    (+ (* (- (ascii (strcase (substr Str$ 1 1))) 64) (expt 26 (1- Num#)))
       (Alpha2Number (substr Str$ 2))
    );+
  );if
);defun Alpha2Number
;-------------------------------------------------------------------------------
; Number2Alpha - Converts Number into Alpha string
; Function By: Gilles Chanteau from Marseille, France
; Arguments: 1
;   Num# = Number to convert
; Syntax example: (Number2Alpha 731) = "ABC"
;-------------------------------------------------------------------------------
(defun Number2Alpha (Num# / Val#)
  (if (< Num# 27)
    (chr (+ 64 Num#))
    (if (= 0 (setq Val# (rem Num# 26)))
      (strcat (Number2Alpha (1- (/ Num# 26))) "Z")
      (strcat (Number2Alpha (/ Num# 26)) (chr (+ 64 Val#)))
    );if
  );if
);defun Number2Alpha
;-------------------------------------------------------------------------------
; Cell-p - Evaluates if the argument Cell$ is a valid cell ID
; Function By: Gilles Chanteau from Marseille, France
; Arguments: 1
;   Cell$ = String of the cell ID to evaluate
; Syntax examples: (Cell-p "B12") = t, (Cell-p "BT") = nil
;-------------------------------------------------------------------------------
(defun Cell-p (Cell$)
  (and (= (type Cell$) 'STR)
    (or (= (strcase Cell$) "A1")
      (not (equal (ColumnRow Cell$) '(1 1)))
    );or
  );and
);defun Cell-p
;-------------------------------------------------------------------------------
; Row+n - Returns the cell ID located a number of rows from cell
; Function By: Gilles Chanteau from Marseille, France
; Arguments: 2
;   Cell$ = Starting cell ID
;   Num# = Number of rows from cell
; Syntax examples: (Row+n "B12" 3) = "B15", (Row+n "B12" -3) = "B9"
;-------------------------------------------------------------------------------
(defun Row+n (Cell$ Num#)
  (setq Cell$ (ColumnRow Cell$))
  (strcat (Number2Alpha (car Cell$)) (itoa (max 1 (+ (cadr Cell$) Num#))))
);defun Row+n
;-------------------------------------------------------------------------------
; Column+n - Returns the cell ID located a number of columns from cell
; Function By: Gilles Chanteau from Marseille, France
; Arguments: 2
;   Cell$ = Starting cell ID
;   Num# = Number of columns from cell
; Syntax examples: (Column+n "B12" 3) = "E12", (Column+n "B12" -1) = "A12"
;-------------------------------------------------------------------------------
(defun Column+n (Cell$ Num#)
  (setq Cell$ (ColumnRow Cell$))
  (strcat (Number2Alpha (max 1 (+ (car Cell$) Num#))) (itoa (cadr Cell$)))
);defun Column+n
;-------------------------------------------------------------------------------
; rtosr - Used to change a real number into a short real number string
; stripping off all trailing 0's.
; Arguments: 1
;   RealNum~ = Real number to convert to a short string real number
; Returns: ShortReal$ the short string real number value of the real number.
;-------------------------------------------------------------------------------
(defun rtosr (RealNum~ / DimZin# ShortReal$)
  (setq DimZin# (getvar "DIMZIN"))
  (setvar "DIMZIN" 8)
  (setq ShortReal$ (rtos RealNum~ 2 8))
  (setvar "DIMZIN" DimZin#)
  ShortReal$
);defun rtosr
;-------------------------------------------------------------------------------
(princ);End of GetExcel.lsp
I could use some assistance.

[Willie]

http://cadtips.cadalyst.com/notestext/text-alignment
 
Maybe this will be op help.  If you explode an Mtext, you can align the text.

[danallen]

Again, speaking to tables... our client doesn't tolerate Mtext.   When you explode a table, all the text becomes MTEXT.  I then have to explode it... Assuming the Mtext has an alignment of bottom center, when you explode it the text doesn't move, but by default, the anchor point becomes "LEFT" not center.  I have to manually change the text from "LEFT" to "CENTER" and then re-align all the text to their expected positions.  That's a lot of work for 10 columns by 300 dwgs.  Rea-positioning 3000 pieces of text is not my idea of a fun time.

Is anyone editing the column after it is created since the source is an excel file? If not, does the justification really matter? If it does, express tools has a command to change justification without moving text, TJUST

[ronjonp]

Here's something to pull the text into the drawing from a CSV file matching the properties of a selected piece of text. You still have to cleanup your data in the spreadsheet, but it's a start .

Code - Auto/Visual Lisp: [Select]

1. (defun c:importcsv (/ _d _readcsvfile d file i p text txt x y)
2.   (defun _d (elist / r)
3.     (if (setq r (textbox elist))
4.       (caadr r)
5.     )
6.   )
7.   (defun _readcsvfile (filename / file i line out tmp)
8.     (if (and (= 'str (type filename))
9.              (setq filename (findfile filename))
10.              (setq file (open filename "r"))
11.         )
12.       (progn (while (setq line (read-line file))
13.                (setq tmp nil)
14.                (while (or (setq i (vl-string-search "," line)) (> (strlen line) 0))
15.                  (if i
16.                    (setq tmp  (cons (substr line 1 i) tmp)
17.                          line (substr line (+ 2 i))
18.                    )
19.                    (setq tmp  (cons line tmp)
20.                          line (substr line (1+ (strlen line)))
21.                    )
22.                  )
23.                )
24.                (setq out (cons (reverse tmp) out))
25.              )
26.              (close file)
27.              (reverse out)
28.       )
29.     )
30.   )
31.   (setq i 0.2)
32.   (if (and (setq file (_readcsvfile (getfiled "Select a CSV File" (getvar 'dwgprefix) "csv" 16)))
33.            (setq txt (car (entsel "\nPick text to match properties: ")))
34.            (= "TEXT" (cdr (assoc 0 (setq txt (entget txt)))))
35.            (setq p (getpoint "\nPick a point to place data: "))
36.            (setq y (cadr p))
37.            (setq x (car p))
38.       )
39.     (while file
40.       (setq p (list x y (caddr p)))
41.       (foreach text (mapcar 'car file)
42.         (entmakex (mapcar '(lambda (x)
43.                              (cond ((= (car x) 1)
44.                                     (cons 1
45.                                           (if (= text "")
46.                                             "-"
47.                                             text
48.                                           )
49.                                     )
50.                                    )
51.                                    ((or (= (car x) 10) (= (car x) 11)) (cons (car x) p))
52.                                    (x)
53.                              )
54.                            )
55.                           txt
56.                   )
57.         )
58.         (setq p (list (car p) (- (cadr p) i) (caddr p)))
59.       )
60.       (setq d (apply 'max
61.                      (mapcar '(lambda (x) (_d (subst (cons 1 x) (assoc 1 txt) txt))) (mapcar 'car file))
62.               )
63.             d (+ d
64.                  (apply 'max
65.                         (mapcar '(lambda (x) (_d (subst (cons 1 x) (assoc 1 txt) txt)))
66.                                 (mapcar 'car (setq file (vl-remove 'nil (mapcar 'cdr file))))
67.                         )
68.                  )
69.               )
70.             x (+ (car p) (/ d 1.5))
71.       )
72.     )
73.   )
74.   (princ)
75. )

[DIW_CADtech]

Thank you, I will give this a try Wednessday,  and see what I can do with it...

Many thanks RonJonP

[ronjonp]

Thank you, I will give this a try Wednessday,  and see what I can do with it...

Many thanks RonJonP

Hope it helps you out

[DIW_CADtech]

I just couldn't wait til Wednesday.   

How does the script determine the horizontal location...  I want to modify that to make the gap a bit wider..

I will get some more help with this after, but I will start a new thread.  At first try this looks pretty awesome.

Some things I can likely change... don't need a prompt for the location.. it will always be the same point.  However, wondering if I can set the horizontal locations.  Right now it is pretty darn close, but If I can get that lined up, this may be doable pretty much as is.

[ronjonp]

I just couldn't wait til Wednesday.   

How does the script determine the horizontal location...  I want to modify that to make the gap a bit wider..

I will get some more help with this after, but I will start a new thread.  At first try this looks pretty awesome.

Some things I can likely change... don't need a prompt for the location.. it will always be the same point.  However, wondering if I can set the horizontal locations.  Right now it is pretty darn close, but If I can get that lined up, this may be doable pretty much as is.

The horizontal location is the largest text width of the current column being processed + the largest text width of the next column to be processed divided by 1.5 ( line 70 in the code above ) If you want to make the gaps larger, divide by a smaller number.

Code - Auto/Visual Lisp: [Select]

1. (+ (car p) (/ d 1.25))

[DIW_CADtech]

I will adjust that for now, and start a new thread to see if I can use specific X's.  In this spreadsheet/index for this client the X's will always be the same.

[DIW_CADtech]

Is anyone editing the column after it is created since the source is an excel file? If not, does the justification really matter? If it does, express tools has a command to change justification without moving text, TJUST

Sometimes yes, sometimes No,

This is a constant problem for me.. as the project is supposed to be directed from the log, not the index.

At times, the engineer will take a short cut and update the index, and not the log, or the drawing sheets.  Or, he/she will update the log and not tell any CAD technician.   This is why I want to generate the script.   Per our client the Project log is the bible.  So it doesn't matter to me what anyone has done to the index, I will update it from the project Log(excel) file. 

Manually this can take over an hour to do, but with the script provided by RONJONP this can be accomplished in about 10 min.  It's pretty good.  I would like to cut minutes down to seconds. I will be looking to tweak it a bit further, but the heavy lifting is done.

I would still prefer to go directly to the project EXCEL spreadsheet and skip having to make a CSV file, and skip having to align the text to the proper X values for the columns.  Since the location of the data in the spreadsheet is always exactly the same.

Baby steps. 

[ronjonp]

Is anyone editing the column after it is created since the source is an excel file? If not, does the justification really matter? If it does, express tools has a command to change justification without moving text, TJUST

...
Manually this can take over an hour to do, but with the script provided by RONJONP this can be accomplished in about 10 min.  It's pretty good.  I would like to cut minutes down to seconds. I will be looking to tweak it a bit further, but the heavy lifting is done.
...

Maybe this version will cut your 10 minutes of time down to 2 . I hard coded values based on that example drawing, so all you have to do is select your data.

Code - Auto/Visual Lisp: [Select]

1. (defun c:importcsv (/ _d _maketext _readcsvfile d file i p txt x xlst)
2.   (defun _d (text / r)
3.     (if (setq r (textbox (list '(67 . 0)
4.                                '(8 . "ANNO-TEXT")
5.                                '(100 . "AcDbText")
6.                                '(40 . 0.09375)
7.                                (cons 1 text)
8.                                '(41 . 0.85)
9.                          )
10.                 )
11.         )
12.       (caadr r)
13.     )
14.   )
15.   (defun _maketext (string point)
16.     (entmakex (list '(0 . "TEXT")
17.                     '(100 . "AcDbEntity")
18.                     '(67 . 0)
19.                     '(8 . "ANNO-TEXT")
20.                     '(100 . "AcDbText")
21.                     (cons 10 point)
22.                     '(40 . 0.09375)
23.                     (cons 1 string)
24.                     '(50 . 0.0)
25.                     '(41 . 0.85)
26.                     '(51 . 0.0)
27.                     '(7 . "TEP")
28.                     '(71 . 0)
29.                     '(72 . 1)
30.                     (cons 11 point)
31.                     '(100 . "AcDbText")
32.                     '(73 . 0)
33.               )
34.     )
35.   )
36.   (defun _readcsvfile (filename / file i line out tmp)
37.     (if (and (= 'str (type filename))
38.              (setq filename (findfile filename))
39.              (setq file (open filename "r"))
40.         )
41.       (progn (while (setq line (read-line file))
42.                (setq tmp nil)
43.                (while (or (setq i (vl-string-search "," line)) (> (strlen line) 0))
44.                  (if i
45.                    (setq tmp  (cons (substr line 1 i) tmp)
46.                          line (substr line (+ 2 i))
47.                    )
48.                    (setq tmp  (cons line tmp)
49.                          line (substr line (1+ (strlen line)))
50.                    )
51.                  )
52.                )
53.                (setq out (cons (reverse tmp) out))
54.              )
55.              (close file)
56.              (reverse out)
57.       )
58.     )
59.   )
60.   ;; Create textstyle
61.   (if (not (tblobjname "style" "TEP"))
62.     (entmake '((0 . "STYLE")
63.                (100 . "AcDbSymbolTableRecord")
64.                (100 . "AcDbTextStyleTableRecord")
65.                (2 . "TEP")
66.                (70 . 0)
67.                (40 . 0.09375)
68.                (41 . 0.85)
69.                (50 . 0.0)
70.                (71 . 0)
71.                (42 . 0.125)
72.                (3 . "romans.shx")
73.                (4 . "")
74.               )
75.     )
76.   )
77.   ;; Column spacing
78.   (setq i 0.2)
79.   ;; Starting point
80.   (setq p '(0.45067 9.85 0.0))
81.   ;; Hard coded X values
82.   (setq xlst '(0.45067 1.20067 2.563 4.49174 5.58817 6.20067 7.95067 11.3745 14.4441 15.9507))
83.   (if (setq file (_readcsvfile (getfiled "Select a CSV File" (getvar 'dwgprefix) "csv" 16)))
84.     (while file
85.       (setq p (list (car xlst) 9.85 0.0))
86.       (foreach text (mapcar 'car file)
87.         (setq txt (_maketext (if (= text "")
88.                                "-"
89.                                text
90.                              )
91.                              p
92.                   )
93.         )
94.         (setq p (list (car p) (- (cadr p) i) (caddr p)))
95.       )
96.       (if (setq xlst (cdr xlst))
97.         (setq file (vl-remove 'nil (mapcar 'cdr file)))
98.         (setq d    (apply 'max (mapcar '(lambda (x) (_d x)) (mapcar 'car file)))
99.               d    (+ d
100.                       (apply 'max
101.                              (mapcar '(lambda (x) (_d x))
102.                                      (mapcar 'car (setq file (vl-remove 'nil (mapcar 'cdr file))))
103.                              )
104.                       )
105.                    )
106.               xlst (cons (+ (car p) (/ d 1.25)) xlst)
107.         )
108.       )
109.     )
110.   )
111.   (princ)
112. )

[danallen]

This is a constant problem for me.. as the project is supposed to be directed from the log, not the index.
At times, the engineer will take a short cut and update the index, and not the log, or the drawing sheets.  Or, he/she will update the log and not tell any CAD technician.

(I know this isn't your call, but...) Then why have the log? Seems like a procedural setup ripe for mistake, two sources of data, how do you know you aren't over-writing changes in the CAD file when you import the XLS log? (If the engineer actually does the right thing one day, but not the next.) If the CAD must be the master, perhaps a better system is to have the engineers give you documentation of the specific changes to make in the CAD and not duplicate the CAD in the XLS. I'd recommend talking to the "people in charge", about fixing the procedures so that if you go on vacation, or quit, someone else can easily pick up the changes without knowing custom procedures. (I usually say "if I get hit by a bus" to obfuscate the idea of me quitting). If they understand the hoops that you have to jump through, and the time you spend, perhaps they'll assist with ideas for a solution.

[DIW_CADtech]

Maybe this version will cut your 10 minutes of time down to 2 . I hard coded values based on that example drawing, so all you have to do is select your data.

Testing it out now.

Thanks again RonJonP

[DIW_CADtech]

RonJonP
That is much better...

Each index sheet can only show 50 lines.
I will essentially create each additional sheet from this first sheet.

Is there a way we can make the gap jump 10" instead of .2 on every 51st line?

In that way, I would only have to copy the header down 18" for each sheet. 

I could then do a saveas and just move/adjust  viewports for the additional sheets?

[DIW_CADtech]

One more thing,
How do I add your credentials to the script. 
It needs a proper author.

It should prompt when loaded "Writen by RonJonP from TheSwamp.org 06-21-2016" that would be sweet.

[ronjonp]

...
Is there a way we can make the gap jump 10" instead of .2 on every 51st line?
...

Here you go:

Code - Auto/Visual Lisp: [Select]

1. (defun c:importcsv (/ _d _maketext _readcsvfile d file i n p txt x xlst)
2.   (defun _d (text / r)
3.     (if (setq r (textbox (list '(67 . 0)
4.                                '(8 . "ANNO-TEXT")
5.                                '(100 . "AcDbText")
6.                                '(40 . 0.09375)
7.                                (cons 1 text)
8.                                '(41 . 0.85)
9.                          )
10.                 )
11.         )
12.       (caadr r)
13.       0.
14.     )
15.   )
16.   (defun _maketext (string point)
17.     (entmakex (list '(0 . "TEXT")
18.                     '(100 . "AcDbEntity")
19.                     '(67 . 0)
20.                     '(8 . "ANNO-TEXT")
21.                     '(100 . "AcDbText")
22.                     (cons 10 point)
23.                     '(40 . 0.09375)
24.                     (cons 1 string)
25.                     '(50 . 0.0)
26.                     '(41 . 0.85)
27.                     '(51 . 0.0)
28.                     '(7 . "TEP")
29.                     '(71 . 0)
30.                     '(72 . 1)
31.                     (cons 11 point)
32.                     '(100 . "AcDbText")
33.                     '(73 . 0)
34.               )
35.     )
36.   )
37.   (defun _readcsvfile (filename / file i line out tmp)
38.     (if (and (= 'str (type filename))
39.              (setq filename (findfile filename))
40.              (setq file (open filename "r"))
41.         )
42.       (progn (while (setq line (read-line file))
43.                (setq tmp nil)
44.                (while (or (setq i (vl-string-search "," line)) (> (strlen line) 0))
45.                  (if i
46.                    (setq tmp  (cons (substr line 1 i) tmp)
47.                          line (substr line (+ 2 i))
48.                    )
49.                    (setq tmp  (cons line tmp)
50.                          line (substr line (1+ (strlen line)))
51.                    )
52.                  )
53.                )
54.                (setq out (cons (reverse tmp) out))
55.              )
56.              (close file)
57.              (reverse out)
58.       )
59.     )
60.   )
61.   ;; Create textstyle
62.   (if (not (tblobjname "style" "TEP"))
63.     (entmake '((0 . "STYLE")
64.                (100 . "AcDbSymbolTableRecord")
65.                (100 . "AcDbTextStyleTableRecord")
66.                (2 . "TEP")
67.                (70 . 0)
68.                (40 . 0.09375)
69.                (41 . 0.85)
70.                (50 . 0.0)
71.                (71 . 0)
72.                (42 . 0.125)
73.                (3 . "romans.shx")
74.                (4 . "")
75.               )
76.     )
77.   )
78.   ;; Starting point
79.   (setq p '(0.45067 9.85 0.0))
80.   ;; Hard coded X values
81.   (setq xlst '(0.45067 1.20067 2.563 4.49174 5.58817 6.20067 7.95067 11.3745 14.4441 15.9507))
82.   (if (setq file (_readcsvfile (getfiled "Select a CSV File" (getvar 'dwgprefix) "csv" 16)))
83.     (while file
84.       (setq p (list (car xlst) 9.85 0.0))
85.       (setq n 0)
86.       (foreach text (mapcar 'car file)
87.         (setq n (1+ n))
88.         (setq txt (_maketext (if (= text "")
89.                                "-"
90.                                text
91.                              )
92.                              p
93.                   )
94.         )
95.         (if (= 50 n)
96.           (setq i 10
97.                 n 0
98.           )
99.           (setq i 0.2)
100.         )
101.         (setq p (list (car p) (- (cadr p) i) (caddr p)))
102.       )
103.       (if (setq xlst (cdr xlst))
104.         (setq file (vl-remove 'nil (mapcar 'cdr file)))
105.         (setq d    (apply 'max (mapcar '(lambda (x) (_d x)) (mapcar 'car file)))
106.               d    (+ d
107.                       (apply 'max
108.                              (mapcar '(lambda (x) (_d x))
109.                                      (mapcar 'car (setq file (vl-remove 'nil (mapcar 'cdr file))))
110.                              )
111.                       )
112.                    )
113.               xlst (cons (+ (car p) (/ d 1.25)) xlst)
114.         )
115.       )
116.     )
117.   )
118.   (princ)
119. )

[ChrisCarlson]

One more thing,
How do I add your credentials to the script. 
It needs a proper author.

It should prompt when loaded "Writen by RonJonP from TheSwamp.org 06-21-2016" that would be sweet.

Typically doing the following will more than suffice the original author.

Code - Auto/Visual Lisp: [Select]

1. (defun c:importcsv (/ _d _maketext _readcsvfile d file i n p txt x xlst)
2. ;;Written by ronjonp @ TheSwamp 22-JUN-2016
3. ;;https://www.theswamp.org/index.php?topic=51580.msg566936#msg566936
4.   (defun _d (text / r)

[DIW_CADtech]

done!
Thanks Chris..  I was looking for more than just comments.. but that'll do..

[danallen]

add

Code: [Select]

(princ "\nWritten by ronjonp @ TheSwamp 22-JUN-2016")
put inside the defun and it will show every time it is run

[DIW_CADtech]

I am getting an error at the command prompt but the script seems to be functioning properly..

Command: APPLOAD
RONJONP3.lsp successfully loaded.
Command:
Command:
Command: IMPORTCSV
; error: bad argument type: numberp: nil
Command:

[DIW_CADtech]

This is the CSV file I'm using for testing.

[DIW_CADtech]

and the CAD file I'm starting with....

[Cathy]

I'm not sure what's going on with the .lsp, but I do note that your .csv has a lot of empty lines at the end.  Try opening it with notepad and deleting all those empty lines. 

[ronjonp]

I'm not sure what's going on with the .lsp, but I do note that your .csv has a lot of empty lines at the end.  Try opening it with notepad and deleting all those empty lines.

Agreed .. there are approximately 1200 'empty' rows after your data. I fixed the code in the previous post so it would not error.

119,58-09-0274,684870SH07,COEN - COMBUSTION ENGINEERS,7 OF 9,2,FUNCTIONAL LOGIC DIAGRAM,BURNER MANAGEMENT SYSTEM ,SOME/ PLANT COMMON,REVISED,
120,58-09-0275,684870SH08,COEN - COMBUSTION ENGINEERS,8 OF 9,1,FUNCTIONAL LOGIC DIAGRAM,BURNER MANAGEMENT SYSTEM ,SOME/ PLANT COMMON,REVISED,
121,58-09-0276,684870SH09,COEN - COMBUSTION ENGINEERS,9 OF 9,2,FUNCTIONAL LOGIC DIAGRAM,BURNER MANAGEMENT SYSTEM ,SOME/ PLANT COMMON,REVISED,
122,DE-58-09-0,D0681870SH06,COEN - COMBUSTION ENGINEERS,6 OF 10,3,SCHEM & CONN DIAG,MFT RELAY PANEL,SOME/ PLANT COMMON,REVISED,
,,,,,,,,,,
,,,,,,,,,,
,,,,,,,,,,
,,,,,,,,,,
,,,,,,,,,,
,,,,,,,,,,


...

[DIW_CADtech]

I was trying to standardize my file for generating the CSV files in Excel.   The 2nd tab with the corrected data is generated with formula's.. to avoid  getting "0" for empty cells.     The largest drawing set we've had to date was just under 1300 drawings.. so I carried the formula's to row 1300

in this set 103 DWGs.. so yes ~1200 blank rows.

Hoping to use this file repeatedly.. just dropping in the project log data on tab "Sheet1" and hopping to tab "Sheet2" and immediately saving to CSV.

Thank you for the code update.