Jump to content

How can we do this through EXcel


pmadhwal7

Recommended Posts

is anybody tell me how to import/export any text and paste it to it's original position in multiple layouts tab through excel as i have many layout tab in one dwg

Link to comment
Share on other sites

Sure.

 

Importing:

- Download the lisp file  for reading CSV files here: http://www.lee-mac.com/readcsv.html

Copy/paste the code to your code, or load that file

 

For my example, see attachment:

- You need "Layout1" and "Layout2" . 

- You need "Layer1" and "Layer2" . 

- Change the location of (setq csvfile "H:\\User\\lisp\\cadtutor\\import.csv") so that it matches where you saved the file.

You can use functions like getfiled (Google it) to dynamically pick the file.

 


(defun drawText (pt hgt str)
 (entmakex (list (cons 0 "TEXT")
                 (cons 10  pt)
                 (cons 40 hgt)
                 (cons 1  str)))
)

;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;

(vl-load-com)

;; read from csv
(defun c:rfc ( / textheight csvfile data i row)
  ;; settings
  (setq textheight 2.5)
  (setq csvfile "H:\\User\\lisp\\cadtutor\\import.csv")
 
  (setq data (LM:readcsv csvfile))
  (setq i 0)
  (foreach row data
    (if (> i 0) (progn    ;; skip the title row
    
      (setvar "ctab" (nth 0 row))
      (setvar "clayer" (nth 1 row))
      
      (drawText
        (list (atof (nth 2 row)) (atof (nth 3 row)))
        textheight
        (nth 4 row)
      )
    ))
 
    (setq i (+ i 1))
  )  

  (princ)
)

 

 

import.csv

Link to comment
Share on other sites

31 minutes ago, Emmanuel Delay said:

Sure.

 

Importing:

- Download the lisp file  for reading CSV files here: http://www.lee-mac.com/readcsv.html

Copy/paste the code to your code, or load that file

 

For my example, see attachment:

- You need "Layout1" and "Layout2" . 

- You need "Layer1" and "Layer2" . 

- Change the location of (setq csvfile "H:\\User\\lisp\\cadtutor\\import.csv") so that it matches where you saved the file.

You can use functions like getfiled (Google it) to dynamically pick the file.

 

 


(defun drawText (pt hgt str)
 (entmakex (list (cons 0 "TEXT")
                 (cons 10  pt)
                 (cons 40 hgt)
                 (cons 1  str)))
)

;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;

(vl-load-com)

;; read from csv
(defun c:rfc ( / textheight csvfile data i row)
  ;; settings
  (setq textheight 2.5)
  (setq csvfile "H:\\User\\lisp\\cadtutor\\import.csv")
 
  (setq data (LM:readcsv csvfile))
  (setq i 0)
  (foreach row data
    (if (> i 0) (progn    ;; skip the title row
    
      (setvar "ctab" (nth 0 row))
      (setvar "clayer" (nth 1 row))
      
      (drawText
        (list (atof (nth 2 row)) (atof (nth 3 row)))
        textheight
        (nth 4 row)
      )
    ))
 
    (setq i (+ i 1))
  )  

  (princ)
)

Thanks for your valuable reply sir but i am unable to use this lsp can you please check my attached dwg may you understand what i want to say

 

 

import.csv 356 B · 1 download

 

example.dwg

Link to comment
Share on other sites

5 hours ago, BIGAL said:

I thought this was answered remember Rlx provided solution for "1 of 6" was it here or at Autodesk/forum ?

 

 

thanks for your reply sir but sir this lisp din't export coordinates....also didn't import the same in same place that's why i ask

Link to comment
Share on other sites

pmadhwal7 the best solution for you is redo your title making it into a block with attributes. Then the out or in from excel is easy. I am pretty sure most of us experienced will agree make a title block.

 

I have numerous title block routines.

Link to comment
Share on other sites

55 minutes ago, BIGAL said:

pmadhwal7 the best solution for you is redo your title making it into a block with attributes. Then the out or in from excel is easy. I am pretty sure most of us experienced will agree make a title block.

 

I have numerous title block routines.

sir the problem is i have nearly 500-600 layout in my file

