Insert Block with attributes from csv

[daniel6Z759]

Hello everyone,

I have a CSV file with several lines in it. Each line describes details of a certain block.

For example: the name of the block, X, Y and Z coordinates, and also his rotation.

I would be happy to know how to import the blocks with the data found in the CSV file into the drawing. I have attached the CSV file and also the block that I would like to insert into the drawing.

Thanks in advance.

Daniel

[Sea-Haven]

Try this, just have the correct file open in excel, as it will read the cell values from there.

; https://forums.autodesk.com/t5/visual-lisp-autolisp-and-general/insert-block-with-attributes-from-csv/td-p/12702922

(Defun c:charging ( / getrangexl csv->lst58 columnrow alpha2number getcell2 notfound)

; get active range selected
(defun getrangexl ( / lst UR CR RADD )
(setq lst '())
(setq UR (vlax-get-property  (vlax-get-property myxl "ActiveSheet") "UsedRange"))
(setq CR (vlax-get-property UR "CurrentRegion"))
(setq RADD (vlax-get-property CR "Address"))
(setq cnt (vlax-get-property CR  "Count"))
(setq lst (_csv->lst58 radd))
(setq st (vl-string-subst "" "$" (vl-string-subst "" "$" (nth 0 lst) )))
(setq end (vl-string-subst "" "$" (vl-string-subst "" "$" (nth 1 lst) )))
(setq st  (columnrow st))
(setq end  (columnrow end))
(princ st)
(princ "\n")
(princ end)
)

; thanks to Lee-mac for this defun 
; www.lee-mac.com
; 44 is comma 9 is tab 34 is space 58 is colon
(defun _csv->lst58 ( str / pos )
	(if (setq pos (vl-string-position 58 str))
		(cons (substr str 1 pos) (_csv->lst58 (substr str (+ pos 2))))
		(list str)
    )
)

; ColumnRow - Returns a list of the Column and Row number
; Function By: Gilles Chanteau from Marseille, France
; Arguments: 1
;   Cell$ = Cell ID
; Syntax example: (ColumnRow "ABC987") = '(731 987)
;default to "A1" if there's a problem
;-------------------------------------------------------------------------------
(defun ColumnRow (Cell$ / Column$ Char$ Row#)
  (setq Column$ "")
  (while (< 64 (ascii (setq Char$ (strcase (substr Cell$ 1 1)))) 91)
    (setq Column$ (strcat Column$ Char$)
          Cell$ (substr Cell$ 2)
    )
  )
  (if (and (/= Column$ "") (numberp (setq Row# (read Cell$))))
    (list (Alpha2Number Column$) Row#)
    '(1 1)
  )
)

; Alpha2Number - Converts Alpha string into Number
; Function By: Gilles Chanteau from Marseille, France
; Arguments: 1
;   Str$ = String to convert
; Syntax example: (Alpha2Number "ABC") = 731
;-------------------------------------------------------------------------------
(defun Alpha2Number (Str$ / Num#)
  (if (= 0 (setq Num# (strlen Str$)))
    0
    (+ (* (- (ascii (strcase (substr Str$ 1 1))) 64) (expt 26 (1- Num#)))
       (Alpha2Number (substr Str$ 2))
    )
  )
)

;;	Thanks to fixo			;;
(defun getcell2 (row column / )
(setq cells (vlax-get-property  (vlax-get-property myxl "ActiveSheet") "Cells"))
(setq cell (vlax-get (vlax-variant-value  (vlax-get-property cells "Item" row column)) 'value))
)

;The dtr function converts degrees to radians
;The rtd function converts radians to degrees
(defun dtr (a)
(* pi (/ a 180.0))
)
;
(defun rtd (a)
(/ (* a 180.0) pi)
)

;; starts here

(or (setq myxl (vlax-get-object "Excel.Application"))
    (setq myxl (vlax-get-or-create-object "excel.Application"))
)

(getrangexl)

(setvar 'attreq 1)

(setq row 2)
(repeat (- (cadr end) 1)
(setq bname (getcell2 row 2))
(setq x (getcell2 row 13))
(setq y (getcell2 row 14))
(setq scx (getcell2 row 10))
(setq scy (getcell2 row 11))
(setq rot (getcell2 row 17))
(setq manuf (getcell2 row 3))
(setq power (getcell2 row 4))
(setq zira (getcell2 row 9))
(setq num (getcell2 row 8))
(setq misp (getcell2 row 7))
(setq cellt (getcell2 row 6))
(setq bid (fix (getcell2 row 5)))
(if (= (tblsearch "block" bname) nil)
(progn 
  (princ  (strcat "\n" bname " Block  is not in the dwg skipping insert "))
  (setq notfound "yes")
  )
  (command "-insert" bname (list x y) scx scy rot manuf power zira num misp cellt bid)
)
(setq row (1+ row))
)

(if (not (vlax-object-released-p myXL))(progn(vlax-release-object myXL)(setq myXL nil)))

(command "zoom" "e")

(if (= notfound "yes")
(alert "Various blocks were not found you need to do again \nClose dwg and do not save \nreopn and add blocks before running")
)


(princ)
)

 Make sure the required blocks are in the dwg. 

[Amriya_Exe]

Command: CHARGING
; error: too few actual parameters
Can you try or give more details How to use?

[daniel6Z759]

Hey,
Thank you very much for your reply.
The CSV file was open using Excel and I want the following errors:

Command: CHARGING

; error: bad argument type: VLA-OBJECT nil

[ec-cad]

I'm a little late, but this lisp will read from your .csv format. Tested.

Does about the same thing as previous post.

Cheers

ECCAD

[Sea-Haven]

Sorry forgot a big important step you have no block called "AV_charging_station" so it will not work, you have attdef's not blocks. So I made the AV_charging_station by using the block command and selecting all the attdefs. Then it should work. You need to understand the difference of a block with attributes versus just attdefs.

[ec-cad]

Sea-Haven,

Looks like you missed the fact that the Av_charging_station.dwg that was at top of this post (IS) a block, not just Attdef's.  Download that original .dwg block, Open any other drawing, and do an insert of that Block. If you have Attreq set to a 1, then it will prompt for you to fill in the attribute values, 7 in total for this block.

My lisp attached in this post called 'Av_stations.lsp' needs only 1 line changed, to define the variable:

 (setq BLOCKPATH "C:/DISK8/")

OP would only need to adjust that path to where they store their block(s).

Also, my lisp prompts the OP to pick the .csv file. It then reads that .csv, gets the data into local variable lists,

gets each set of data, inserts the blockname from the blockpath folder, and fills in the attributes.

The only thing is, IF the OP has a variety of blocks, with attributes that do not match the Av_charging_station block attributes (as far as mapping them to the data lists), will not work as expected for those blocknames.

Your Lisp also would have the same issue. So, we would need to get a Block drawing for each of those that need to be inserted, change the code to be (if (= bname "other_block")(progn...... fill in correct attribute values.

One section for each of the different blocks.

ECCAD

[ec-cad]

Daniel6Z759,

Please see Message 7 near end of this thread.

The lisp attached only needs (1) line changed to insert those Av_charging_station blocks.

 (setq BLOCKPATH "C:/DISK8/")

OP would only need to adjust that path to where they store their block(s).

Please try it out, and if it works for you, we can tune it for 'other' blocknames.

Also, if you think it answers your question, then please mark this thread as 'Solved'.

ECCAD

[Sea-Haven]

"Open any other drawing, and do an insert of that Block." This takes the attdefs and when inserted makes a block. But if poster does same for other block names really should have all blocks set up as just that.

You are correct about blocks with different number of attributes, I am sure some where else read say all excel cells as as attributes but if blank set value to something that was ignored, with some form of column limit, it also took advantage of the block attribute order not visual display so tag name was not required when changing attributes. 

Yes a bit of a nightmare where mixed block names and number of attributes.