Lisp to update blocks from excel file Acad 2017

[Anonymous]

Good Morning!
I am looking for a lisp routine that will update the loading tree diagrams on my Transmission line drawings.  The loading trees are attributed and I have and excel file containing the information for the loading trees
The lisp should populate the blocks when the lisp is loaded. I have upload a typical drawing scenario and excel file.  I would appreciate you help.

[SeeMSixty7]

Good morning. Looking at your drawing file, I have to ask will you be inserting that as a block into another drawing, or is your intent to use the attribute definitions as text place holders in your drawing?

I would recommend creating a single block from one of the trees then using the tree number as the identifier, then you could populate the tree data from that id using the data in your excel file.

Try and attach your excel file again, since it did not come through.

Good luck,

[TheCADnoob]

check out this link and see if it helps

http://forums.augi.com/showthread.php?149778-Lisp-to-read-an-excel-and-update-attributes-in-title-bl...

also there is a forum here dedicated to lisps so i would check it out as well

https://forums.autodesk.com/t5/visual-lisp-autolisp-and-general/bd-p/130

[Anonymous]

I have been inserting this drawing into a new drawing as a block, but I can make a block "loading tree".  The excel file has been uploaded.

Thanks!

[Anonymous]

I will check out this site.  Thanks!

[SeeMSixty7]

Looking at your excel file, It is not exactlky clear what data goes where. There is also data missing that comes from a linked file. I would recommend uploading an excel file that has the data set complete and avoid the linking of external files for this effort.

Looking at LOAD CASE No. 1

There are multiple LC1VC attribute definitions, is the intent for all these to be the same or have different values based on a calculation or something else possibly.

In your excel file which values go to what?

What column and Row provides the data for LC1VGW?

What column and Row provides the data for LC1TGW?

What column and Row provides the data for LC1LGW?

What column and Row provides the data for LC1TC?

What identifies CASE 1

If you can upload just the data for a single load case and a way to identify each load case, it may be easier to decipher.

Thank you and good luck,

[Anonymous]

Friend,

I have updated my drawing to contain a block "LoadTree.  I have a new excel file to correlate with the block "LoadTree".

[SeeMSixty7]

I updated your Excel file format to use rows instead of columns. I also saved it as a CSV file. You can do the same from Excel using save as.

I updated your block to remove the 1's form the attribute tags. Normally I would not recommend using the same tagname for multiple attributes, but it works ok.

I also added a description attribute to replace the "LOAD CASE NO. #" static text. I also added a column for that into the excel(csv) file. The column names in the CSV file are used to identify which attribute to fill in with the values.

Attached are all the files. Try it out and let me know what you think. I did update your block, so you will need to use this drawing file because it has the proper block definition in it.

Also If you are interested in learning how this is done, let me know and I will add comments to the code.

Oh and Name of command is LOADTREE. Copy and paste from below or drag and drop the loadtree.lsp file into your drawing area to test.

Good Luck,

