Jump to content
samsudeenmanoos

How to update the details in the excel to attribute DWG block

Recommended Posts

samsudeenmanoos

Dear Guys,

 

Can you help me to solve this issue that I am trying to insert the attribute details in excel to the Dwg block

Is there any blocks or lisp if you have please send me rather than it is hard to update one by one 

 

I am using Autocad map 3D and i tried some other ways to insert as an object data with an attribute but the blocks become separate its self once it is imported

I have attached the excel file and Dwg herewith

 

Thanks

 

 

ATTRIBUTE DETAILS_.xlsx

GULLY DRAWING.dwg

Share this post


Link to post
Share on other sites
Emmanuel Delay
Posted (edited)

I made it for CSV.

So save the spreadsheet as CSV.  delimiter = ";"  make sure numbers have "." as decimal delimiter (which is not default in some countries)

 

I saved it to "C:\UserTemp\gully.csv".  Do the same, or change the line in the code to match the correct path\filename.

 

  (setq csvfile "C:\\UserTemp\\gully.csv")

 

Command FIAG (for Fill In Attributes Gully)

 

In attachment: the filled in dwg.  The CSV

I notice that a few blocks are not filled in.  I suppose the x and y value do not match the the insert point of those blocks well enough.


;; @see http://www.lee-mac.com/readcsv.html
;; Read CSV  -  Lee Mac
;; Parses a CSV file into a matrix list of cell values.
;; csv - [str] filename of CSV file to read
(defun LM:readcsv ( csv / des lst sep str )
    (if (setq des (open csv "r"))
        (progn
            (setq sep (cond ((vl-registry-read "HKEY_CURRENT_USER\\Control Panel\\International" "sList")) (",")))
            (while (setq str (read-line des))
                (setq lst (cons (LM:csv->lst str sep 0) lst))
            )
            (close des)
        )
    )
    (reverse lst)
)

;; CSV -> List  -  Lee Mac
;; Parses a line from a CSV file into a list of cell values.
;; str - [str] string read from CSV file
;; sep - [str] CSV separator token
;; pos - [int] initial position index (always zero)
(defun LM:csv->lst ( str sep pos / s )
    (cond
        (   (not (setq pos (vl-string-search sep str pos)))
            (if (wcmatch str "\"*\"")
                (list (LM:csv-replacequotes (substr str 2 (- (strlen str) 2))))
                (list str)
            )
        )
        (   (or (wcmatch (setq s (substr str 1 pos)) "\"*[~\"]")
                (and (wcmatch s "~*[~\"]*") (= 1 (logand 1 pos)))
            )
            (LM:csv->lst str sep (+ pos 2))
        )
        (   (wcmatch s "\"*\"")
            (cons
                (LM:csv-replacequotes (substr str 2 (- pos 2)))
                (LM:csv->lst (substr str (+ pos 2)) sep 0)
            )
        )
        (   (cons s (LM:csv->lst (substr str (+ pos 2)) sep 0)))
    )
)

(defun LM:csv-replacequotes ( str / pos )
    (setq pos 0)
    (while (setq pos (vl-string-search  "\"\"" str pos))
        (setq str (vl-string-subst "\"" "\"\"" str pos)
              pos (1+ pos)
        )
    )
    str
)

;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;

;; Set Attribute Value  -  Lee Mac
;; Sets the value of the first attribute with the given tag found within the block, if present.
;; blk - [ent] Block (Insert) Entity Name
;; tag - [str] Attribute TagString
;; val - [str] Attribute Value
;; Returns: [str] Attribute value if successful, else nil.
(defun LM:setattributevalue ( blk tag val / enx )
    (if (and (setq blk (entnext blk)) (= "ATTRIB" (cdr (assoc 0 (setq enx (entget blk))))))
        (if (= (strcase tag) (strcase (cdr (assoc 2 enx))))
            (if (entmod (subst (cons 1 val) (assoc 1 (reverse enx)) enx))
                (progn
                    (entupd blk)
                    val
                )
            )
            (LM:setattributevalue blk tag val)
        )
    )
)

;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;

(defun c:fiag ( / csvfile blockname data blocks i blk dst)
  (setq csvfile "C:\\UserTemp\\gully.csv")
  ;; (setq blockname "D_PR_SGW_GL")  ;; since it's a dynamic block I'll skip testing if the name is correct
  (setq data (LM:readcsv  csvfile))
  (setq blocks (ssget "_x" (list '(0 . "INSERT"))))
  (foreach row data
    (setq i 0)
    ;; search block by coordinates - see if the x,y is the same as the x,y in the csv
    (repeat (sslength blocks)
      (setq blk (ssname blocks i))
      ;; skip first row that contains the name of the columns
      (if (> i 0) (progn
        (setq dst (distance  ;; Ignoring the Z-axis
          (list (atof (nth 4 row)) (atof (nth 5 row))) (list (nth 0 (cdr (assoc 10 (entget blk)))) (nth 1 (cdr (assoc 10 (entget blk)))) )
        ))
        (if (< dst 0.01 ) (progn
          ;; fill in the attributes
          (LM:setattributevalue blk "PR-REF" (nth 0 row))
          (LM:setattributevalue blk "COVER"  (nth 1 row))
          (LM:setattributevalue blk "INVERT" (nth 2 row))
          (LM:setattributevalue blk "DEPTH"  (nth 3 row))
        ))
      ))
      (setq i (+ i 1))
    )
  )
  (princ)
)

 

 

 

GULLY DRAWING.dwg

gully.csv

Edited by Emmanuel Delay

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×