Add text to drawing from excel file

Hi friends,

i need add text from excel file to cad drawing lisp routine. read drawing text and excel file text, if matches paste relevant from excel to cad.

for example: my drawing text contains "B2" and my excel file at "D" column contains "B2" text then paste value from "E" column of excel file to Cad drawing beside B2 Text.

please find sample files of Cad and Excel files.

Thanks.

sample drawing.dxf

First, save your excel file to csv and then try with this:

[color=RED]([/color][color=BLUE]defun[/color] c:csv2txt [color=RED]([/color][color=BLUE]/[/color] file data ss in enx txt addtxt[color=RED])[/color]
 [color=RED]([/color][color=BLUE]if[/color]
   [color=RED]([/color][color=BLUE]and[/color]
     [color=RED]([/color][color=BLUE]setq[/color] file [color=RED]([/color][color=BLUE]getfiled[/color] [color=#a52a2a]"Select CSV File"[/color] [color=#a52a2a]""[/color] [color=#a52a2a]"csv"[/color] [color=#009900]16[/color][color=RED]))[/color]
     [color=RED]([/color][color=BLUE]setq[/color] data [color=RED]([/color][color=BLUE]LM:readcsv[/color] file[color=RED]))[/color]
     [color=RED]([/color][color=BLUE]setq[/color] ss [color=RED]([/color][color=BLUE]ssget[/color] [color=DARKRED]'[/color][color=RED](([/color][color=#009900]0[/color] [color=DARKRED].[/color] [color=#a52a2a]"TEXT"[/color][color=RED]))))[/color]
   [color=RED])[/color]
   [color=RED]([/color][color=BLUE]repeat[/color] [color=RED]([/color][color=BLUE]setq[/color] in [color=RED]([/color][color=BLUE]sslength[/color] ss[color=RED]))[/color]
     [color=RED]([/color][color=BLUE]setq[/color] enx [color=RED]([/color][color=BLUE]entget[/color] [color=RED]([/color][color=BLUE]ssname[/color] ss [color=RED]([/color][color=BLUE]setq[/color] in [color=RED]([/color][color=BLUE]1-[/color] in[color=RED])))))[/color]
     [color=RED]([/color][color=BLUE]setq[/color] txt [color=RED]([/color][color=BLUE]cdr[/color] [color=RED]([/color][color=BLUE]assoc[/color] [color=#009900]1[/color] enx[color=RED])))[/color]
     [color=RED]([/color][color=BLUE]foreach[/color] _X data
       [color=RED]([/color][color=BLUE]if[/color] [color=RED]([/color][color=BLUE]setq[/color] addtxt [color=RED]([/color][color=BLUE]cadr[/color][color=RED]([/color][color=BLUE]member[/color] txt _X[color=RED])))[/color]
         [color=RED]([/color][color=BLUE]progn[/color]
           [color=RED]([/color][color=BLUE]setq[/color] enx [color=RED]([/color][color=BLUE]subst[/color] [color=RED]([/color][color=BLUE]cons[/color] [color=#009900]1[/color] [color=RED]([/color][color=BLUE]strcat[/color] txt addtxt[color=RED]))[/color] [color=RED]([/color][color=BLUE]assoc[/color] [color=#009900]1[/color] enx[color=RED])[/color] enx [color=RED]))[/color]
           [color=RED]([/color][color=BLUE]entmod[/color] enx[color=RED])[/color]
           [color=RED]([/color][color=BLUE]entupd[/color] [color=RED]([/color][color=BLUE]ssname[/color] ss in[color=RED]))[/color]
         [color=RED])[/color]
       [color=RED])[/color]
     [color=RED])[/color]
   [color=RED])[/color]
 [color=RED])[/color]
 [color=RED]([/color][color=BLUE]princ[/color][color=RED])[/color]
[color=RED])[/color]

Ofcourse you have to load this one

http://www.lee-mac.com/readcsv.html

Hi friend,

thank you for sharing. after using your code showing error at command line as "CSV2TXT ; error: no function definition: LM:READCSV". Text was not developed. please have a look.

sample drawing.dxf

Test file.csv

This is somthing you have to download from here: http://www.lee-mac.com/readcsv.html

This is somthing you have to download from here: http://www.lee-mac.com/readcsv.html

Dear friend, i dont know about programing skills. please contribute lisp routine.

Thank you.

Hi friend,

thank you for sharing. after using your code showing error at command line as "CSV2TXT ; error: no function definition: LM:READCSV". Text was not developed. please have a look.

I gave you link to site where you can find missing function.

once again, but with direct link to file which you have to load to run my routine.

http://www.lee-mac.com/lisp/ReadCSV-V1-3.lsp

Consider this (some specific text reformatting was required) :

(defun C:test ( / acDoc SSX f opn row LstRows i TxtLst a b)

(setq acDoc (vla-get-ActiveDocument (vlax-get-acad-object)))
(vlax-map-collection (vla-get-Layers acDoc) (function (lambda (o) (vla-put-Lock o :vlax-false))))
(if 
	(and
		(setq SSX (ssget "_X" (list (cons 0 "TEXT") (if (= 1 (getvar 'cvport)) (cons 410 (getvar 'ctab)) '(410 . "Model")))))
		(setq f (getfiled "Select CSV File" "" "csv" 0))
	)
	(progn
		(vla-EndUndoMark acDoc)(vla-StartUndoMark acDoc)
		(setq opn (open f "r"))
		(while (setq row (read-line opn)) (setq LstRows (cons row LstRows)))
		(close opn)
		(repeat (setq i (sslength SSX)) (setq TxtLst (cons (entget (ssname SSX (setq i (1- i))))  TxtLst)) )
		(if (and LstRows TxtLst)
			(foreach a LstRows
				(foreach b TxtLst
					(and ; sloppy string reformatting below
						(wcmatch a (strcat "*" (cdr (assoc 1 b)) "*"))
						(setq a (vl-string-subst "(" "Reg" a))
						(setq a (vl-remove "\"" (vl-remove "," (mapcar 'chr (vl-string->list a)))))
						(if (/= (last a) ")") (setq a (strcat (apply 'strcat a) ")")) (setq a (apply 'strcat a)))
						(setq a (vl-string-subst "\")" ")" (vl-string-subst "\"x" "x" a)))
						(entmod (setq b (subst (cons 1 a) (assoc 1 b) b)))
						(entupd (cdr (assoc -1 b)))
					)
				)
			)
		)
		(vla-EndUndoMark acDoc)
	)
)
(princ)
);| defun |; (vl-load-com) (princ)

I gave you link to site where you can find missing function.

once again, but with direct link to file which you have to load to run my routine.

http://www.lee-mac.com/lisp/ReadCSV-V1-3.lsp

Dear friend,

i followed your rules but developing "reg" instead of sizes like 9"x18". and missing Brackets after text.example:B1(9"x4½") format.

Thank you.

Consider this (some specific text reformatting was required) :

 

Hi friend your code is good working. but one column text from excel is un necessarily developed.that is "A" column text from excel file. text should be extract from "B" column instead of "A" column from excel. text was developed as G1B1(9"x4½"). here G1 is not required. required format is B1(9"x4½").

please modify for small correction.

Thanking you for your contribution.

Hi friend your code is good working. but one column text from excel is un necessarily developed.that is "A" column text from excel file. text should be extract from "B" column instead of "A" column from excel. text was developed as G1B1(9"x4½"). here G1 is not required. required format is B1(9"x4½").

 

please modify for small correction.

 

Thanking you for your contribution.

Quick correction (untested) :

(defun C:test ( / acDoc SSX f opn row LstRows i TxtLst a b)

(setq acDoc (vla-get-ActiveDocument (vlax-get-acad-object)))
(vlax-map-collection (vla-get-Layers acDoc) (function (lambda (o) (vla-put-Lock o :vlax-false))))
(if 
	(and
		(setq SSX (ssget "_X" (list (cons 0 "TEXT") (if (= 1 (getvar 'cvport)) (cons 410 (getvar 'ctab)) '(410 . "Model")))))
		(setq f (getfiled "Select CSV File" "" "csv" 0))
	)
	(progn
		(vla-EndUndoMark acDoc)(vla-StartUndoMark acDoc)
		[color="red"](setq LstRows (mapcar 'cadr (LM:readcsv f)))[/color]
		[color="darkgreen"]; (setq opn (open f "r"))
		; (while (setq row (read-line opn)) (setq LstRows (cons row LstRows)))
		; (close opn)[/color]
		(repeat (setq i (sslength SSX)) (setq TxtLst (cons (entget (ssname SSX (setq i (1- i))))  TxtLst)) )
		(if (and LstRows TxtLst)
			(foreach a LstRows
				(foreach b TxtLst
					(and ; sloppy string reformatting below
						(wcmatch a (strcat "*" (cdr (assoc 1 b)) "*"))
						(setq a (vl-string-subst "(" "Reg" a))
						(setq a [color="red"](member "B"[/color] (vl-remove "\"" (vl-remove "," (mapcar 'chr (vl-string->list a))))[color="red"])[/color])
						(if (/= (last a) ")") (setq a (strcat (apply 'strcat a) ")")) (setq a (apply 'strcat a)))
						(setq a (vl-string-subst "\")" ")" (vl-string-subst "\"x" "x" a)))
						(entmod (setq b (subst (cons 1 a) (assoc 1 b) b)))
						(entupd (cdr (assoc -1 b)))
					)
				)
			)
		)
		(vla-EndUndoMark acDoc)
	)
)
(princ)
);| defun |; (vl-load-com) (princ)

[color="darkgreen"]
;; Read CSV  -  Lee Mac
;; Parses a CSV file into a matrix list of cell values.
;; csv - [str] filename of CSV file to read[/color]
[color="red"]
(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
)		
[/color]

All credits to Lee Mac.

Quick correction (untested) :

 

helo friend,

showing some errors. please test.

helo friend,

 

showing some errors. please test.

I can't without proper .csv file,

I've edited the last code I posted (forgot to include LM:csv->lst and LM:csv-replacequotes subfunctions) - try it again.

I can't without proper .csv file,

I've edited the last code I posted (forgot to include LM:csv->lst and LM:csv-replacequotes subfunctions) - try it again.

Dear friend,

Text was formed as shown in image.

here i have attached correct format of csv file. please test. your post#7 is better than post#10 please provide final version of code.

Thanking you.

Test file.csv

sample drawing2.dxf

You said that the text should be extract from column "B" instead of column "A", but your in last attached .csv file the text is located in column "A".

Based on your decision change this line of the code in post #10 :

(setq LstRows (mapcar 'cadr (LM:readcsv f)))

To:

If you want to extract from column "A" (this worked on your lastly uploaded files):

(setq LstRows (mapcar '[color="red"]car [/color](LM:readcsv f)))

If you want to extract from column "B":

(setq LstRows (mapcar '[color="red"]cadr [/color](LM:readcsv f)))

If you want to extract from column "C":

(setq LstRows (mapcar '[color="red"]caddr [/color](LM:readcsv f)))

And so on...

You said that the text should be extract from column "B" instead of column "A", but your in last attached .csv file the text is located in column "A".

oh sorry friend, that modification of csv file is unfortunatly happened. i apologies for my mistake. i will try your latest code and give feedback.

Thanking you for your kind support.

Dear friend,

i have tested with below code: for Extract from "B" and "D" Columns only. but error is not rectified as above shown figure:

(defun C:test ( / acDoc SSX f opn row LstRows i TxtLst a b)
   
   (setq acDoc (vla-get-ActiveDocument (vlax-get-acad-object)))
   (vlax-map-collection (vla-get-Layers acDoc) (function (lambda (o) (vla-put-Lock o :vlax-false))))
   (if 
       (and
           (setq SSX (ssget "_X" (list (cons 0 "TEXT") (if (= 1 (getvar 'cvport)) (cons 410 (getvar 'ctab)) '(410 . "Model")))))
           (setq f (getfiled "Select CSV File" "" "csv" 0))
       )
       (progn
           (vla-EndUndoMark acDoc)(vla-StartUndoMark acDoc)
           (setq LstRows (mapcar 'cadr (LM:readcsv f)))
           ; (setq opn (open f "r"))
           ; (while (setq row (read-line opn)) (setq LstRows (cons row LstRows)))
           ; (close opn)
           (repeat (setq i (sslength SSX)) (setq TxtLst (cons (entget (ssname SSX (setq i (1- i))))  TxtLst)) )
           (if (and LstRows TxtLst)
               (foreach a LstRows
                   (foreach b TxtLst
                       (and ; sloppy string reformatting below
                           (wcmatch a (strcat "*" (cdr (assoc 1 b)) "*"))
                           (setq a (vl-string-subst "(" "Reg" a))
                           (setq a (member "B" (vl-remove "\"" (vl-remove "," (mapcar 'chr (vl-string->list a))))))
                           (if (/= (last a) ")") (setq a (strcat (apply 'strcat a) ")")) (setq a (apply 'strcat a)))
                           (setq a (vl-string-subst "\")" ")" (vl-string-subst "\"x" "x" a)))
                           (entmod (setq b (subst (cons 1 a) (assoc 1 b) b)))
                           (entupd (cdr (assoc -1 b)))
                       )
                   )
               )
           )
           (vla-EndUndoMark acDoc)
       )
   )
   (princ)
);| defun |; (vl-load-com) (princ)


;; 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
)        

please test and correct my bad knowledge in lisp programing.

Thanking you.

sample drawing2.dxf

Test file.csv

Could you atleast upload a screenshot to see what looks like inside of your csv, instead of upploading it.

Like this (currently what I'm seeing):

So I we could have an idea what we're dealing with (as I feel that I totally misunderstood the task, because of the provided .csv file).

Obviously the OP wanted you to provide him with what he needs and not what he asked for. Two different things no?

Obviously the OP wanted you to provide him with what he needs and not what he asked for. Two different things no?

This looks like the reason why OPs are not answered that easily on their requests, because of the unclear questions/provided files.

Although his task is solvable, why need to spam 2-3 pages more only to understand what the real need is.

Here you can see a simple request, until we found that actually we have to deal with corrupt geometry instead of "rectangle" objects.

"I want small changes" is a different thing (which is not a problem IMO).

EDIT:

Anyway, to concatenate column "B" with column "D":

(if (setq LstRows (LM:readcsv f))
   (setq LstRows (apply 'append (mapcar '(lambda (x) (list (nth 1 x) (nth 3 x))) LstRows)))
)

But still won't solve the issue, because I have to remove the string re-formatting from the code - and to do so I firstly have to know what string to manipulate from the csv to be filled into the text entities.

my last post in this thread...

(defun c:csv2txt (/ file data ss in enx txt addtxt)
 (if
   (and
     (setq file (getfiled "Select CSV File" "" "csv" 16))
     (setq data (LM:readcsv file))
     (setq ss (ssget '((0 . "TEXT"))))
   )
   (repeat (setq in (sslength ss))
     (setq enx (entget (ssname ss (setq in (1- in)))))
     (setq txt (cdr (assoc 1 enx)))
     (foreach _X data
       (if (setq addtxt (caddr(member txt _X)))
         (progn
           (setq enx (subst (cons 1 (strcat txt "(" addtxt ")")) (assoc 1 enx) enx ))
           (entmod enx)
           (entupd (ssname ss in))
         )
       )
     )
   )
 )
 (princ)
)