Populating dynamic blocks with data from Excel

I am looking for any help whatsoever in workflow issue that I have been presented with.

Put simply ( ! ) I have an excel spreadsheet with thousands of rows and columns of data. This data contains length from station, bearing from station and levels of a survey undertaken. The survey has been done on various sections a railway.

What I would like to do is to create a template spreadsheet, and then populate this spreadsheet with the data from the survey data.

During this process, I would want to convert the bearing from degrees, minutes and seconds to decimal degrees.

Then I would like to import the data from the template spreadsheet into AutoCAD into the form of blocks.

I have been thinking of using dynamic blocks with attributes (which would contain the level and point identification data). The dynamic part of the block would contain the length and bearing from the station.

Each block would be put on an appropriate layer as defined by the template spreadsheet (taken from the survey data).

My first question is, is it possible to populate the settings of dynamic blocks with data from excel ?

Secondly, is there enough information contained above, for someone to either assist me, or point me in the correct direction ?

no help but just expanding the question...

does the data to change the dynamic block graphically? i.e. if the data is 3km & N will a bit oftrack be drawn 3km long in a north direction?

If so I know it can be done as I have tested VBA code to demonstate but I didn't get very far developing it.

I assume you are expecting an intermediate program to be doing the grunt work. Which language do you favour? If its anything other than VBA I'll sit back and watch. If its VBA I'll look for what I posted in the past. Bear in mind, I only got a little way towards proving that it could be done, not a fully finalised project.

As i read your post, you can cope with the survey side of things. But I would make the point that as AutoCAD has to be told which way round to measure angles, it would be better to reduce the survey readings to an x, y, z format in Excel if possible.

Each survey point is in effect a specific length and angle from the survey station. The length is not relative to the length of track, as in the attached pic

OK, moving away from my expertise anyway - I'm a straightforward mechanical engineer! I'll keep an eye where you are going and jump in if I can help.

I am looking for any help whatsoever in workflow issue that I have been presented with.

 

Put simply ( ! ) I have an excel spreadsheet with thousands of rows and columns of data. This data contains length from station, bearing from station and levels of a survey undertaken. The survey has been done on various sections a railway.

 

What I would like to do is to create a template spreadsheet, and then populate this spreadsheet with the data from the survey data.

During this process, I would want to convert the bearing from degrees, minutes and seconds to decimal degrees.

Then I would like to import the data from the template spreadsheet into AutoCAD into the form of blocks.

I have been thinking of using dynamic blocks with attributes (which would contain the level and point identification data). The dynamic part of the block would contain the length and bearing from the station.

Each block would be put on an appropriate layer as defined by the template spreadsheet (taken from the survey data).

 

My first question is, is it possible to populate the settings of dynamic blocks with data from excel ?

 

Secondly, is there enough information contained above, for someone to either assist me, or point me in the correct direction ?

Here is quick method to write data to Excel

(I guess you know it)

Keep in mind I know nothing about bearings

so you need to recalculate them all

(defun C:SPE (/ ang blk dis elev elist en fd fname inspt layer p2 pt sp ss)
 (prompt "\n\t\t***\tSelect Survey Station first\t***")
 (if (and
    (setq ss (ssget ":S:E:N" (list (cons 0 "INSERT")(cons 2 "Survey Station"))))
    (setq sp (ssget "X" (list (cons 0 "POINT")(cons 410 (getvar "CTAB")))))
 )
   (progn
       (setq blk (ssname ss 0))
 (setq inspt (cdr (assoc 10 (entget blk))))

 (setq fname (getfiled "* Set Name Of New Excel File *" "" "xls" 1))
 (setq fd (open fname "w"))
 (princ "Length from station\tBearing\tPoint Level\tPoint Layer\n" fd)
 (while (setq en (ssname sp 0))
   (setq elist (entget en))
   
   (setq pt (cdr (assoc 10 elist))
  elev (rtos (caddr pt) 2 3)
  dis (rtos (distance inspt pt) 2 3);<--metric distance
  ang (angtos (angle inspt pt) 0 2);<--degrees (recalculate as needed)
  layer (cdr (assoc 8 elist))
  )
   (princ (strcat dis "\t"
	   ang "\t"
	   elev "\t"
	   layer "\n")
   fd)
   (ssdel en sp)
)
 (close fd)
     )
   )
   (princ)
 )
(princ "\n\t\t***\tType SPE to write points\t***")
(princ)

~'J'~

Each survey point is in effect a specific length and angle from the survey station. The length is not relative to the length of track, as in the attached pic

Hope this would be interesting for you

Take a look at post #2 from this page:

http://www.cadtutor.net/forum/showthread.php?t=25629&highlight=topo.lsp

~'J'~

During this process, I would want to convert the bearing from degrees, minutes and seconds to decimal degrees.

Here is an Excel formula which converts a bearing written as ddd.mmss to decimal degrees. (Angle written in cell D1)

(INT(D1)+(INT(((D1)-INT(D1))*100)/60)+((((D1*100)-INT(D1*100))*100)/3600))

Thanks for all the help so far.

My current status with this is that I have now a template spreadsheet with the data formatted as to how I think it should be importable into CAD.

I am now stuck, however; as I wish to place blocks at the x,y,z co-ordinates taken from the spreasheet (simple enough, I know). I am having difficulties in automatting the filling in of the attributes, and the placement of the blocks onto specific layers - all as taken from data in the spreadsheet.

I posted a simple LISP a while back that inserts blocks listed in a CSV at the insertion point of a block whose handle is listed in the CSV. you should be able to use some of that code to insert your block on the right layer.

http://www.cadtutor.net/forum/showthread.php?t=40237&highlight=BATCH+BLOCK+INSERT

I can't help with the attributes.

Glen