Fill in atrributes from Excel file or CSV file

[robert.klempau]

Hello,

I am looking for a lisp routine with which I can fill attributes via an Excel file.
My project has lots of blocks.
Whit a routine from @Moshe-A I added several attributes to all these blocks.
Now I need to fill in the Values.
Because it is difficult to fill these in one by one in AutoCAD, I am looking for the possibility to do this via Excel.
The picture below shows an example of the excel file.

The attributes in my block are invisible.

hopefully, somebody can help me.
Unfortunately, I am not a LISP expert.

Thanks in advance,

Kind Regards,

Robert

[O_Eckmann]

Hi,

Why don't you use ATTOUT to export all attributs to TXT file, drag&drop this TXT file to Excel, fills it in Excel, save back to TXT and ATTIN to "synchronise" values to ATTRIBUTS. Express tools must be installed for using ATTOUT/ATTIN

Olivier

[philliprusso]

A similar script exists to update atrributes in a drawing from csv that contains source code that might be useable or modifiable.

https://www.cadalyst.com/cad/autocad/update-your-attribute-blocks-automatically-5051

[pbejse]

---

@robert.klempau wrote:

The picture below shows an example of the excel file.

The attributes in my block are invisible.

hopefully, somebody can help me.

Robert

---

Use the native DATAEXTRACTION command.

Exactly what you're looking for.

HTH

[robert.klempau]

I want to add some more information to this request:

I have placed multiple instances of different blocks in my drawing.

For example:

10 times a block with block name 'block_1' en 5 times a block with block name 'block_2'

All blocks have attributes.

TAG1 , TAG2, TAG3  en TAG4

Now I want to read the information from my Excel file.

read the block name in the first column from the Excel file, read the TAG values from the other colums, and then find all the block of the same name in my drawing and fille in the Attribute values.

So all the blocks instances with the same name have the same attribute values.

above is just a example.

In my drawing, I have a few hundred different block name withs have all the same attributes. 

Hope someone can help.

[O_Eckmann]

Hi,

here is a screencast for my solution with ATTIN/ATTOUT without any development.

Olivier

[Sea-Haven]

This code expects a couple of things, that all blocks have been created with attributes in tag order ie tag1 tag2 etc it does not effect the display it will still be correct, The code ignores tag names using as mentioned the creation order.

This reduces the code required significantly. I am looking at a more global version that would handle various number of atts for a block with different tag names.

There is no dwg or csv to test but worked on my dummy test dwg.

; update all blocks from csv BLKname,tag1,tag2,tag3,tag4
; hard coded for 4 atts
; expects that tags have been created in order

; thanks to Lee-mac for this defun 
; www.lee-mac.com
; 44 is comma

(defun _csv->lst ( str / pos )
	(if (setq pos (vl-string-position 44 str))
		(cons (substr str 1 pos) (_csv->lst (substr str (+ pos 2))))
		(list str)
    )
)

