INSERT ATTRIBUTES BLOCK FROM EXCEL DATA

[shil143]

Hi,

i want insert attributes block based on excel data. attributes want to fill up with automatically from excel.

please refer attachment

[TheCADnoob · Accepted solution]

This will be a bit of a convoluted process as there is a trick we will have to take to get there but it can be done fairly easily. What we can do is create a script file from your excel.

First we will create a formula, then we will copy that formula to WordPad and then to Notepad (we do this because of a text formatting issue pasting out of excel) and then save the notepad to a script file. We paste it into two locations so that we can get it into the right text format.

Excel Formula

=CONCATENATE("-insert",CHAR(10),"D_EX_SGW_ATT",CHAR(10),Sheet1!B2,",",Sheet1!C2,CHAR(10),"1",CHAR(10),"1",CHAR(10),"0",CHAR(10),Sheet1!A2,CHAR(10),Sheet1!B2,CHAR(10),Sheet1!C2,CHAR(10),Sheet1!D2,CHAR(10),Sheet1!E2,CHAR(10),"||||",)

First Turn off ATTDIA in AutoCAD. This will prevent the Attribute dialog from popping up during the script. 

Next copy this formula into sheet2 in your excel sheet and drag the formula down. Next tell excel to wrap the text. 

Copy the output from excel and paste it into WordPad. This will allow it to keep the carriage return format of the text. 

Next copy everything from wordpad and paste it into NotePad. This will remove any other unwanted formatting. 

Next we need to do a character replacement for the vertical bar "|"

press CTRL+H

note: this is a bar character and not the letter "L" or the number "1" nor the letter"I"

We replace the bar character "|" with a space " "

Once that is done save the file as a script (.scr) file. 

Click and drag the script file into AutoCAD and let AutoCAD do the rest. 

[shil143]

Thanks.........TheCADnoob, 

for me when i read script file inserting only one block, with wrong data. please check attached drawing

[CADaSchtroumpf · Accepted solution]

Hi,

With your drawing...

Before convert MH_COORDI.xlsx to CSV (replace "," by ".")

After this, you can try use this:

(defun c:readCSV ( / input f_open l_read tag easting northing cover inv ins_pt)
  (setq
    input (getfiled "Select a CSV file" "" "csv" 2)
    f_open (open input "r")
  )
  (setq l_read (read-line f_open))
  (while (setq l_read (read-line f_open))
    (setq tag (substr l_read 1 (vl-string-position 59 l_read)))
    (setq l_read (substr l_read (+ 2 (vl-string-position 59 l_read))))
    (setq easting (atof (substr l_read 1 (vl-string-position 59 l_read))))
    (setq l_read (substr l_read (+ 2 (vl-string-position 59 l_read))))
    (setq northing (atof (substr l_read 1 (vl-string-position 59 l_read))))
    (setq l_read (substr l_read (+ 2 (vl-string-position 59 l_read))))
    (setq cover (atof (substr l_read 1 (vl-string-position 59 l_read))))
    (setq l_read (substr l_read (+ 2 (vl-string-position 59 l_read))))
    (setq inv (atof (substr l_read 1 (vl-string-position 59 l_read))))
    (setq ins_pt (list easting northing))
    (entmake
      (list
        '(0 . "INSERT")
        '(100 . "AcDbEntity")
        '(67 . 0)
        '(410 . "Model")
        '(8 . "ele_FSD")
        '(100 . "AcDbBlockReference")
        '(66 . 1)
        '(2 . "MH_ATT")
        (cons 10 ins_pt)
        '(41 . 1.0)
        '(42 . 1.0)
        '(43 . 1.0)
        '(50 . 0.0)
        '(70 . 0)
        '(71 . 0)
        '(44 . 0.0)
        '(45 . 0.0)
        '(210 0.0 0.0 1.0)
      )
    )
    (mapcar
      '(lambda (value tag_att delta / )
        (entmake
          (list
            '(0 . "ATTRIB")
            '(100 . "AcDbEntity")
            '(67 . 0)
            '(410 . "Model")
            (if (eq tag_att "MH_ID") '(8 . "0") '(8 . "D_EX_SGW_ATT"))
            '(100 . "AcDbText")
            (cons 10 (list (+ (car ins_pt) (car delta)) (+ (cadr ins_pt) (cadr delta)) 0.0))
            '(40 . 1.1)
            (cons 1 value)
            '(50 . 0.0)
            '(41 . 1.0)
            '(51 . 0.0)
            '(7 . "ISOCP")
            '(71 . 0)
            (if (eq tag_att "MH_ID") '(72 . 4) '(72 . 0))
            (if (eq tag_att "MH_ID")
              (cons 11 (list (+ (car ins_pt) -11.25) (+ (cadr ins_pt) 8.55474655) 0.0))
              '(11 0.0 0.0 0.0)
            )
            '(210 0.0 0.0 1.0)
            '(100 . "AcDbAttribute")
            (cons 2 tag_att)
            '(70 . 0)
            '(73 . 0)
            '(74 . 0)
            '(280 . 0)
          )
        )
      )
      (list tag (rtos easting 2 3) (rtos northing 2 3) (rtos cover 2 3) (rtos inv 2 3))
      '("MH_ID" "EAST" "NORTH" "ELEVATION" "IN.LEVEL")
      '((-19.66692188 8.00474655) (-15.98193473 6.14443250) (-15.98193473 4.23173750) (-15.98193473 2.31904250) (-15.98193473 0.40634750))
    )
    (entmake 
      '(
        (0 . "SEQEND")
        (100 . "AcDbEntity")
        (67 . 0)
        (410 . "Model")
        (8 . "ele_FSD")
      )
    )
  )
  (close f_open)
  (prin1)
)

[TheCADnoob · Accepted solution]

It looks like there might be something off with your script file. I've attached the script file i created. review it and see how yours differs. 

(note: i had to change it to a .txt to be able to attach it.)

If you are going to use the data as attached, please validate it! i did not check it for consistency. I built it as proof of principle only!

[shil143]

Thanks  

[shil143]

as usual Thanks .........