Visual LISP, AutoLISP and General Customization

Visual LISP, AutoLISP and General Customization

Reply
Contributor
clareselley
Posts: 12
Registered: ‎07-17-2008
Message 1 of 16 (421 Views)

Importing data from a database/spreadsheet into polyline

421 Views, 15 Replies
06-29-2012 03:24 AM

Hi all. :smileyhappy:

 

Request for a script that may or may not be easy. :smileytongue:

 

I have a database with the polyline reference/handle (i.e. '13EE6A') and each polyline has a 'door number' associated with it.

 

I can easily create a query that produces a table of polyline ref and associated door number.

 

I need to somehow import that door number into the middle of each polyline on the drawing - i.e. if there is a polyline with that handle, put the associated door number in the middle of the polyline on a certain layer. I do NOT need an active link between the database and drawing - it's just to initially populate several thousand rooms numbers!

 

This would make my life SO much easier - so, is there a way?

 

I can also do this from Excel if it's easier!

Valued Contributor
aabfm
Posts: 74
Registered: ‎01-05-2012
Message 2 of 16 (414 Views)

Re: Importing data from a database/spreadsheet into polyline

06-29-2012 03:56 AM in reply to: clareselley

What do mean by "put the associated door number in the middle of the polyline on a certain layer"?

Contributor
clareselley
Posts: 12
Registered: ‎07-17-2008
Message 3 of 16 (410 Views)

Re: Importing data from a database/spreadsheet into polyline

06-29-2012 04:25 AM in reply to: aabfm

Extract from spreadsheet/database example:

 

 handleroom_num_door

3D306217
3D2CE216
3D302215

 

 

So, I need a script that'll put '217' in the middle of the (closed) polyline with entity handle 3D306. Preferably on a layer called 'Door Number'. And also put all the other door numbers in their associated polylines.

 

It can be the exact middle of the x-y coordinates - I don't mind moving the numbers around to reposition them, that's far easier than updating 32000 door numbers manually!

 

Valued Contributor
aabfm
Posts: 74
Registered: ‎01-05-2012
Message 4 of 16 (404 Views)

Re: Importing data from a database/spreadsheet into polyline

06-29-2012 04:53 AM in reply to: clareselley

It seems that you have a similar issue as I use to have, and I was lucky enough to have Lee_Mac on the other side of the line to help me.

 

Why not use a script that allows you to draw a pline and afterwords inserts a block (in this case with 5 attributes, I think) where you want and later on a simple call-line to let you know which pline this block relates to.

Also, one of the attributes, the last one, is already populated with the pline object handle.

So once you have all of your plines just export the blocks through express tools, use Excel to import this info and associate there your door numbers with the pline, i.e. assign to the corresponding block to the pline object handle.

Export this info from Excel back to the file that was exported early before from ACad and import it again to ACad through express tools.

Now you'll have the blocks of each pline with the corresponding room number on them.

 

Here is the code:

 

