Visual LISP, AutoLISP and General Customization
cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 

Importing data from a database/spreadsheet into polyline

15 REPLIES 15
Reply
Message 1 of 16
clareselley
755 Views, 15 Replies

Importing data from a database/spreadsheet into polyline

Hi all. 🙂

 

Request for a script that may or may not be easy. 😛

 

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!

15 REPLIES 15
Message 2 of 16
aabfm
in reply to: clareselley

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

Message 3 of 16
clareselley
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!

 

Message 4 of 16
aabfm
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.

Message 5 of 16
aabfm
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.

Message 6 of 16
clareselley
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.

Message 7 of 16
clareselley
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.

Message 8 of 16
aabfm
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.

 

 

Message 9 of 16
aabfm
in reply to: aabfm

I forgot the attach... Ooops...

Message 10 of 16
clareselley
in reply to: aabfm

Sadly you've lost me at 'block' Smiley Sad- 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.

Message 11 of 16
aabfm
in reply to: clareselley

Create your block is quite easy.

 

1. Create 1 attribute with ATTDEF giving tag and prompt the same value, 'door_number' for instance.

2. Just for decoration design with REC a rectangle around the atribute.

3. Select both the attribute and the rectangle.

4. Create the block with the BLOCK command.

5. Save the block with WRITE command.

 

Just to test the block use INSERT and select the block you've just created.

Message 12 of 16
johncavogt
in reply to: clareselley

Do you still need this as you wished for it? Might be kind of fun but I don't want to do it if you don't need it.

Message 13 of 16

Hi

 

Yes, I'd like it as I wished it if possible!

 

Clare

Message 14 of 16

I think the simple way for it to work is:

 

Get handle and centroid co-ordinates of the polyline.

I can then put this in the spreadsheet

Then a routine to input text 'X' at the X.Y centroid co-ordinates

 

This looks 'simple' from stuff online - extracting centroids/handles, etc, but I don't know LISP, so I can't write it!

 

The looking up live in the spreadsheet might be a bit complicated!

 

 

 

Message 15 of 16

http://forums.autodesk.com/t5/Visual-LISP-AutoLISP-and-General/Routine-to-Import-Points/m-p/1871097/...

 

The 2nd post here will do the 2nd part...

 

So all I need now is a script to extract the handle of a polyline (NOT the region) and the centroid to Excel. Then I can match polyline handle to the one in the database and therefore the centroid co-ordinates... And then run the above script to import the door numbers at the correct co-ordinates. 🙂

Message 16 of 16

Someone has now solved it the export then import way for me. 🙂

 

Thanks all!

 

Clare.

Can't find what you're looking for? Ask the community or share your knowledge.

Post to forums  

Autodesk Design & Make Report

”Boost