Link to comment
Share on other sites

 

Today is the start of using the new title block.

 

Ok so you have hundreds of layouts write 1 lisp to convert to a title block you have already invested in the time to find most of the answers required even if you only do the text part and leave line work as is. My old work Title block was 2 parts text and linework/logos

 

I have a move title so that would be 1st step to make sure the layout border/title  is always at the same co-ordinates.

 

image.thumb.png.a73f6532c92c49b96e55d6eda1cec0ba.png

image.png.868fa9b06c2583d5a7d74ac3733f1be3.png

Edited by BIGAL
Link to comment
Share on other sites

5 hours ago, BIGAL said:

 

Today is the start of using the new title block.

 

Ok so you have hundreds of layouts write 1 lisp to convert to a title block you have already invested in the time to find most of the answers required even if you only do the text part and leave line work as is. My old work Title block was 2 parts text and linework/logos

 

I have a move title so that would be 1st step to make sure the layout border/title  is always at the same co-ordinates.

 

image.thumb.png.a73f6532c92c49b96e55d6eda1cec0ba.png

image.png.868fa9b06c2583d5a7d74ac3733f1be3.png

sir,

i only want to patch length and sheet number to export in to the excel and same import in the cad file....because our people do many mistake in patch length. please help sir this the very important step in our work

Edited by pmadhwal7
Link to comment
Share on other sites

think you better take Bigal's advise and start using borders with attributes (and also learn from examples yourself)


