export visibility and attribute to excel

[gbG5A5W]

I have multiple blocks (always) containing an attribute (called type) and (sometimes) a visibility (called kleur).

All blocknames start with ID-, EX-, EP- or EW-. I use these blocks to make an assembly. After assembling I need to export the number of blocks per block, the attribute and the visibility to excel. To accomplish that I use the data extraction tool:

It's a lot of clicks for everytime the same settings. Is it possible to do this faster?

[gbG5A5W]

The nested blocks (ID-100-010, ID-100-020 and ID-100-030) should indeed be included.

@daniel_cadext : COUNTLIST does more or less the same as DATA EXTRACTION, but I still would have to select the blocks manually. That gives room for errors, so for me it's not the way to go.

@komondormrex : it's missing the type (vis state) of the first 5 blocks and the type (attribute, invisible constant) of ID-300-100 perfohoek and ID-300-600 TV1000. And (indeed) blocks ID-100-010, ID-100-020 and ID-100-030 are missing, but also ID-200-570

[komondormrex]

this is all about nested blocks. i did not look thoroughly into the file, but i did now, and there are nested into nested into nested...

[komondormrex]

check this one with counting nested blocks. outputs to 'dwg_full_path.csv' thou.

(defun parse_host_block (insert / parsed_block_list)
	(defun parse_block (insert)
		(vlax-map-collection (vla-item (vla-get-blocks (vla-get-activedocument (vlax-get-acad-object)))
									   (vla-get-effectivename insert)
							 )
							 '(lambda (object) (if (= "AcDbBlockReference" (vla-get-objectname object))
													(if (minusp (vlax-get object 'visible))
														(progn
															(setq parsed_block_list (append parsed_block_list (list object)))
															(parse_block object)
														)
													)
											   )
							  )
		)
	)
	(setq parsed_block_list (list insert))
	(parse_block insert)
	parsed_block_list
)
(defun parse_list (_list / unique_list is_member)
	(foreach _member _list (if (not (member _member unique_list))
									(setq unique_list (append unique_list (list _member)))
						   )
	)
	(setq unique_list (mapcar '(lambda (_member) (cons _member 0)) unique_list))
	(foreach _member _list
		(if (setq is_member (assoc _member unique_list))
				(setq unique_list (subst (cons (car is_member) (1+ (cdr is_member))) is_member unique_list))
		)
	)
)
(defun get_att (insert tag_str / att_found)
  (if (vl-some '(lambda (att) (= tag_str (vla-get-tagstring (setq att_found att))))
	    	(append (vlax-invoke insert 'getattributes) (vlax-invoke insert 'getconstantattributes))
      )
      (list (cons tag_str (vla-get-textstring att_found)))
      (list (cons tag_str ""))
  )
)
(defun get_vs_state (insert vs_str / dyn_prop_found)
	(if (vl-some '(lambda (dyn_prop) (= vs_str (vla-get-propertyname (setq dyn_prop_found dyn_prop))))
		    	  (vlax-invoke insert 'getdynamicblockproperties)
	  	)
	  	(list (cons vs_str (vlax-get dyn_prop_found 'value)))
	  	(list (cons vs_str ""))
	)
)
(defun c:get_att_vs (/ insert_sset)
	(if (setq insert_sset (ssget '((0 . "insert"))))
		(progn
			(setq csv_file_id (open (setq csv_file_name (strcat (vla-get-path (vla-get-activedocument (vlax-get-acad-object))) "\\"
																(vl-filename-base (vla-get-name (vla-get-activedocument (vlax-get-acad-object)))) ".csv"
														)
									)
									"w"
							  )
			)
			(princ "Count,Block,Type,Color\n" csv_file_id)
			(foreach insert
				(parse_list (vl-sort (vl-remove nil
								     			(mapcar '(lambda (insert) (append (list (vla-get-effectivename insert)) (get_att insert "TYPE") (get_vs_state insert "kleur")))
									 					 (vl-remove-if-not '(lambda (insert) (wcmatch (vla-get-effectivename insert) "ID-*,EX-*,EP-*,EW-*"))
									 						  				(apply 'append
																					(mapcar 'parse_host_block
																							(mapcar 'vlax-ename->vla-object
																									(vl-remove-if 'listp (mapcar 'cadr (ssnamex insert_sset)))
																							)
																					)
																			)
									 					 )
								    			)
							 		 )
						    		'(lambda (insert_1 insert_2) (< (car insert_1) (car insert_2)))
							)
				)
			    (princ (strcat (itoa (cdr insert)) "," (caar insert) "," (cdr (cadr (car insert))) "," (cdr (caddr (car insert))) "\n") csv_file_id)
			)
			(close csv_file_id)
			(princ (strcat "\"" csv_file_name "\" has been written.\n"))
		)
	)
  	(princ)
)

[gbG5A5W]

Thnx! But please help me out on this. I learned that the lisp has te be saved with the first name after 'defun'. So, in this case I saved it as parse_host_block.lsp. It loads succesfully, after command GET_ATT_VS it creates the excel in csv. Am I missing something or is this the correct way?

[komondormrex]

you'd better save all this lisp code as the name of the command, ie 'get_att_vs.lsp'.

[gbG5A5W]

OK, did that. When using the lisp I'missing the type (vis state) of the blocks starting with EX. Also I see a block ID-100-091 that's not visible. But everything else is there, looks promising.

[komondormrex]

you have to stick to a sort of rules to make it go easier

[gbG5A5W]

Hmmm... that's strange:

I've attached the last version of the drawing, to make sure everything is up-to-date.

[komondormrex]

---

@gbG5A5W wrote:

Also I see a block ID-100-091 that's not visible.

---

it is.

[gbG5A5W]

Yse, it's there in a dyn block, but it's not the visible state. The visible state in the drawing is the 261, that is correctly exported.

[komondormrex]

---

@gbG5A5W wrote:

Hmmm... that's strange:

---

sorry, i have mistaken. the code gets attributes tagged 'TYPE' and visibility states named 'kleur'. others are ignored. i presume you want to expand the scale of getting data. is that it?

[gbG5A5W]

I don't think you have mistaken, it's my fault. I missed the visibility state 'type', that indeed should also be exported. If possible in the same column as the attribute 'type'.

[komondormrex]

check the updated code following. it will output visibility state of visibility parameter 'type' to the same 'Type' column for value of 'TYPE' tagged attribute .

(defun parse_host_block (insert / parsed_block_list)
	(defun parse_block (insert)
		(vlax-map-collection (vla-item (vla-get-blocks (vla-get-activedocument (vlax-get-acad-object)))
									   (vla-get-effectivename insert)
							 )
							 '(lambda (object) (if (= "AcDbBlockReference" (vla-get-objectname object))
													(if (minusp (vlax-get object 'visible))
														(progn
															(setq parsed_block_list (append parsed_block_list (list object)))
															(parse_block object)
														)
													)
											   )
							  )
		)
	)
	(setq parsed_block_list (list insert))
	(parse_block insert)
	parsed_block_list
)
(defun parse_list (_list / unique_list is_member)
	(foreach _member _list (if (not (member _member unique_list))
									(setq unique_list (append unique_list (list _member)))
						   )
	)
	(setq unique_list (mapcar '(lambda (_member) (cons _member 0)) unique_list))
	(foreach _member _list
		(if (setq is_member (assoc _member unique_list))
				(setq unique_list (subst (cons (car is_member) (1+ (cdr is_member))) is_member unique_list))
		)
	)
)
(defun get_att (insert tag_str / att_found)
  (if (vl-some '(lambda (att) (= (strcase tag_str) (strcase (vla-get-tagstring (setq att_found att)))))
	    	(append (vlax-invoke insert 'getattributes) (vlax-invoke insert 'getconstantattributes))
      )
      (list (cons tag_str (vla-get-textstring att_found)))
      (get_vs_state insert tag_str)  
      ;(list (cons tag_str ""))
  )
)
(defun get_vs_state (insert vs_str / dyn_prop_found)
	(if (vl-some '(lambda (dyn_prop) (= (strcase vs_str) (strcase (vla-get-propertyname (setq dyn_prop_found dyn_prop)))))
		    	  (vlax-invoke insert 'getdynamicblockproperties)
	  	)
	  	(list (cons vs_str (vlax-get dyn_prop_found 'value)))
	  	(list (cons vs_str ""))
	)
)
(defun c:get_att_vs (/ insert_sset)
	(if (setq insert_sset (ssget '((0 . "insert"))))
		(progn
			(setq csv_file_id (open (setq csv_file_name (strcat (vla-get-path (vla-get-activedocument (vlax-get-acad-object))) "\\"
																(vl-filename-base (vla-get-name (vla-get-activedocument (vlax-get-acad-object)))) ".csv"
														)
									)
									"w"
							  )
			)
			(princ "Count,Block,Type,Color\n" csv_file_id)
			(foreach insert
				(parse_list (vl-sort (vl-remove nil
								     			(mapcar '(lambda (insert) (append (list (vla-get-effectivename insert)) (get_att insert "TYPE") (get_vs_state insert "kleur")))
									 					 (vl-remove-if-not '(lambda (insert) (wcmatch (vla-get-effectivename insert) "ID-*,EX-*,EP-*,EW-*"))
									 						  				(apply 'append
																					(mapcar 'parse_host_block
																							(mapcar 'vlax-ename->vla-object
																									(vl-remove-if 'listp (mapcar 'cadr (ssnamex insert_sset)))
																							)
																					)
																			)
									 					 )
								    			)
							 		 )
						    		'(lambda (insert_1 insert_2) (< (car insert_1) (car insert_2)))
							)
				)
			    (princ (strcat (itoa (cdr insert)) "," (caar insert) "," (cdr (cadr (car insert))) "," (cdr (caddr (car insert))) "\n") csv_file_id)
			)
			(close csv_file_id)
			(princ (strcat "\"" csv_file_name "\" has been written.\n"))
		)
	)
  	(princ)
)

[Rick_Tolleshaug_TSC]

Here is update of ExportAV.lsp. Also attached is Excel template file ExportAV.xls.

IMPORTANT - the template file's location is set within ExportAV.lsp near the top.  You MUST open ExportAV.lsp in a text editor (Notepad) and edit the path to template's saved location.

UPDATE NOTES:

1.  Now gets TYPE data in this priority sequence:

        a. from Attribute (tag "TYPE"), if not present...

        b. from Visibility Parameter "Type", if not present...

        c. from Visibility Parameter "Visibility1"

2.  Now should count nested blocks accurately, including if nested 3 levels deep (this can be increased but the limit is set to avoid stack overflow due to unforeseen situations). You should know that block "systeemlijn" has (2) extra ID-100-255 blocks that are not visible in ANY visibility state. This will cause incorrect nested block count (too high). It will be important to avoid doing this for accurate counts.

3.  Now opens directly in Excel to tab "Xdwgpos".

4.  Now purges blocks at start of command, otherwise nested blocks in unreferenced block definitions could be counted

5.  Now file name for spreadsheet is <drawing name>.xls with (#) added if file name exists. 

Finally, given your latest example drawing, there are still two nested blocks with no "TYPE" indicator: ID-100-091 and ID-100-255 but I presume you know that.

Let me know how it goes.

[gbG5A5W]

In credible!! Big thanks! You are right that the two ID-100 don't have a type indicator. But that's fine tuning on my side, is not of any influence on the LISP, I think?

Final thing: there's a dyn block named systeemlijn, with 6 vis states (091 to 261, the extra two ID-100-255's I have deleted, error from my side). All these blocks 6x 2 pcs. are listed, where I would rather want only the ones in the active vis state to be listed. Would that conflict with the extraction of the nested blocks?

[gbG5A5W]

YES! Almost there. From the block systeemlijn the first vis style (ID-100-091 SM0910) is listed, not the active vis state. The result should be 5 times ID-100-261 SW2610, now it's 3 times ID-100-261 SW2610 and two times ID-100-091 SM0910.

[komondormrex]

who taught you to work that smart?)

[komondormrex]

---

@gbG5A5W wrote:

The result should be 5 times ID-100-261 SW2610, now it's 3 times ID-100-261 SW2610 and two times ID-100-091 SM0910.

---

that is because when block definition is checked against its composition the default first (actually you are right) visibility state is under inspection. and it is 091. for sure what you intend to get could be done with some over-complication of the code.

[gbG5A5W]

I can follow, but don't understand why with the ID-400-series of blocks it shows the right vis state of "kleur". Is that because these are not nested blocks?

[komondormrex]

absolutely! these series  hold no blocks!