;;;By: Clint Moore
;;;Date: 05/10/2017
;;;
(defun getLoadTreeData (loadtreefilename / gltd-LoadTreeFileHandle gltd-ReturnLoadTreeData lt-linedata lt-listdata)
	(setq gltd-LoadTreeFileHandle (open loadtreefilename "r")
		  gltd-ReturnLoadTreeData (list)
	)
	(while (setq lt-linedata (read-line gltd-LoadTreeFileHandle))
		(setq lt-listdata (SimpleCommaParse lt-linedata)
			gltd-ReturnLoadTreeData (append gltd-ReturnLoadTreeData (list lt-listdata))
		)
	)
	(close gltd-LoadTreeFileHandle)
	gltd-ReturnLoadTreeData
)
(defun SimpleCommaParse (StringToParse / scp-parsedlist sc-delpos)
	(setq scp-parsedlist (list))
	(while (setq scp-delpos (vl-string-position 44 StringToParse))
		(setq scp-parsedlist (append scp-parsedlist (list (substr StringToParse 1 scp-delpos)))
			  StringToParse (substr StringToParse (+ scp-delpos 2))
		)
	)
	(setq scp-parsedlist (append scp-parsedlist (list StringToParse))) 
	scp-parsedlist
)
(defun FillAttributeTag (BlockEntity AttTagName AttValue / fat-ent fat-data fat-object)
	(setq fat-ent BlockEntity
		  fat-data (entget fat-ent)
		  AttTagName (strcase AttTagName)
	)
	(if (cdr (assoc 66 fat-data))
		(progn
			(while (not (equal (cdr (assoc 0 fat-data)) "SEQEND"))
				(setq fat-ent (entnext fat-ent)
					  fat-data (entget fat-ent)
				)
				(if (equal (cdr (assoc 2 fat-data)) AttTagName)
					(progn
						(setq fat-attobject (vlax-ename->vla-object fat-ent))
						(vla-put-TextString fat-attobject AttValue)
					)
				)
			)
		)
	)
)
(defun PlaceLoadTreeData (LoadTreeDataList / pltd-att_tags pltd-loadtrees pltd-insertpoint pltd-DistanceApart pltd-ent pltd-index)
	(setq pltd-att_tags (car LoadTreeDataList)
		  pltd-loadtrees (cdr LoadTreeDataList)
		  pltd-insertpoint (list 0.0 0.0 0.0)
		  pltd-DistanceApart 2.334
	)
	(foreach lt pltd-loadtrees
		(setq pltd-currattreq (getvar "ATTREQ"))
		(setvar "ATTREQ" 0)
		(command "-insert" "loadtree" pltd-insertpoint 1.0 0.0)
		(setvar "ATTREQ" pltd-currattreq)
		(setq pltd-insertpoint (polar pltd-insertpoint 0.0 pltd-DistanceApart)
			  pltd-ent (entlast)
			  pltd-index 0
		)
		(foreach att-tag pltd-att_tags
			(FillAttributeTag pltd-ent att-tag (nth pltd-index lt))
			(setq pltd-index (1+ pltd-index))
		)
	)
)
(defun c:LOADTREE( / excelfilename loadtreedata)
	(setq excelfilename (getfiled "Select Loadtree CSV File: " "" "csv" 0))
	(if excelfilename
		(progn
			(setq loadtreedata (getLoadTreeData excelfilename))
			(if loadtreedata
				(placeloadtreedata loadtreedata)
			)
		)
	)
	(princ)
)

[Anonymous]

You have set up the CSV file perfectly.  The lisp updates the blocks based on the block description?   I ran the routine on the sample drawing with some new numbers and have upload a revised CSV file. It didn't update the numbers. Is there a problem with my CSV file?

Thanks!

[SeeMSixty7]

I ran it with your csv file and it ran fine. For some reason When I downloaded it though it had a xls extension rather than a csv extension.

Here are the results and the renamed csv file.

Make sure yours has the correct extension, otherwise the xls file will not show up in the file open dialog box.

EDIT - Looks like The Autodesk Forum site saves the csv file to an xls file when you download it from here. I'm not sure why it does that.

Also Make sure you run it form the drawing I gave you, or try the one attached, by erasing everything and running LOADTREE.

Let me know and good luck,

[Anonymous]

Bingo, works like a charm.  I notice that if I use a different CSV file it keeps the original numbers in the tree.   When I erased everything it redrew the trees with the new numbers.  How does it know the loadtree blocks are supposed redraw themselves? 

[SeeMSixty7]

The routine is set to create the trees based on the excel file. It is not setup to locate and update the trees already in a file. Is that functionality that you need? Takes a bit more effort to then locate and update it, but doable.

Clint

[Anonymous]

Clint,

The file works fine the way it is.  I was trying to understand how I erased everything in the drawing, saved it, ran the lisp routine, and the loadtrees appeared in the drawing with the numbers updated.  Amazing!  If I need to add more Load Cases, I should copy a block any rename the description to the next Load Case No.?

Colette

[SeeMSixty7 · Accepted solution]

The routine will create as many new load trees as found in the csv file. So if you have 30 of them it will create them all for you. It will always start at 0,0,0 to create them. If you just want to add some, just add them to the csv or create a new csv with just the new ones. Then you would have to move them to where you want them to go.

You can basically configure the routine to start anywhere you want though. In the lisp routine there is a place for the starting insertion point.

Clint

[vanconghoaviet]