; program starts here hardcoded for testing
; read 1st line dummy for heading
(defun c:4atts ( / fname newline newlst ss x y att)

(setq fname (open "d:\\acadtemp\\test.csv" "r"))
(setq newline (read-line fname))

(while (setq newline (read-line fname))
	(setq newlst ( _csv->lst newline))
	(setq ss (ssget "X" (LIST (CONS 0 "INSERT")(CONS 2 (nth 0 newlst)))))
	(repeat (setq x (sslength ss))
	(setq y 0)
	(foreach att (vlax-invoke (vlax-ename->vla-object (ssname SS (setq x (- x 1)) )) 'getattributes)
	(vla-put-textstring att (nth (setq y (+ y 1)) newlst))
	)
    )
)
(close fname)
(princ)
)
(c:4atts)

[pbejse]

---

@robert.klempau wrote:

Now I want to read the information from my Excel file.

So all the blocks instances with the same name have the same attribute values.

...

---

Lets be clear, you are only wanting to read and assign attribute values to an exsiting blocks from an external file (xls/csv)? or are you also asking for a code to read the blocks ONLY with invisible attributes to and export the values to a external file.? or already have a code for the latter?

[Sea-Haven]

Like you PBE he asked "possibility to do this via Excel" and gave the easy answer make a csv file as the lisp exists in other code.

Can it be done from excel as a macro more than likely but it  means taking what has been done and converting to VBA syntax

(setq ss (ssget "X" (LIST (CONS 0 "INSERT")(CONS 2 (nth 0 newlst)))))

FilterDXFCode(0) = 0
FilterDXFVal(0) = "INSERT"
'FilterDXFCode(1) = 2
'FilterDXFVal(1) = blkname
Set SS = ThisDrawing.SelectionSets.Add("ss")
SS.Select acSelectionSetAll, , , FilterDXFCode, FilterDXFVal

[robert.klempau]

Hello @pbejse,

I only want to update all my 800 block instances placed in my drawing with attribute values that are placed in my Excel file. 

for all the same blocks in my drawing I have only one row of attribute information in my Excel file.

So X rows for each blockname in my drawing.

so the routine I am looking for needs to read the header row from my Excel file for the blockname and TAG names and then read row by row the block name en Tag values and then per row search all the blocks with the same name in my drawing and fill in the attribute values.

Kind regards,

Robert

[pbejse · Accepted solution]

This code will read the HEADER to identify the TAG names.

Block names from the collected CSV data  will be included on the selection filter.

Each selected block will be checked for matching Block name AND Tag names

(defun c:ReadAndUpdate  ( / _DelTolst AddComma data allData block csv opf
			 i e en f TagAndvalue NameAndTag processedMeat )
;;;		pBe Oct 2020			;;;
(defun _DelTolst ( str m / pos x lst lst2)
	(if (setq pos (vl-string-position m str))
	   (cons (substr str 1 pos) (_DelTolst (substr str (+ pos 2)) m ))
	   (list str)
	   )
)
(setq AddComma (lambda (v) (strcat "," v)))  
(if
  (and
    	(setq allData nil
	       csv (getfiled "Select data source" (getvar 'dwgprefix) "csv" 16))
	(progn
		(setq processedMeat 0 opf (open csv "r"))
		(setq NameAndTag (_DelTolst (read-line opf) 44))
		(while (setq data (read-line opf))
		(setq allData (cons (_DelTolst data 44) allData))
		)
		(close opf)
		(setq allData (mapcar '(lambda (v)
					     (cons (strcase (Car v)) (cdr v))) alldata))
		  )
		(setq blocks (ssget "X" (append '((0 . "INSERT")(66 . 1))
		       		(list (cons 2 (apply 'strcat
						     (cons "`*U*" (mapcar 'AddComma (mapcar 'car allData)))
					 ))))))
	)
    (repeat (setq i (sslength blocks))
      	(setq e (vlax-ename->vla-object (ssname blocks (setq i (1- i))))
              en (strcase (vla-get-effectivename e)))
      	(if (and (setq f (assoc en allData))
	         (setq TagAndvalue  (mapcar 'list (cdr NameAndTag)(cdr f))))
		  	(progn
			  (mapcar '(lambda (at)
			 		(if (setq mtag (assoc (Vla-get-tagstring at) TagAndvalue))
					  	(Vla-put-textstring at (cadr mtag))))
				  (vlax-invoke e 'GetAttributes)
				)
			  (setq processedMeat (1+ processedMeat))
			)
		)
      	)
     )

  (if (> processedMeat  0)
    	(princ (strcat "\n" (itoa processedMeat) " Blocks updated")))
    (princ)
    )

HTH

[robert.klempau]

Hello @pbejse,

Thanks for your routine.

I was wondering what I am doing wrong.

Nothing happens in my sample file when I run the lisp routine.

can you check my sample files before I run it on my project files?

Thanks,

Robert

[pbejse · Accepted solution]

---

@robert.klempau wrote:

Thanks for your routine.

I was wondering what I am doing wrong.

---

Nothing wrong, its just different, the code targets the character comma, change all ";" to "," then you're good to go

,TAG1,TAG2,TAG3,TAG4
block1,375,249,123,444
block2,499,375,321,555

Side note: I will put something in there just because its better to look at. it will  work with or without nonetheless.

Blockname,TAG1,TAG2,TAG3,TAG4

Either you make the change on the csv

or

Leave the csv as it is (as ";") but change the part of the code where you see 44 and change it to 59.

BUT if you ask me, i'd rather you change the csv format to ","

Command: READANDUPDATE

18 Blocks updated

HTH

[Sea-Haven]

Apart from the "," v's ";" problem,  the code I posted did what was required using a block attribute order method, tested on dwg provided.

There is a couple of excel where are you in the world problems that catch you out. Decimal point comes to mind.

[pbejse]

---

@Sea-Haven wrote:

.. did what was required using a block attribute order method,

---

Was that a requirement from the OP? Not sure that it is, otherwise why would the csv even include the tag names as headers?

Anyhoo, I wouldnt know either, i waited a good half an hour after i posted my reply, after that the OP did a Homer disappearing in the bush act 😁

---

@Sea-Haven wrote:

There is a couple of excel where are you in the world problems that catch you out. Decimal point comes to mind.

---

True that.

[Sea-Haven]

A more global approach is doing block name then attributes in order, this allows a block to have any number of attributes and still work, like Block1 has 3 but block4 has 5. If no attribute cell in excel is left blank and csv becomes Blockname,att1,,att3,att4 etc.

In other code I have a pad defun this fixes the problem when inserting to fill in blank for missing attribute values.

The task is a bit strange, could edit 1, then copy erase others and replace at same location with filled in block.

[robert.klempau]

Hello @pbejse ,

With your last tip, it works just fine. 😀

Thanks men, this will help me a lot.

You are the best.

Kind regards,

Robert