topic Re: Text from selection to excel in Visual LISP, AutoLISP and General Customization Forum

Text from selection to excel

Philip-John — Tue, 23 Feb 2021 18:22:26 GMT

I have some texts (top and bottom) in my drawing as above. I just need to have these in excel.

After selection of the top texts (point 01 - 08) it should go in first column of excel file and after selection of bottom texts (23.65 - 22.10) it should go in second column of excel file.

Re: Text from selection to excel

pbejse — Tue, 23 Feb 2021 18:36:46 GMT

@Philip-John wrote:

I have some texts (top and bottom) in my drawing as above. I just need to have these in excel.

After selection of the top texts (point 01 - 08) it should go in first column of excel file and after selection of bottom texts (23.65 - 22.10) it should go in second column of excel file.

Easy does it.

Re: Text from selection to excel

JTBWorld — Tue, 23 Feb 2021 19:20:09 GMT

Use the txt2mtxt command and select all text. Then edit the text and copy the text from within the text editor and paste it to Excel.

Or use DATAEXTRACTION command and on page 2 select the whole drawing or only selected objects, on page 3 select Text, on page 4 only select Text Value, on page 6 select output to Excel.

If you often do this and need a command created we can help create what you need.

Re: Text from selection to excel

pbejse — Wed, 24 Feb 2021 03:48:40 GMT

@Philip-John wrote:

I have some texts (top and bottom) in my drawing as above. I just need to have these in excel.

After selection of the top texts (point 01 - 08) it should go in first column of excel file and after selection of bottom texts (23.65 - 22.10) it should go in second column of excel file.

Try this, the program writes the string rows into on a CSV file format

(defun c:RTC ( / _sortsel _dxf fc sc csvfile opf); Row to column (defun _sortsel (sel) (mapcar 'cadr (vl-sort (mapcar '(lambda (e) (list (_dxf 10 e) (_dxf 1 e)) ) (vl-remove-if 'listp (mapcar 'cadr (ssnamex sel))) ) '(lambda (a b) (< (caar a) (caar b))) ) ) ) (defun _Dxf (d e)(cdr (assoc d (entget e)))) (if (and (princ "\nSelect text for first column") (setq fc (ssget '((0 . "TEXT")))) (princ "\nSelect text for second column") (setq sc (ssget '((0 . "TEXT")))) ) (progn (setq fc (_sortsel fc) sc (_sortsel sc)) (setq csvfile (strcat (getvar 'dwgprefix) (vl-filename-base (getvar 'dwgname)) ".csv")) (setq opf (open csvfile "w")) (foreach itm (mapcar 'list fc sc) (write-line (strcat (Car itm) "," (cadr itm)) opf) ) (close opf) (startapp "explorer" csvfile) ); progn ); if (princ) )

Command: RTC

Select text for first column

Select text for second column

HTH

Re: Text from selection to excel

Philip-John — Wed, 24 Feb 2021 17:00:27 GMT

Thanks a lot Patrick..
Your lisp worked as I wish...

Re: Text from selection to excel

Sea-Haven — Thu, 25 Feb 2021 02:30:34 GMT

Like your answer Pbe, Did years ago select as many columns as required. That way dont get "Oh need 3 columns now". XYZ. Just press enter when asked for next column. Trying to find it found a hint 2004.

Re: Text from selection to excel

pbejse — Thu, 25 Feb 2021 02:53:45 GMT

@Sea-Haven wrote:

That way dont get "Oh need 3 columns now". XYZ. Just press enter when asked for next column. .

That's a good point @Sea-Haven . Good ideas come from experience. 👍

Re: Text from selection to excel

Philip-John — Sat, 06 Mar 2021 16:50:09 GMT

Hi Patric,

Can you make it possible as sea.heaven said. (MESSAGE 6 OF 7)

Re: Text from selection to excel

pbejse — Sat, 06 Mar 2021 18:18:34 GMT

@Philip-John wrote:

Hi Patric,

Can you make it possible as sea.heaven said. (MESSAGE 6 OF 7)

Sure, hang on.

(defun c:RTC ( / _sortsel _dxf fc csvfile opf row collection _comma ); Row to column
(defun _sortsel	(sel cl)
  (mapcar 'cdr
	  (vl-sort
	    (mapcar '(lambda (e)
		       (redraw e 3)
		       (list (_dxf 10 e) (_dxf 1 e) e) )
		    (vl-remove-if 'listp (mapcar 'cadr (ssnamex sel))) )
	    '(lambda (a b) (< (caar a) (caar b)))
	  )
  )
)
(defun _Dxf (d e)(cdr (assoc d (entget e))))
(setq collection nil inc 0
      _comma (lambda (s)(strcat s ","))
 )
  
