import multible data from excell

[martinpd]

A newbie here. We have an excell list of 1500 radio sites with co-ordinates and labels. We need to import all this to the right locations, insert a tower symbol ( block), label it and put the co-ordinates under the label. We're using AutoCad Map 3D 2010. Does anyone have a script or easier methodology than doing all this manually? Thanks.

[irneb]

Could you attach a sample of the excel file (no need for all 1500, just a couple should be fine). And then, some more info about the block (Name, attributes, dynamic properties, annotative scaling, etc.) - perhaps attaching that as well would be better

[fixo]

A newbie here. We have an excell list of 1500 radio sites with co-ordinates and labels. We need to import all this to the right locations, insert a tower symbol ( block), label it and put the co-ordinates under the label. We're using AutoCad Map 3D 2010. Does anyone have a script or easier methodology than doing all this manually? Thanks.

Could you insert just one block manually for example and attach this drawing as well

(I'm using A2008)

 

~'J'~

[irneb]

Just realized, there's a limitation on attachments prior to your 10th post . Could you in the meantime work with us on describing some of this stuff. E.g. the XLS file's tab name, range of data, column titles & description. Also do a list of one of the blocks as you would want it from one of those lines in the XLS.

[martinpd]

On the edit screen I don't see a command to delete or move this, thus the double post. Sorry to offend.

Paul

[martinpd]

The block is just a graphic symbol of a tower. The tower's name varies but something like B123456. That would be one column in excell. The next column would be decimal degrees latitude and the next would be decimal degrees longitude. All location appropriate data from all three columns would appear with the tower symbol as well as being placed in the right location. The geographic location data would cover Ohio and surrounding areas. We could change the column titles and sequence to what ever would be needed. Thanks.

[ReMark]

Have you seen this program?

 

http://lee-mac.com/ptmanager.html

[Tiger]

On the edit screen I don't see a command to delete or move this, thus the double post. Sorry to offend.

Paul

 

Threads merged - good luck

[fixo]

The block is just a graphic symbol of a tower. The tower's name varies but something like B123456. That would be one column in excell. The next column would be decimal degrees latitude and the next would be decimal degrees longitude. All location appropriate data from all three columns would appear with the tower symbol as well as being placed in the right location. The geographic location data would cover Ohio and surrounding areas. We could change the column titles and sequence to what ever would be needed. Thanks.

Give this a shot

(vl-load-com)
;;local defun
(defun read_excel_range (FilePath ShtNum Address / ExcelApp ExcData Sht UsdRange Wbk)
(setq ExcelApp (vlax-get-or-create-object "Excel.Application"))
(vla-put-visible ExcelApp :vlax-true)
(vlax-put-property ExcelApp 'DisplayAlerts :vlax-true)
(setq Wbk (vl-catch-all-apply
'vla-open
(list (vlax-get-property ExcelApp "WorkBooks") FilePath)
)
)
(setq Sht (vl-catch-all-apply
'vlax-get-property 
(list (vlax-get-property Wbk "Sheets")
"Item"
ShtNum
)
)
)
(vlax-invoke-method Sht "Activate")
(setq UsdRange (vlax-get-property
(vlax-get-property Sht 'Cells)
"Range"
Address)
UsdRange (vlax-get-property
UsdRange
"CurrentRegion"
)
ExcData (vlax-safearray->list
(vlax-variant-value
(vlax-get-property UsdRange 'Value2)
)
)
)
(setq
ExcData (mapcar
(function (lambda (x) (mapcar 'vlax-variant-value x)))
ExcData
)
)
(setq
ExcData (vl-remove-if
(function (lambda (x) (member nil x)))
ExcData
)
)
(vl-catch-all-apply
'vlax-invoke-method
(list Wbk "Close" :vlax-false)
)
(vl-catch-all-apply
'vlax-invoke-method
(list ExcelApp "Quit")
)
(mapcar
(function
(lambda (x)
(vl-catch-all-apply
(function (lambda ()
(progn
(if (not (vlax-object-released-p x))
(progn
(vlax-release-object x)
(setq x nil)
)
)
)
)
)
)
)
)
(list UsdRange Sht Wbk ExcelApp)
)

(gc)
(gc)
(gc)
ExcData
)
;; main program
(defun C:STX (/ acsp address attcoll blkname blkobj data_list fname inspoint sheetnum)
(setvar "pdmode" 34)
(setvar "pdsize" 2)
(setq blkname "R-STATION");<-- change block name here
(setq sheetnum "stations");<-- change excel sheet name or use sheet number here
(setq address "A:C") ;<--3 columns

(if (not (tblsearch "block" blkname))
(progn
(alert (strcat "Block " "\""blkname"\"" " does not exist\nProgram exiting.."))
(exit)(princ))
)

(if 
(setq fname (getfiled "Select Excel File:" (getvar "dwgprefix") "XLS" 16)
)
(progn
(setq data_list (read_excel_range fname sheetnum address))
;; remove headers from list
(setq data_list (vl-remove-if
(function (lambda (a) (or(not (numberp (cadr a)))(not (numberp (caddr a))))))
data_list))
(if data_list
(progn
(setq acsp
(vlax-get-property
(vla-get-activedocument
(vlax-get-acad-object))
(if (equal (getvar "cvport") 1)
'PaperSpace
'ModelSpace
)
)
)

(foreach item data_list

(setq blkobj (vlax-invoke-method
acsp
'InsertBlock 
(setq inspoint (vlax-3d-point(list (cadr item) (caddr item) 0.)))
blkname
1. 1. 1. 0.
)
)

(setq attcoll (vlax-invoke blkobj 'GetAttributes))

(foreach attobj attcoll
(cond ((eq "LABEL" (vla-get-tagstring attobj));<-- change tag here
(vla-put-textstring attobj (vl-princ-to-string (car item))
)
)
((eq "LATITUDE" (vla-get-tagstring attobj));<-- change tag here
(vla-put-textstring attobj (vl-princ-to-string (cadr item))
)
)
((eq "LONGITUDE" (vla-get-tagstring attobj));<-- change tag here
(vla-put-textstring attobj (vl-princ-to-string (caddr item))
)
)
)
)
)
)
)
(vla-zoomextents (vlax-get-acad-object))
)
)
(princ)
)
(prompt
"\n\t\t>>>\tType STX or stx to execute\t<<<")
(prin1)

[martinpd]

Thanks for your help, Fixo. I made an excel file with 3 columns; Tower name, latitude, and longitude and changed the appropriate places in your script. when I run the app I get this response:

; error: bad argument type: VLA-OBJECT #. Any ideas on what I'm doing wrong?

[fixo]

Go through main program line by line

You could see on which line this lisp stops

Think the problem is on Excel part

[stevesfr]

Thanks for your help, Fixo. I made an excel file with 3 columns; Tower name, latitude, and longitude and changed the appropriate places in your script. when I run the app I get this response:

; error: bad argument type: VLA-OBJECT #. Any ideas on what I'm doing wrong?

 

Martin:

Some ideas to check:

Is the name of your block R-STATION ? or if its not, then did you edit the lisp ?

Are the three attributes of your block LABEL, LATITUDE, and LONGITUDE ?

bear in mind that Latitude will be the "Y" location in the drawing

ACAD is stupid and will enter the first coordinate of a pair and enter it as "X" location, so

be sure to list the insertion point of one of the blocks in the drawing to be sure data is at proper location.

HTH, Steve

[fixo]

Martin:

Some ideas to check:

Is the name of your block R-STATION ? or if its not, then did you edit the lisp ?

Are the three attributes of your block LABEL, LATITUDE, and LONGITUDE ?

bear in mind that Latitude will be the "Y" location in the drawing

ACAD is stupid and will enter the first coordinate of a pair and enter it as "X" location, so

be sure to list the insertion point of one of the blocks in the drawing to be sure data is at proper location.

HTH, Steve

Thanks Steve

It's my bad

Here is edited lisp

I used Excel file where are the columns "B" and "C" in the number format (two decimals precision) and column "A" in the text format

(vl-load-com)
;;local defun
(defun read_excel_range (FilePath ShtNum Address / ExcelApp ExcData Sht UsdRange Wbk)
(setq ExcelApp (vlax-get-or-create-object "Excel.Application"))
(vla-put-visible ExcelApp :vlax-true)
(vlax-put-property ExcelApp 'DisplayAlerts :vlax-true)
(setq Wbk (vl-catch-all-apply
    'vla-open
    (list (vlax-get-property ExcelApp "WorkBooks") FilePath)
    )
     )
(setq Sht (vl-catch-all-apply
    'vlax-get-property 
    (list (vlax-get-property Wbk "Sheets")
   "Item"
   ShtNum
   )
    )
     )
(vlax-invoke-method Sht "Activate")
(setq UsdRange (vlax-get-property
  (vlax-get-property Sht 'Cells)
  "Range"
  Address)
     UsdRange (vlax-get-property
  UsdRange
  "CurrentRegion"
  )
     ExcData  (vlax-safearray->list
  (vlax-variant-value
    (vlax-get-property UsdRange 'Value2)
    )
  )
     )
(setq
 ExcData (mapcar
    (function (lambda (x) (mapcar 'vlax-variant-value x)))
    ExcData
    )
 )
(setq
 ExcData (vl-remove-if
    (function (lambda (x) (member nil x)))
    ExcData
    )
 )
(vl-catch-all-apply
 'vlax-invoke-method
 (list Wbk "Close" :vlax-false)
 )
(vl-catch-all-apply
 'vlax-invoke-method
 (list ExcelApp "Quit")
 )
(mapcar
 (function
   (lambda (x)
     (vl-catch-all-apply
(function (lambda ()
     (progn
       (if (not (vlax-object-released-p x))
  (progn
    (vlax-release-object x)
    (setq x nil)
    )
  )
       )
     )
   )
)
     )
   )
 (list UsdRange Sht Wbk ExcelApp)
 )

 (gc)
 (gc)
 (gc)
 ExcData
)
;; main program
(defun C:STX (/ acsp address attcoll blkname blkobj data_list fname inspoint sheetnum)
(setvar "pdmode" 34)
(setvar "pdsize" 2)
 (setq blkname "R-STATION");<--  change block name here
 (setq sheetnum "stations");<--  change excel sheet name or use sheet number here
 (setq address "A:C") ;<--3 columns range

 (if (not (tblsearch "block" blkname))
   (progn
(alert (strcat "Block " "\""blkname"\"" " does not exist\nProgram exiting.."))
(exit)(princ))
   )

(if 
(setq fname (getfiled "Select Excel File:" (getvar "dwgprefix") "XLS" 16)
      )
 (progn
(setq data_list (read_excel_range fname sheetnum address))
;; remove headers from list
(setq data_list (vl-remove-if
    (function (lambda (a) (or(not (numberp (cadr a)))(not (numberp (caddr a))))))
    data_list))

(if data_list
  (progn
      (setq acsp
       (vlax-get-property
           (vla-get-activedocument
       (vlax-get-acad-object))
           (if (equal (getvar "cvport") 1)
               'PaperSpace
               'ModelSpace
           )
       )
   )

    (foreach item  data_list

      (setq blkobj (vlax-invoke-method
 acsp
 'InsertBlock 
   (setq inspoint (vlax-3d-point(list (caddr item) (cadr item) 0.)))
 blkname
  1. 1. 1. 0.
 )
     )

      (setq attcoll (vlax-invoke blkobj 'GetAttributes))

      (foreach attobj attcoll
 (cond ((eq "LABEL" (vla-get-tagstring attobj));<--Station, change tag here
   (vla-put-textstring attobj (vl-princ-to-string (car item))
     )
   )
       ((eq "LATITUDE" (vla-get-tagstring attobj));<--Y,  change tag here
   (vla-put-textstring attobj (vl-princ-to-string (caddr item))
     )
   )
       ((eq "LONGITUDE" (vla-get-tagstring attobj));<--X, change tag here
   (vla-put-textstring attobj (vl-princ-to-string (cadr item))
     )
   )
  )
 )
      )
    )
  )
(vla-zoomextents (vlax-get-acad-object))
)
  )
 (princ)
 )
(prompt
 "\n\t\t>>>\tType STX or stx to execute\t<<<")
(prin1)

[martinpd]

How do you "step through" a lsp app to see where it fails (like debug in the old DOS days of programming)?

[stevesfr]

How do you "step through" a lsp app to see where it fails (like debug in the old DOS days of programming)?

 

See here:

http://www.lee-mac.com/introtovlide.html

[martinpd]

Thanks everyone! We're getting into territory I haven't touched before. I'm gonna do some studying on this over the weekend before I make a fool of mself. Vlide opens up a new world for me.