(defun c:t3 ( / ss filter i e edat x y lay rot hgt result-list)
  (vl-load-com) (defun *error* (msg) (princ msg))
  (setq filter (strcase "Patch Length*,#*of #*"))
  (foreach lay (layoutlist)
    (setvar "CTAB" lay)
    (if (setq ss (ssget "X" (list '(0 . "TEXT") (cons 410 (getvar "CTAB")))))
      (repeat (setq i (sslength ss))
        (setq e (ssname ss (setq i (1- i))) edat (entget e) s (cdr (assoc 1 edat)))
        (if (wcmatch (strcase s) filter)(add_to_result_list edat lay))
      )
    )
  )
  (if (vl-consp result-list)
    (write_csv_file result-list)(princ"\n\nNo data to write"))
  (princ)
)

(defun add_to_result_list ( dat tab / s x y r h i l )
  (if (not (setq h (assoc 40 dat)))
    (setq h (rtos (getvar "textsize") 2 4))(setq h (rtos (cdr h) 2 4)))
  (setq i (cdr (assoc 10 dat)) x (rtos (car i) 2 4) y (rtos (cadr i) 2 4))
  (setq l (cdr (assoc 8 dat)) s (cdr (assoc 1 dat)) r (rtos (rtd (cdr (assoc 50 dat))) 2 4))
  (setq result-list (cons (strcat tab ";" l ";" x ";" y ";" h ";" r ";" s) result-list))
)

(defun write_csv_file ( lst / csv-fn csv-fp)
  (cond
    ((not (vl-consp lst))(princ "\nNo data in list"))
    ((not (setq csv-fn (getfiled "Enter name for (new) csv file" (getvar 'dwgprefix) "csv" 1)))
     (princ "\nNo name was provided for csv file"))
    ((not (setq csv-fp (open csv-fn "w")))(princ "\nUnable to create csv file"))
    (t (write-line "Layout;Layer;X;Y;Height;Rotation;Tekst" csv-fp) (foreach item lst (write-line item csv-fp)))
  )
  (if csv-fp (progn (close csv-fp)(gc)))
  (if (and csv-fn (setq csv-fn (findfile csv-fn)))(startapp "notepad" csv-fn))
)

(defun read_csv_file ( / csv-fn csv-fp lst )
  (cond
    ((not (setq csv-fn (getfiled "Enter name for (existing) csv file" (getvar 'dwgprefix) "csv" 0)))
     (princ "\nNo name was provided for csv file"))
    ((not (setq csv-fp (open csv-fn "r")))(princ "\nUnable to read from csv file"))
    (t (while (setq data (read-line csv-fp))(setq lst (cons data lst)))
     (if csv-fp (close csv-fp)) (setq lst (mapcar '(lambda (x) (SplitSTr x ";")) lst)))
  )
  (reverse lst)
)


;;; tiny lisp section
(defun rtd (r) (* 180.0 (/ r pi))) (defun dtr (d) (* pi (/ d 180.0)))
(defun SplitStr ( s d / p )
  (if (setq p (vl-string-search d s))(cons (substr s 1 p) (SplitStr (substr s (+ p 1 (strlen d))) d)) (list s)))
(defun wcmember  (s l f)
  (vl-some '(lambda (x)(if f (vl-string-search x s)(vl-string-search (strcase x)(strcase s)))) l))
(defun add_tab ($lo)
  (if (and (snvalid $lo)(not (wcmember $lo (layoutlist) nil)))
    (vl-catch-all-apply 'vla-add (list (vla-get-layouts (vla-get-ActiveDocument (vlax-get-acad-object))) $lo))))
(defun add_layer ($la)
  (cond ((or (null $la) (not (snvalid $la))) "0") ((and (snvalid $la)(tblsearch "layer" $la)) $la)
    (t (if (not (vl-catch-all-error-p (vl-catch-all-apply 'vla-add (list (vla-get-layers
      (vla-get-activedocument (vlax-get-Acad-Object))) $la)))) $la "0"))))

 

;Layout;Layer;X;Y;Height;Rotation;Tekst
(defun c:t4 ( / lst tab lay x y h r s err)
  (if (not (vl-consp (setq lst (read_csv_file))))
    (alert "No csv data to show")
    (foreach item (reverse (cdr lst))
      (mapcar 'set (list 'tab 'lay 'x 'y 'h 'r 's) item)
      (add_tab tab)(setvar "CTAB" tab)(setq lay (add_layer lay))(setvar "CLAYER" lay)
      (if (vl-catch-all-error-p (setq err (vl-catch-all-apply 'vl-cmdf (list "-text" (strcat x "," y) h r s))))
        (princ (vl-catch-all-error-message err)))
    )
  )
)

 

c:t3 or just t3 is saving data , c:t4 is reading it back in a new file

 

 

Link to comment
Share on other sites

Okay, this works.

 

Commands

- ETC (Export To Csv).  Then select the area of the cartouche.  Those same coordinates will also be scanned for all other layouts (except Model).

- IFC (Import From Csv).  This means it will draw new text objects to the layouts.  The layouts must pre exist !!

- UFC (Update From Csv). This means the existing text elements get updated.  So you use the same dwg (or a copy).

 


;; http://www.lee-mac.com/writecsv.html
;; Write CSV  -  Lee Mac
;; Writes a matrix list of cell values to a CSV file.
;; lst - [lst] list of lists, sublist is row of cell values
;; csv - [str] filename of CSV file to write
;; Returns T if successful, else nil
(defun LM:writecsv ( lst csv / des sep )
    (if (setq des (open csv "w"))
        (progn
            (setq sep (cond ((vl-registry-read "HKEY_CURRENT_USER\\Control Panel\\International" "sList")) (",")))
            (foreach row lst (write-line (LM:lst->csv row sep) des))
            (close des)
            t
        )
    )
)

;; List -> CSV  -  Lee Mac
;; Concatenates a row of cell values to be written to a CSV file.
;; lst - [lst] list containing row of CSV cell values
;; sep - [str] CSV separator token
(defun LM:lst->csv ( lst sep )
    (if (cdr lst)
        (strcat (LM:csv-addquotes (car lst) sep) sep (LM:lst->csv (cdr lst) sep))
        (LM:csv-addquotes (car lst) sep)
    )
)

(defun LM:csv-addquotes ( str sep / pos )
    (cond
        (   (wcmatch str (strcat "*[`" sep "\"]*"))
            (setq pos 0)    
            (while (setq pos (vl-string-position 34 str pos))
                (setq str (vl-string-subst "\"\"" "\"" str pos)
                      pos (+ pos 2)
                )
            )
            (strcat "\"" str "\"")
        )
        (   str   )
    )
)

;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;

;; http://www.lee-mac.com/readcsv.html
;; Read CSV  -  Lee Mac
;; Parses a CSV file into a matrix list of cell values.
;; csv - [str] filename of CSV file to read
(defun LM:readcsv ( csv / des lst sep str )
    (if (setq des (open csv "r"))
        (progn
            (setq sep (cond ((vl-registry-read "HKEY_CURRENT_USER\\Control Panel\\International" "sList")) (",")))
            (while (setq str (read-line des))
                (setq lst (cons (LM:csv->lst str sep 0) lst))
            )
            (close des)
        )
    )
    (reverse lst)
)

;; CSV -> List  -  Lee Mac
;; Parses a line from a CSV file into a list of cell values.
;; str - [str] string read from CSV file
;; sep - [str] CSV separator token
;; pos - [int] initial position index (always zero)
(defun LM:csv->lst ( str sep pos / s )
    (cond
        (   (not (setq pos (vl-string-search sep str pos)))
            (if (wcmatch str "\"*\"")
                (list (LM:csv-replacequotes (substr str 2 (- (strlen str) 2))))
                (list str)
            )
        )
        (   (or (wcmatch (setq s (substr str 1 pos)) "\"*[~\"]")
                (and (wcmatch s "~*[~\"]*") (= 1 (logand 1 pos)))
            )
            (LM:csv->lst str sep (+ pos 2))
        )
        (   (wcmatch s "\"*\"")
            (cons
                (LM:csv-replacequotes (substr str 2 (- pos 2)))
                (LM:csv->lst (substr str (+ pos 2)) sep 0)
            )
        )
        (   (cons s (LM:csv->lst (substr str (+ pos 2)) sep 0)))
    )
)

(defun LM:csv-replacequotes ( str / pos )
    (setq pos 0)
    (while (setq pos (vl-string-search  "\"\"" str pos))
        (setq str (vl-string-subst "\"" "\"\"" str pos)
              pos (1+ pos)
        )
    )
    str
)

;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;

(defun drawText (pt hgt str rot)
 (entmakex (list (cons 0 "TEXT")
                 (cons 10  pt)
                 (cons 40 hgt)
                 (cons 1  str)
                 (cons 50  rot)
                 ))
)

;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;

(vl-load-com)

;; Read From Csv
(defun rfc (update / textheight csvfile data i row)
  ;; settings
 
  (setq csvfile (getfiled "Select File" (getvar 'dwgprefix) "csv" 16))
 
  (setq data (LM:readcsv csvfile))
  (setq i 0)
  (foreach row data
    (if (> i 0) (progn    ;; skip the title row
      ;; (list "Layout" "Layer" "X" "Y" "Tekst" "Entity" "Rotation" "Height")
      (setvar "ctab" (nth 0 row))
      (setvar "clayer" (nth 1 row))
      
      (if update
        (progn
          ;; find the text by entity and update the text contents
          (setq ent (handent (nth 5 row)))
          (entmod (subst
            (cons 1 (nth 4 row))
            (assoc 1 (entget ent))
            (entget ent)
          ))
        )
        (progn
          ;; Draw a new text
          (drawText
            (list (atof (nth 2 row)) (atof (nth 3 row)))
            (atof (nth 7 row))
            (nth 4 row)
            (atof (nth 6 row))
          )
        )
      )
      
    ))
    (setq i (+ i 1))
  )  
  (princ)
)


(defun point_between_window (pt bl tr)
  (and
    (>= (nth 0 pt) (nth 0 bl))
    (>= (nth 1 pt) (nth 1 bl))
    (<= (nth 0 pt) (nth 0 tr))
    (<= (nth 1 pt) (nth 1 tr))
  )  
)

;; Export To Csv
(defun etc ( / csvfile p1 p2 bl tr ss ss_texts i item pt layout)

  (setq csvfile (getfiled "Create or Select File" (getvar 'dwgprefix) "csv" 1))
 
  ;; User selects a window.  Regardless of the direction, a bottom-left and top-right point are extracted
  (princ "\nSet the window for the cartouche: ")
  (setq p1 (getpoint "\nPoint1: "))
  (setq p2 (getcorner p1 "\nPoint2: "))
  (setq bl (list (min (nth 0 p1) (nth 0 p2) ) (min (nth 1 p1) (nth 1 p2) )) )
  (setq tr (list (max (nth 0 p1) (nth 0 p2) ) (max (nth 1 p1) (nth 1 p2) )) )
 
  ;; ss selection.  First select everything, then check on all paperspaces if the text is within the window.
  ;; these get put in a seperate selection ss_texts
  (setq ss_texts (ssadd))
  (setq ss (ssget "_X" (list (cons 0 "TEXT"))))
  (setq i 0)
  (setq data (list
    (list "Layout" "Layer" "X" "Y" "Tekst" "Entity" "Rotation" "Height")
  ))
  (repeat (sslength ss)
    (setq pt (cdr (assoc 10 (entget (ssname ss i)))))
    (setq layout (cdr (assoc 410 (entget (ssname ss i)))))
    (if (and (point_between_window pt bl tr) (/= layout "Model"))  ;; only points between the window.  Also skip model
      (progn
        ;;
        (setq data (append data (list
          (list
            layout
            (cdr (assoc 8 (entget (ssname ss i))))
            (rtos (nth 0 (cdr (assoc 10 (entget (ssname ss i))))) 2 16)
            (rtos (nth 1 (cdr (assoc 10 (entget (ssname ss i))))) 2 16)
            (cdr (assoc 1 (entget (ssname ss i))))
            (cdr (assoc 5 (entget (ssname ss i))))
            (rtos (cdr (assoc 50 (entget (ssname ss i)))) 2 16)
            (rtos (cdr (assoc 40 (entget (ssname ss i)))) 2 16)
          )
        )))
      )
    )
    (setq i (+ i 1))
  )

  (LM:writecsv data csvfile)
  (princ (strcat "\n" csvfile "  - created"))
  (princ)
 
)

;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
;; COMMANDS

;; Export To Csv
(defun c:etc ( / )
  (etc)
  (princ)
)

;; Import From Csv
(defun c:ifc ( / )
  (rfc nil)
  (princ)
)

;; Update From Csv
(defun c:ufc ( / )
  (rfc T)
  (princ)
)

(princ "\nCommands: ETC to export / IFC to import / UFC to update: ")
(princ)

 

Now, there's Excel ... Excel tends to transform data.  Like

- every Layout name will be changed from 01, 02, 03... to 1, 2, 3. 

- sometimes the decimal separators / thousands separators can be a problem (some versions have reversed . & , )

- If Entity handles get read as numbers this can mess up everything.

- Excel might not use semicolon as the default separator.

 

One way is to adapt the csv file with Notepad.  That's a great program that doesn't mess up your data.

(Or maybe you have found Excel settings where it doesn't mess up data, feel free to tell me about that.)

 

Else, look how to import a CSV into Excel.  Tab Data -> (Get External Data) From Text

-> set the semicolon separator on step 2.

-> For every column set the correct data-type.

 

Edited by Emmanuel Delay
Link to comment
Share on other sites

Using later version of Excel can detect strings v's numbers did not play much, you have to open csv say notepad Ctrl+A Ctrl+c then use paste.

 

 

screenshot88.png

Edited by BIGAL
  • Like 1
Link to comment
Share on other sites

I would have thought by now you would have started to get some idea of how to get text from the dwg using lisp so changing text property layer can be done pretty easy.

 

(setq ss (ssget "x" (list (cons 0 "Text")(cons 1 "Patch Length*"))))

 

Again the sooner you convert your info to a block the easier all these questions can be answered.

Edited by BIGAL
Link to comment
Share on other sites

7 hours ago, BIGAL said:

I would have thought by now you would have started to get some idea of how to get text from the dwg using lisp so changing text property layer can be done pretty easy.

 

(setq ss (ssget "x" (list (cons 0 "Text")(cons 1 "Patch Length*"))))

 

Again the sooner you convert your info to a block the easier all these questions can be answered.

thanks sir i will try to go with your advice, please tell me where i can paste those code you given

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Unfortunately, your content contains terms that we do not allow. Please edit your content to remove the highlighted words below.
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...