(While
  (progn
      (princ (strcat "\nSelect text for column "
		     (itoa (setq inc (1+ inc))) " "
	     )
      )
      (setq fc (ssget '((0 . "TEXT")))))
  (setq collection (Cons (_sortsel fc inc) collection))
  )

(if  (setq collection (reverse collection))
  (progn
    	(setq csvfile (strcat (getvar 'dwgprefix)
			  (vl-filename-base (getvar 'dwgname)) ".csv"))
	(setq opf (open csvfile "w"))
	(while (and (Car collection)
		    (setq data (mapcar 'car collection))
	       )
	  (setq row (apply 'strcat (mapcar '_comma (mapcar 'car data))))
	  (write-line row opf)
	  (foreach itm (mapcar 'cadr data) (redraw itm 4))
	  (setq collection (mapcar 'cdr collection))
	)
    	(close opf)
    	(startapp "explorer" csvfile)
    )
  )
  (princ)
)

HTH

Re: Text from selection to excel

Philip-John — Sun, 07 Mar 2021 09:53:41 GMT

Thanks Patric,

one more request please...

i have more than 400 text objects in my drawing and I want to make it by 25 objects in one column and its corresponding value to the second column. For first selection it is working fine. But for second selection of 25 objects it is not creating another excel file as the previous file is open.

So can you modify your script to create a new sheet in the same excel file to have the second group object of selection.

hope you understand what I requested.

Thanks in advance.

Regards,

Philip John

Re: Text from selection to excel

Sea-Haven — Mon, 08 Mar 2021 00:01:01 GMT

In fairness to pbejse to make new sheets in excel can be done but that is a fairly big add on to the method now being used, it would need to open excel and write the data directly to excel rather than to a csv file.

Perhaps a donation to pbejse would be a good idea as its not a 5 minute fix.

Re: Text from selection to excel

Philip-John — Mon, 08 Mar 2021 17:03:43 GMT

Re: Text from selection to excel

Philip-John — Mon, 08 Mar 2021 17:04:45 GMT

Hi,

I know it is not a 5 min fix and I am not in hurry. He will take his own time and will come out with a solution. For time being I shall use what he has "gifted" to me.
Moreover, I came to know from his previous answers/helps, that he is not so greedy to have some donation to help others by his God gifted knowledge and talents.

Anyway thanks for your recommendation.😊

Re: Text from selection to excel

JTBWorld — Mon, 08 Mar 2021 17:33:32 GMT

@Philip-John Are you working for free? Or are you as greedy as us that needs to pay our bills and thus charge for our time even though we once in awhile help some without charging.

Re: Text from selection to excel

Philip-John — Mon, 08 Mar 2021 18:16:48 GMT

Sorry for the inconvenience that occurred..

It was my own assumption that he is not greedy to have some donation for helping others as he never said he need payment for further modification or help.

Re: Text from selection to excel

pbejse — Tue, 09 Mar 2021 04:43:07 GMT

@Philip-John wrote:

...He will take his own time and will come out with a solution. For time being I shall use what he has "gifted" to me

Moreover, I came to know from his previous answers/helps, that he is not so greedy to have some donation to help others by his God gifted knowledge and talents.

Thank you for your kind words, and it still holds true, a request that requires a bit of time to code usually stays in my head for days.

We're doing this to give back to the community in return for Autodesk appreciating our talent.

In defense of @Sea-Haven and @JTBWorld , they're not wrong either. We also have bills to pay.

We wll definitely NOT ask for any donation, but i doesnt mean its not welcome.😊

Give your donations to : 106.117.115.116-32-97-32-80.114.97.110.107 under the name L. E. Gpull.

Now that issue is settled, Moving on to your request..

@Philip-John wrote:

Thanks Patric,

First of all, if you had not misspelled my name this could've been done days ago 😀

@Philip-John wrote:

....corresponding value to the second column. For first selection it is working fine. But for second selection of 25...

Multiple tab csv is not possible, its either we do individual csv files or one excel file.

Choose wisely @Philip-John

Re: Text from selection to excel

Philip-John — Tue, 09 Mar 2021 18:07:10 GMT

"Give your donations to : 106.117.115.116-32-97-32-80.114.97.110.107 under the name L. E. Gpull.""

I could not understand this method of sending donation. Can you please elaborate it?

Re: Text from selection to excel

pbejse — Tue, 09 Mar 2021 18:43:27 GMT

@Philip-John wrote
I could not understand this method of sending donation. Can you please elaborate it?

@Philip-John it's not real 🙂, its was meant to be a joke. When you learn enough lisp then you will understand. Dont worry about it. 😀

What i need from you is your reply to my last question regarding multiple csv file or one excel file, also you need to show me an example of a drawing and the desired result OK?

The donation thing is just for fun👊

Re: Text from selection to excel

Sea-Haven — Wed, 10 Mar 2021 04:47:41 GMT

Had a laugh a hint (chr 106).

Its just nice sometimes to make an offer when a request becomes a lot more complicated, a 6 pack of beer is not very expensive I know drank to many while coding late at night. I have never been paid for code provided on the forums commercially yes, offers have been made but its a big world out there hard to get to the other side of the world to have a couple of beers.

I am in AUS last big project was for client in CANADA no charge, 930 lines of code in one module 2 others done also will get there one day and redeem the couple of cartons of beer as payment.

Re: Text from selection to excel

Philip-John — Thu, 11 Mar 2021 15:40:09 GMT

Sorry for delayed response. There was some net problem in my home

The donation joke I enjoyed.. So funny...😀. Really I thought it's a kind of money transfer...

Hi pbejse, I prefer to have an excel file. But if it take more coding or time, then the csv file is enough for me.

Thanks for your valuable support and help..

Regards,

Philip