Visual LISP, AutoLISP and General Customization
cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 

Text from selection to excel

44 REPLIES 44
SOLVED
Reply
Message 1 of 45
Philip-John
6151 Views, 44 Replies

Text from selection to excel

txt2excel.png

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.

 

44 REPLIES 44
Message 2 of 45
pbejse
in reply to: Philip-John


@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.

 

toexcel.png

 

Message 3 of 45
JTBWorld
in reply to: Philip-John

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. 


Jimmy Bergmark
JTB World - Software development and consulting for CAD and license usage reports
https://jtbworld.com

Message 4 of 45
pbejse
in reply to: Philip-John


@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

 

Message 5 of 45
Philip-John
in reply to: pbejse

Thanks a lot Patrick..
Your lisp worked as I wish...
Message 6 of 45
Sea-Haven
in reply to: pbejse

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.

Message 7 of 45
pbejse
in reply to: Sea-Haven


@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. 👍

 

Message 8 of 45
Philip-John
in reply to: pbejse

Hi Patric,

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

Message 9 of 45
pbejse
in reply to: Philip-John


@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

 

Message 10 of 45
Philip-John
in reply to: pbejse

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

 

Message 11 of 45
Sea-Haven
in reply to: Philip-John

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.

Message 12 of 45
Philip-John
in reply to: Philip-John

--

Message 13 of 45
Philip-John
in reply to: Sea-Haven

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.😊

Message 14 of 45
JTBWorld
in reply to: Philip-John

@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. 


Jimmy Bergmark
JTB World - Software development and consulting for CAD and license usage reports
https://jtbworld.com

Message 15 of 45
Philip-John
in reply to: JTBWorld

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.

Message 16 of 45
pbejse
in reply to: Philip-John

@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 

 

Message 17 of 45
Philip-John
in reply to: pbejse

"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?

Message 18 of 45
pbejse
in reply to: Philip-John


@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👊

 

 

Message 19 of 45
Sea-Haven
in reply to: Philip-John

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.

 

 

 

 

Message 20 of 45
Philip-John
in reply to: pbejse

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

Can't find what you're looking for? Ask the community or share your knowledge.

Post to forums  

Autodesk Customer Advisory Groups