(defun c:q8a  (/ objname p blk trc lcp1 p or_osmode)
    (vl-load-com)
	(setq or_osmode (getvar "osmode"))
    (if (and(progn (command "_.PLINE")
         	(while (= (getvar "CMDNAMES") "PLINE")
			(command pause)) T
			)
			(setq objname (vlax-ename->vla-object (entlast)))
			
			(setvar "osmode" 2)
						
			(setq lcp1 (getpoint "\nSpecify CallLine StartPoint: "))
			
			(setvar "osmode" 0)
			
			(setq p (getpoint "\nSpecify Insertion Point: "))

			(setq trc (getstring "\nTRC: "))
		)
		
		(progn
			(setq blk
				(vlax-invoke
					(vlax-get
						(vla-get-ActiveLayout (vla-get-activedocument  (vlax-get-acad-object)))
					'Block)
					'InsertBlock
					(trans p 1 0)
					"TROC_PRP"
					1 1 1 
					(progn
						(setq rot 
							(cond
								((getangle p
									(strcat "\nEnter Rotation <"
										(angtos
											(setq rot (cond ( rot )( 0 )))
										) ">: "
									)
								))
								(rot)
							)
						)
						(+ rot (- 6.28318531 (getvar 'Viewtwist)))
					)
				)
			)
			(mapcar '(lambda (l1 l2)
			(vla-put-textstring  l2 l1))
                      (list trc (vla-get-handle objname))
                      (vlax-invoke blk 'GetAttributes))
					  
			(command "line" lcp1 p "")
		)
    )
	(setvar "osmode" or_osmode)
	(princ)
)

 NOTE: In this case the block used is called TROC_PRP.

Valued Contributor
aabfm
Posts: 74
Registered: ‎01-05-2012
Message 5 of 16 (401 Views)

Re: Importing data from a database/spreadsheet into polyline

06-29-2012 04:59 AM in reply to: aabfm

By the way, you may notice a rot command somewhere in the code, this was due to my UCS which was rotated.

Also it doesn't matter the name you give to the block attributes since the script looks to the order they come and not to the names.

Contributor
clareselley
Posts: 12
Registered: ‎07-17-2008
Message 6 of 16 (398 Views)

Re: Importing data from a database/spreadsheet into polyline

06-29-2012 05:07 AM in reply to: aabfm

Hi

Thanks for this!

The main issue is I already have all the polylines around the 32000 rooms drawn - so drawing more lines would take far longer than it would to manually check/edit the door numbers anyway.

 

Bear in mind I'm not that familiar with blocks and attributes - I was hoping for the door number to just be put in as MTEXT at the middle of the polyline (i.e. the midpoint of the X and Y coordinates)

 

Is there no way to import from a list (can be comma deliniated or whatever format, or from SQL) where for each polyline it runs through the list until it finds the handle for the polyline and imports the appropriate door number in the middle of it?

 

Or a way to export the handle & midpoint co-ordinates of each polyline to Excel and then import the door number as MTEXT at that co-ordinate?

 

Thanks,

 

Clare.

Contributor
clareselley
Posts: 12
Registered: ‎07-17-2008
Message 7 of 16 (396 Views)

Re: Importing data from a database/spreadsheet into polyline

06-29-2012 05:19 AM in reply to: clareselley

Except, of course, the polyline handle is only unique within the drawing and 1 handle can refer to several polylines within the database - I can't assume that the script would pick up the correct door number. Argh.

 

So I need a script that asks what spreadsheet I want to import from - so I can have 1spreadsheet per building floor.

 

Still quicker than manually doing it...

 

Clare.

Valued Contributor
aabfm
Posts: 74
Registered: ‎01-05-2012
Message 8 of 16 (393 Views)

Re: Importing data from a database/spreadsheet into polyline

06-29-2012 05:31 AM in reply to: clareselley

Hummm.... 

What about:

- export all the plines with the code from Lee_Mac in my post "Export list of objects", this also gets the Object Handle;

- import them in Excel;

- calculate in Excel their mid point or other you want to;

- create simple block like a rectangle with a single attribute inside for the door number and 2 other with its coordinates;;

- export the list of these excel calculated coordinates to use with the script I send in attach which will put the block you created before on each coordinate you gave it;

- select all the blocks previously created;

- export through Tools - DataExtraction the blocks as well as the coordinates of them;

- import this file into Excel;

- now you have the block handle (to recover the info back to ACad) and the coordinates to associate with pline.

 

 

Valued Contributor
aabfm
Posts: 74
Registered: ‎01-05-2012
Message 9 of 16 (391 Views)

Re: Importing data from a database/spreadsheet into polyline

06-29-2012 05:32 AM in reply to: aabfm

I forgot the attach... Ooops...

Contributor
clareselley
Posts: 12
Registered: ‎07-17-2008
Message 10 of 16 (388 Views)

Re: Importing data from a database/spreadsheet into polyline

06-29-2012 05:58 AM in reply to: aabfm

Sadly you've lost me at 'block' :smileysad:- I'm not very good with blocks and attributes and prefer not to use them - these are only simple drawings!

 

Is there no way to set a script up that will:

 

Get handle of pline

Run through a spreadsheet and find handle in one column

Find associated text in next column

Place text at center point in pline

 

Or a way to export the handle and center point of a polyline (I tried that script - it didn't export the handle?) and then I can match up room numbers. Then I'd just need a script that will put the mtext at the coordinate for the center point.

Announcements
Are you familiar with the Autodesk Expert Elites? The Expert Elite program is made up of customers that help other customers by sharing knowledge and exemplifying an engaging style of collaboration. To learn more, please visit our Expert Elite website.
Need installation help?

Start with some of our most frequented solutions or visit the Installation and Licensing Forum to get help installing your software.