Jump to content

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


samsudeenmanoos

Recommended Posts

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

Link to comment
Share on other sites

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
Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Unfortunately, your content contains terms that we do not allow. Please edit your content to remove the highlighted words below.
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...