Add text to drawing from excel file

my last post in this thread...

Well then theres no point to continue my efforts.

By the way can I ask you:

1. The csv in the gif looks different from that I've got, did you downloaded it like this?

2. What program did you use to create that gif ?

@Grrr:

When you open a .csv file in a spreadsheet program you should specify the correct field separator.

Note that CSV stands for Comma-Separated Values. But other field separators also occur. See here.

Well then theres no point to continue my efforts.

By the way can I ask you:

1. The csv in the gif looks different from that I've got, did you downloaded it like this?

2. What program did you use to create that gif ?

1) No, I made some changes, becouse, as Roy_043 wrote, I had to change field separator from comma to semicolon (which is default setting in my system).

2) ScreenToGif 2

Thanks guys! I'm kinda ashamed that it was my fault overall

Sorry structo, this should work now (alternatively to ziele_o2k's version) :

(defun C:test ( / SSX f acDoc opn row LstRows i TxtLst b )
(if 
	(and
		(setq SSX (ssget "_X" (list (cons 0 "TEXT") (if (= 1 (getvar 'cvport)) (cons 410 (getvar 'ctab)) '(410 . "Model")))))
		(or 
			(setq f (findfile "Test file.csv")) ; suit the filename and put the file within the trusted paths
			(setq f (getfiled "Select CSV File" "" "csv" 0))
		)
	)
	(progn
		(repeat (setq i (sslength SSX)) (setq TxtLst (cons (entget (ssname SSX (setq i (1- i)))) TxtLst)))
		(if (and (setq LstRows (LM:readcsv f)) TxtLst)
			(progn
				(setq acDoc (vla-get-ActiveDocument (vlax-get-acad-object)))
				(vla-EndUndoMark acDoc)(vla-StartUndoMark acDoc)
				(vlax-map-collection (vla-get-Layers acDoc) (function (lambda (o) (vla-put-Lock o :vlax-false))))
				(foreach a (mapcar 'list (mapcar 'cadr LstRows) (mapcar '(lambda (x) (strcat "(" x ")")) (mapcar 'cadddr LstRows))) ; column B and D
					(foreach b TxtLst
						(and 
							(= (car a) (cdr (assoc 1 b)))
							(entmod (setq b (subst (cons 1 (apply 'strcat 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
)

1st up well done guys, the method not looked at for this task was Getexcel.lsp this allows direct access to excel rather than a csv it uses row,column for cell addresses making I want D not E etc easy to change.

Grr just a suggestion bit easier if you want column G

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

(setq LstRows (nth x (LM:readcsv f))) ; remember x column is -1 as nth starts at 0

1st up well done guys, the method not looked at for this task was Getexcel.lsp this allows direct access to excel rather than a csv it uses row,column for cell addresses making I want D not E etc easy to change.

 

Grr just a suggestion bit easier if you want column G

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

(setq LstRows (nth x (LM:readcsv f))) ; remember x column is -1 as nth starts at 0

Thanks BIGAL,

I didn't knew about GetExcel.lsp, but IMO nothing can be more flexible than LM's matrix lists.

I also thought about writing a subfunction to get directly row or column:

; key - from "A" to "Z"
; f - filepath (return from functions like getfiled/findfile)
(defun GetColumn ( key f / Lst Column )
(setq Lst
	(mapcar 
		(function (lambda (x) (cons x (vl-position x Lst)))) 
		(setq Lst (mapcar 'chr (vl-string->list "ABCDEFGHIJKLMNOPQRSTUVWXYZ")))
	)
)
(setq Column (mapcar (function (lambda (x) (nth (cdr (assoc key Lst)) x))) (LM:readcsv f)))
)

_$ (setq ColumnD (GetColumn "D" (getfiled "Select CSV File" "" "csv" 0)))
("D1" "D2" "D3" "D4" "D5" "D6" "D7" "D8" "D9" "D10" "D11" "D12" "D13" "D14" "D15" "D16" "D17" "D18" "D19" "D20")
_$ 

Row is easy as you mentioned.

FWIW, here's another way to obtain the column values:

(defun column ( c l )
   (nth (1- (LM:col->num c)) (apply 'mapcar (cons 'list l)))
)

;; Column to Number  -  Lee Mac
;; Converts a column reference into an integer, e.g. AA -> 27
;; c - [str] upper-case string representing column reference

(defun LM:col->num ( c / n )
   (if (= 1 (setq n (strlen c)))
       (- (ascii c) 64)
       (+ (* 26 (LM:col->num (substr c 1 (1- n)))) (LM:col->num (substr c n)))
   )
)

For anyone interested

PROGRAM NAME: GETEXCEL.LSP [GETEXCEL R4]

; CREATED BY: TERRY MILLER (EMAIL: TERRYCADD@YAHOO.COM)

; (URL: http://HTTP://WEB2.AIRMAIL.NET/TERRYCAD)

; DATE CREATED: 9-20-03

; FUNCTION: SEVERAL FUNCTIONS TO GET AND PUT VALUES INTO EXCEL CELLS.

Hi friends,

thank you all for great contribution of this thread. every one well executed to solving of task. my final request is my actual data file is in .xlsm format. any tweak for directly extract from excel file to cad, instead of .csv format? please tweak.

Thank you guys.

Hi friends,

 

thank you all for great contribution of this thread. every one well executed to solving of task. my final request is my actual data file is in .xlsm format. any tweak for directly extract from excel file to cad, instead of .csv format? please tweak.

 

Thank you guys.

BIGAL wrote solution in post above your - GETEXCEL.LSP

BIGAL wrote solution in post above your - GETEXCEL.LSP

hi zie,

i saw your link. how to adopt or combine with grr's code?

Thank you.

Getexcel.lsp has a number of functions within it like get a single cell value or a range of cells from a spreadsheet. So rather than write a csv and read line by line and parse csv etc just read spreadsheet. It will mean replacing sections of Grr code.

GetExcel.lsp

Dear BIGAL friend,

please combine Grr code and yours "get excel" lisp which is mentioned in post#32. because i have no idea regarding lisp editing and modifications for combining two codes. please shape it as final code.

Thank you all.

structo your request would be better directed to Grr, he has indicated that he will look at getexcel for future use.

You must remember Cadtutor is a voluntary forum so most posts are done when workloads & private life permits. I am sure Grr will help it may just take a bit of time.

hi Grr,

kindly arrange final version for excel instead of CSV.

thank you BIGAL for kind guidance.

Thank you guys.

Just for anyone looking

; 2 lines of code
(LOAD "C:/Alans_stuff/LISPS/GetExcel.lsp")
(GetExcel "C:\\acadTemp\\Test.xlsx" "sheet1" nil)
; result is variable list *ExcelData@ 
(("GRP" "BLS numbers" "Type" "Size") ("" "" "" "(inch)") ("G1" "B1" "Reg" "9\"x4½\"") ("" "B2" "Reg" "12\"x12\"") ("" "B3" "Reg" "9\"x3\"") 
;using the list can do the text matching

Hi Bigal friend,

thank you for great execution. how to use above code or how to combine Grr's code and your code?

please post full lisp.

thanking you.

Here you go (it looks for .xlsm):

(defun C:test ( / SSX fpath acDoc opn row LstRows ExelRange i TxtLst b )

(setq 
	fpath (findfile "Test file.xlsm") ; suit the filename and put the file within the trusted paths
	ExelRange "D42" ; adjust the column-row range in the excel file (read up to that cell), for this example is "D42" 
); setq ;;; dont set a high value like "XYZ123", since it affects the performance speed
(if 
	(and
		GetExcel 
		(setq SSX (ssget "_X" (list (cons 0 "TEXT") (if (= 1 (getvar 'cvport)) (cons 410 (getvar 'ctab)) '(410 . "Model")))))
		(or fpath (setq fpath (getfiled "Select xlsm File" "" "xlsm" 0)))
	)
	(progn
		(repeat (setq i (sslength SSX)) (setq TxtLst (cons (entget (ssname SSX (setq i (1- i)))) TxtLst)))
		(if (and (setq LstRows (GetExcel fpath nil ExelRange)) TxtLst)
			(progn
				(setq acDoc (vla-get-ActiveDocument (vlax-get-acad-object)))
				(vla-EndUndoMark acDoc)(vla-StartUndoMark acDoc)
				(vlax-map-collection (vla-get-Layers acDoc) (function (lambda (o) (vla-put-Lock o :vlax-false))))
				(foreach a (mapcar 'list (mapcar 'cadr LstRows) (mapcar '(lambda (x) (strcat "(" x ")")) (mapcar 'cadddr LstRows))) ; column B and D
					(foreach b TxtLst
						(and 
							(= (car a) (cdr (assoc 1 b)))
							(entmod (setq b (subst (cons 1 (apply 'strcat a)) (assoc 1 b) b)))
							(entupd (cdr (assoc -1 b)))
						)
					)
				)
				(vla-EndUndoMark acDoc)
			)
		)
	)
	(cond
		((not GetExcel) (alert "\nGetExcel.lsp is not loaded, load it first and try the routine again!"))
		((not SSX) (alert "\nNo \"TEXT\" entities are found on the current tab."))
		((not fpath) (alert "\nThe \".xlsm\" file is not found nor specified."))
	)
)
(princ)
);| defun |; (vl-load-com) (princ)

And you still need to download and load GETEXCEL.LSP.

And you might want to adjust this part of the code for your needs (it currently suits the example):

(setq 
fpath (findfile "Test file.xlsm") ; suit the filename and put the file within the trusted paths
ExelRange "D42" ; adjust the column-row range in the excel file (read up to that cell), for this example is "D42" 
); setq ;;; dont set a high value like "XYZ123", since it affects the performance speed

hi Grr,

Thank you very much friend, now my problem is solved.

thank you all for great contribution.