Help: My name is Cong from Vietnam
Please help me fix this lisp to fit my block. Thank you
The attached Czech I sent below.

[dbhunia]

If the Red Boxes are your required conditions.......

Then try the modified Lisp.......

;;;By: Clint Moore
;;;Date: 05/10/2017
;;;
(defun getLoadTreeData (loadtreefilename / gltd-LoadTreeFileHandle gltd-ReturnLoadTreeData lt-linedata lt-listdata)
	(setq gltd-LoadTreeFileHandle (open loadtreefilename "r")
		  gltd-ReturnLoadTreeData (list)
	)
	(while (setq lt-linedata (read-line gltd-LoadTreeFileHandle))
		(setq lt-listdata (SimpleCommaParse lt-linedata)
			gltd-ReturnLoadTreeData (append gltd-ReturnLoadTreeData (list lt-listdata))
		)
	)
	(close gltd-LoadTreeFileHandle)
	gltd-ReturnLoadTreeData
)
(defun SimpleCommaParse (StringToParse / scp-parsedlist sc-delpos)
	(setq scp-parsedlist (list))
	(while (setq scp-delpos (vl-string-position 44 StringToParse))
		(setq scp-parsedlist (append scp-parsedlist (list (substr StringToParse 1 scp-delpos)))
			  StringToParse (substr StringToParse (+ scp-delpos 2))
		)
	)
	(setq scp-parsedlist (append scp-parsedlist (list StringToParse))) 
	scp-parsedlist
)
(defun FillAttributeTag (BlockEntity AttTagName AttValue / fat-ent fat-data fat-object)
	(setq fat-ent BlockEntity
		  fat-data (entget fat-ent)
		  AttTagName (strcase AttTagName)
	)
	(if (cdr (assoc 66 fat-data))
		(progn
			(while (not (equal (cdr (assoc 0 fat-data)) "SEQEND"))
				(setq fat-ent (entnext fat-ent)
					  fat-data (entget fat-ent)
				)
				(if (equal (cdr (assoc 2 fat-data)) AttTagName)
					(progn
						(setq fat-attobject (vlax-ename->vla-object fat-ent))
						(vla-put-TextString fat-attobject AttValue)
					)
				)
			)
		)
	)
)
(defun PlaceLoadTreeData (LoadTreeDataList / pltd-att_tags pltd-loadtrees pltd-insertpoint pltd-DistanceApart pltd-ent pltd-index)
	(setq pltd-att_tags (car LoadTreeDataList)
		  pltd-loadtrees (cdr LoadTreeDataList)
		  pltd-insertpoint (list 1277.8977 0.0 0.0)
		  pltd-DistanceApart 2555.7955
	)
	(foreach lt pltd-loadtrees
		(setq pltd-currattreq (getvar "ATTREQ"))
		(setvar "ATTREQ" 0)
		(command "-insert" "SDNLG" "_none" pltd-insertpoint 25.4 25.4 0.0)
		(vla-put-layer (vlax-ename->vla-object (entlast)) "AC-ROOM")
		(setvar "ATTREQ" pltd-currattreq)
		(setq pltd-insertpoint (polar pltd-insertpoint 0.0 pltd-DistanceApart)
			  pltd-ent (entlast)
			  pltd-index 0
		)
		(foreach att-tag pltd-att_tags
			(FillAttributeTag pltd-ent att-tag (nth pltd-index lt))
			(setq pltd-index (1+ pltd-index))
		)
	)
)
(defun c:LOADTREE( / excelfilename loadtreedata)
	(setq excelfilename (getfiled "Select Loadtree CSV File: " "" "csv" 0))
	(if excelfilename
		(progn
			(setq loadtreedata (getLoadTreeData excelfilename))
			(if loadtreedata
				(placeloadtreedata loadtreedata)
			)
		)
	)
	(princ)
)

[vanconghoaviet]

Thank you very much!
I will try this autolisp.

[vanconghoaviet]

I used the autolisp you sent. I feel wonderful
Thank you!
I have 2 more problems while using:
1- Type the command "loadtree" - Select file "CSV" - Choose the set point for the block (currently in position 0.0 0.0).
2-I am having Vietnamese problems in Font name "Unicode" (Font name "VNI-" is okay).
Thanks for the enthusiastic help!