Lisp import excel data and place the blocks in the coordinates (start-end)

[Edwin.Saez]

hi everyone!, They would like help with a lisp for the following:

I have a block that has a start and end point. I have an excel with information of the start and end coordinates, so I would like to import the excel data, the blocks will fit the excel information with both the start and end coordinates.

I attach the block and excel

[pbejse]

This is similar to a recent discussion,

Question: Do you NEED to do this with excel or CSV format will do? One nice thing about writing a code to read a text file format is, the program will work even without excel installed on the machine.

What say you edwin.saez.jamanca?

[Edwin.Saez]

Thanks for answering @pbejse

In either format it would be excellent.

[pbejse]

---

@Edwin.Saez.Jamanca wrote:
Thanks for answering @pbejse

In either format it would be excellent.

---

We will make it so edwin

Curious about the value at column DISTANCIA "61" where is that based from? I see you used fields on the dynamic block. 

Assuming COOR_F-X,COOR_F-Y is start point  (100.00 100.00 0.0) and COOR_I-X,COOR_I-Y (700.00 700.00 0.00) as the endpoint,DISTANCIA attribute value would be 849. what is 61 then?

BTW: Id don't have excel on my machine right now. can you save your file in csv format?

[Edwin.Saez]

@pbejse

COOR_F-X,COOR_F-Y is end point
COOR_I-X,COOR_F-Y is start point
DISTANCIA = Distance between the 2 coordinate points

[pbejse]

---

@Edwin.Saez.Jamanca wrote:

COOR_F-X,COOR_F-Y is end point
COOR_I-X,COOR_F-Y is start point....

---

Got that.. 

---

DISTANCIA = Distance between the 2 coordinate points

---

Lost me there. on the points i posted earlier, reversing the coordinates will still give me the same distance of 849. we can force 61 as DISTANCIA distance parameter value but the end point would be  (656.87  656.87 0.00) . or we can force DISTANCIA attribute value to 61. Which one is the preferred value? or are we dealing with drawing units issue?

Again, can you save the excel file to CSV format edwin.

confused pBe

[Edwin.Saez]

@pbejse,

It was my mistake @pbejse, in excel attach you can delete the column distance. Since that value in the dynamic block is associated with the distance of the line, and the distance of the line will change as a function of the start and end coordinates.
Do not take into account the attribute "DISTANCE".

Thanks for the help

[pbejse]

---

@Edwin.Saez.Jamanca wrote:

@pbejse,

It was my mistake @pbejse, in excel attach you can delete the column distance. Since that value in the dynamic block is associated with the distance of the line, and the distance of the line will change as a function of the start and end coordinates.
Do not take into account the attribute "DISTANCE".

Thanks for the help

---

Now that makes more sense, Distance value at block will remain as per coordinates. Also second row of valid data does not have NAME value, does that mean the name from the previous row will be use?

