pmadhwal7 Posted October 11, 2019 Share Posted October 11, 2019 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 Quote Link to comment Share on other sites More sharing options...
Emmanuel Delay Posted October 11, 2019 Share Posted October 11, 2019 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 Quote Link to comment Share on other sites More sharing options...
pmadhwal7 Posted October 11, 2019 Author Share Posted October 11, 2019 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 Quote Link to comment Share on other sites More sharing options...
pmadhwal7 Posted October 13, 2019 Author Share Posted October 13, 2019 Anybody have an idea please help me.... Quote Link to comment Share on other sites More sharing options...
BIGAL Posted October 13, 2019 Share Posted October 13, 2019 (edited) I thought this was answered remember Rlx provided solution for "1 of 6" was it here or at Autodesk/forum ? Edited October 13, 2019 by BIGAL Quote Link to comment Share on other sites More sharing options...
pmadhwal7 Posted October 14, 2019 Author Share Posted October 14, 2019 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 Quote Link to comment Share on other sites More sharing options...
BIGAL Posted October 14, 2019 Share Posted October 14, 2019 Admin please move into other post. Quote Link to comment Share on other sites More sharing options...
BIGAL Posted October 14, 2019 Share Posted October 14, 2019 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. Quote Link to comment Share on other sites More sharing options...
pmadhwal7 Posted October 14, 2019 Author Share Posted October 14, 2019 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 Quote Link to comment Share on other sites More sharing options...
BIGAL Posted October 14, 2019 Share Posted October 14, 2019 (edited) 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. Edited October 14, 2019 by BIGAL Quote Link to comment Share on other sites More sharing options...
pmadhwal7 Posted October 15, 2019 Author Share Posted October 15, 2019 (edited) 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. 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 October 15, 2019 by pmadhwal7 Quote Link to comment Share on other sites More sharing options...
rlx Posted October 15, 2019 Share Posted October 15, 2019 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 Quote Link to comment Share on other sites More sharing options...
Emmanuel Delay Posted October 15, 2019 Share Posted October 15, 2019 (edited) 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 October 15, 2019 by Emmanuel Delay Quote Link to comment Share on other sites More sharing options...
BIGAL Posted October 16, 2019 Share Posted October 16, 2019 (edited) 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. Edited October 16, 2019 by BIGAL 1 Quote Link to comment Share on other sites More sharing options...
pmadhwal7 Posted October 16, 2019 Author Share Posted October 16, 2019 (edited) sir, how can i change the layer of patch length in all layout?? Edited October 16, 2019 by pmadhwal7 Quote Link to comment Share on other sites More sharing options...
BIGAL Posted October 16, 2019 Share Posted October 16, 2019 (edited) 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 October 16, 2019 by BIGAL Quote Link to comment Share on other sites More sharing options...
pmadhwal7 Posted October 17, 2019 Author Share Posted October 17, 2019 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 Quote Link to comment Share on other sites More sharing options...
Recommended Posts
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.