I'll write a quick code, hang on. AND where is that CSV i asked for? Nowadays i'm not really 100% sure of the result when saving CSV from different versions of excel (or it doesn't really have an effect at all). 😄

[Edwin.Saez]

@pbejse,

Yes, it could be the same name, or it could be another name. Maybe as long as there are different blocks in the drawing, and then import from the excel or csv data, each block (same or different) is inserted with the indicated coordinate data.

[pbejse]

---

@Edwin.Saez.Jamanca wrote:

Yes, it could be the same name, or it could be another name. Maybe as long as there are different blocks in the drawing, and then import from the excel or csv data, each block (same or different) is inserted with the indicated coordinate data.

---

Roger that.

I supposed this is a mistake as well?

COOR_F-X,COOR_F-Y is end point
COOR_I-X,COOR_F-Y is start point <-- should that be COOR_I-X,COOR_I-Y is start point?

[Edwin.Saez]

@pbejse,

Exactly, it is as you say it.

COOR_I-X,COOR_I-Y is start point

[pbejse]

---

@Edwin.Saez.Jamanca wrote:

....COOR_I-X,COOR_I-Y is start point

---

Ok , despite not providing the CSV file I repeatedly asked for, I took a the leap of faith and wrote the code without it.

(defun C:ESJ (/ doc _delimiter  l2p allTheDataNeeded
                theCSVSoure openedCSVFile coordinates bname )
(setq aDoc (vla-get-ActiveDocument (vlax-get-acad-object))
           *mspace* (vla-get-ModelSpace aDoc))

(defun _delimiter (str md / d l str)
    (if	(listp str)
      (apply 'strcat
	     (cons (car str)
		   (mapcar '(lambda (r) (strcat (chr md) r))
			   (cdr str)
		   )
	     )
      )
      (progn
	(while (setq d (vl-string-position md str nil T))
	  (setq	l   (cons (substr str (+ 2 d)) l)
		str (substr str 1 d)
	  )
	)
	(cons str l)	
      )
    )
  )

  (defun l2p (l)
    (if (>=  (length l) 2)
      (cons (list (distof (car l))
                  (distof (cadr l)) 0.0) (l2p (cddr l)))
    )
  )

(if (setq allTheDataNeeded
               nil
          theCSVSoure
               (getfiled "Select CSV file"
                     (getvar 'dwgprefix)  "csv" 16))
(progn
(setq openedCSVFile (open theCSVSoure "r"))
(while (setq a (read-line openedCSVFile))
      		(setq b (_delimiter a 44))
      		(setq allTheDataNeeded (cons b allTheDataNeeded))
      	)
(setq allTheDataNeeded (cdr (reverse allTheDataNeeded)))
(close openedCSVFile)

(foreach data allTheDataNeeded
      (if
            (and
		(setq coordinates (L2P  (cdddr data)))
                (snvalid (setq bname (cadr data))))
            (progn
		(setq Dblock (vlax-invoke *mspace*
			'InsertBlock (Cadr coordinates)  bname  1 1 1 0))
		(vlax-put Dblock 'Rotation (angle  (Cadr coordinates) (Car coordinates)))
		(setq curretnStateOfDBlock
		(mapcar '(lambda (dbcs)
		          (cons  (vlax-get dbcs 'PropertyName) dbcs))
		                 (vlax-invoke Dblock 'GetDynamicBlockProperties)
		                 ))
		(vlax-put (cdr (assoc "LONGITUD" curretnStateOfDBlock)) 'Value
		(distance (Cadr coordinates) (Car coordinates)))
		(vl-some '(lambda (atb)
		        (if (equal (strcase (vla-get-tagstring atb)) "CODIGO")
		            (null (Vla-put-textstring atb (Caddr data)))))
		        
		        (vlax-invoke Dblock 'getattributes))
                )
                )
	)
)
    )
      (vla-regen aDoc acallviewports)
)

Keep in mind, the CSV data should at the least have values for these columns Name,CODIGO,COOR_F-X,COOR_F-Y,COOR_I-X,COOR_I-Y and the blocks exist in the drawing, No error trap for non-existing blocks , I leave that to you to sort out. Anyways  you can always ask for help

The result from running this code is the "as per coordinate.dwg"  the other is what it would look if you used the values in DISTANCIA column (no code posted for this yet | but i wrote the code anyway at the ready)

HTH

[Edwin.Saez]

@pbejse,

Thanks for writing the code

When I run it I get "error: AutoCAD.Application: Filer error".

I attach the csv that I use.

The process I do is, in the drawing insert the block, then I delete it so that it is only stored in memory, and then I execute the lisp and I get the error that tells you.

I am doing something wrong?

[pbejse]

---

@Edwin.Saez.Jamanca wrote:

Thanks for writing the code

When I run it I get "error: AutoCAD.Application: Filer error".

I attach the csv that I use.

The process I do is, in the drawing insert the block, then I delete it so that it is only stored in memory, and then I execute the lisp and I get the error that tells you.

I am doing something wrong?

---

Yes, it would do that. because **

I guess you meant, you inserted the block manually, gather the data, copy and paste it to excel, save hte file in csv format then run the code, is that correct?

It would've been avoided  if you  had attached the CSV file 8 post ago 😄 < - no worries edwin, all part of learning.

This format edwin

Name,CODIGO,COOR_F-X,COOR_F-Y,COOR_I-X,COOR_I-Y
LR_BPP01,LR-XX17-001,310197.00,8845307.00,310197.00,8845407.00

Still no error trap for whatever. we can spend more time writing smarter code to identify which column to read for the data, but you need to decide what the final CSV format is OR we just use the excel file and based the values by column names. but for now.....

See attached lisp file InsertDblockFromCSV.lsp

Still no error trap for whatever.

HTH

[pbejse]

---

@pbejse wrote:

.....code to identify which column to read for the data,... 

---

The attached code includes: [ still CSV format ]

....
(foreach itm ColumNnames ;; complete column names as per csv file
                      (setq n (1-  n))
                      (if (member itm '("Name" "CODIGO"
                                        "COOR_F-X" "COOR_F-Y"
                                        "COOR_I-X" "COOR_I-Y"))
                          	(setq format (cons (list itm n) format))))... ; filtered list of valid column names

..
(setq data (mapcar '(lambda (x)
           		(nth x data)) (mapcar 'cadr format)))

HTH

[Edwin.Saez]

@pbejse,

Sorry for the delay in answering.
Something happens with this last lisp attached, does nothing.
 
I also attach the final csv format. And a dwg, to know if you could add a function to lisp, to be able to insert the data in the drawing in the form of a table and with the format that I send in the drawing.

[pbejse]

---

@Edwin.Saez.Jamanca wrote:

@pbejse,

Sorry for the delay in answering.
Something happens with this last lisp attached, does nothing.

---

There's always something eh?  Now its trailing white space.

---

@Edwin.Saez.Jamanca wrote:

I also attach the final csv format. And a dwg, to know if you could add a function to lisp, to be able to insert the data in the drawing in the form of a table and with the format that I send in the drawing.

---

Yes we can., We will hold off on using ACAD_TABLE for now and stick with attribute blocks. 

Any more surprises coming edwin?

EDIT: Did you do a "saveas" off the excel file or you type the value directly on notepad and save as CSV?

[Edwin.Saez]

---

Now its trailing white space.

---

I'm sorry, I did not quite understand what he meant. Does it refer to adding the code written in the blank space within the lisp?

---

---

Yes we can., We will hold off on using ACAD_TABLE for now and stick with attribute blocks. 

Any more surprises coming edwin?

---

I'm sorry again, it's the last thing I need for what I need.

* Copy the data separated by commas in a txt, and open it from excel and save it as csv.

I really appreciate your help @pbejse.

[pbejse · Accepted solution]

---

@Edwin.Saez.Jamanca wrote:

 ... * Copy the data separated by commas in a txt, and open it from excel and save it as csv.

I really appreciate your help @pbejse.

---

Oh my, no wonder there are inconsistencies every time you post a sample csv or its format.

First of all, what is the file format of the coordinates value? The title says  "... Lisp import excel data..."  

Well anyway..

The attached lisp file should be able to recognize the column names with or without leading/trailing whitespace.

The required blocks ( "ESJ_HEADER" and "ESJ_DATA" ) for the table can be found on the attached drawing file "edwin_SJ.dwg"

The DATA table will be located with in the lower left corner of the last block create

HTH