Exporting Text To Excel

[BIM-RH]

Hi All, I have a problem with data extraction. Please check the snap below, I would like to macth yellow texts with white ones. I mean, I want to see "S406" & "3738" side by side or one under the other in excel. I tried data extraction command but i could not figure out, which rule is used by Autocad while exporting them to excel. Is there any way to do it without writing lisp? or should i try to write lisp? Thanks in advance. Onur

[cadffm]

Without testing: The order of creation.
If you write out per objectselection, perhaps the selectionorder.

We can not check these things from simple screenshot, but if these texts are simple text objects, you can export the insertion point and reorder the result in excel by coordinates.
Perfect is when you create a block with attributes for this goal, then you get one riw per insert and for each attribut one column.

[Anonymous]

Try this:

(defun c:test (/ sset itm num fn fh ent stv )
  (setq sset (ssget '((0 . "TEXT"))))
  (if sset
    (progn
      (setq itm 0)
      (setq num (sslength sset))
      (setq fn (getfiled "Export File" "" "csv" 1))
      (if (/= fn nil)
        (progn
          (setq fh (open fn "w"))
          (while (< itm num)
            (setq hnd (ssname sset itm))
            (setq ent (entget hnd))
            (setq stv (cdr (assoc 1 ent)))
            (princ (strcat stv "\n") fh)
            (setq itm (1+ itm))
          )
          (close fh)
        )
      )
    )
  )
  (setq sset nil)
  (princ)
)

Júnior Nogueira.

Por favor,  Aceitar como Solução se meu post te ajudar.

Please Accept as Solution if my post helps you.

[BIM-RH]

Dear Nogueira,

Thanks for your code but the result is same with changing "text" to "Mtext" and export them by using data extraction. Please check the snap. 

Thanks & Regards.

Onur

[roland.r71]

One of the reasons i never use MTEXT unless i realy have a big piece of (multilined) text.

Never ever use it for "text-strings" like these.

The last thing i want is people formatting the text, as has been done here, overruling the textstyle.

Since that's exactly what you have here:

\W0.7000 = Set text width to 0.7

\W0.7360 = Set text width to 0.736

\H30.0000 = Set text height to 30

\Ftxt = Set Text Font to txt (.shx)

etc.

Especially the various, slightly deviating width settings is something i would not accept, from myself or any colleague. ...but ofcourse you can do as you please. Just mind the consequences (which is: extra formatting info with your MTEXT content)

If you used TEXT, not MTEXT and applied a textstyle (with width set to 0.7 if that's what you want), you would not have this problem right now.

Looking at the image & export; it appears to me you used MTEXT where a block with attributes would fit 10x better. You may have your reasons, but you are making it hard on yourself this way.

In the end, you do not want to extract the data, but only a part of it.

For that you will need to write a lisp.

One that will take the MTEXT and remove all the formatting, before exporting. (you can probably find a lisp right here on the forum to do just that)

BTW: I'm not trying to bash you or something, just saying:

Had you used regular TEXT or, better yet, a block with attributes (where you can still apply different formatting with each attribute), you wouldn't be having this problem.

[ВeekeeCZ]

It depends... but I would try what @cadffm suggests. Keep TEXTs, export values, X and Y coords.

Then try some clever formula to combine X and Y coords into one number. Maybe x*1000+y*1000. And sort them....

[roland.r71]

---

@ВeekeeCZ wrote:

It depends... but I would try what @cadffm suggests. Keep TEXTs, export values, X and Y coords.

Then try some clever formula to combine X and Y coords into one number. Maybe x*1000+y*1000. And sort them....

---

The order is not the problem.

All the text you see in the first image, is combined within 1 MTEXT (as you can see with the 2nd image (reply #4), which is the dataextraction export.)

So, the text strings he wants, are inside one and the same MTEXT entity and will therefor always be "together".

The problem is the MTEXT formatting, which he doesn't want exported. Just the (plain) text.

Afaik, that can only be done by writing a lisp to strip the formatting and then export the plain text to a .csv file, instead of using dataextraction. (which exports the entities value. For MTEXT that includes all the formatting too)

[ВeekeeCZ]

Ohh, I see. I don't know, I tend to think, that it is a lack of intelligence that the OPs are posting just a screenshots and not the actual drawings... or it's just lack of experience? Maybe if the site told them "CONSIDER POSTING DWG" when they are posting the original request? I think it worse to add this note in there, because it's happening so often...

To the OP. Yes, that's only solvable using a lisp routine, but there are some good and free ones already written, so no need to write a new code. HERE is probably the best one.

[roland.r71]

---

@ВeekeeCZ wrote:

Ohh, I don't know, I tend to think, that it is a lack of intelligence that the OPs are posting just a screenshots and not the actual drawings... or it's just lack of experience? Maybe if the site told them "CONSIDER POSTING DWG" when they are posting the original request? I think it worse to add this note in there, because it's happening so often...

To the OP. Yes, that's only solvable using a lisp routine, but there are some good and free ones already written, so no need to write a new code. HERE is probably the best one.

---

To that i can only agree.

With a DWG it would have been clear straight away he (or whomever created the drawing) used 1 MTEXT for all the text, instead of separate TEXT (or attributes) as one might expect. (it took the second image with the data export for me to understand the real problem)

[ВeekeeCZ]

Or a quick mod of @Anonymous's code should make the trick. (for AutoCAD 2012+)

(defun c:test (/ sset itm num fn fh ent stv )
  (setq sset (ssget '((0 . "MTEXT"))))
  (if sset
    (progn
      (setq itm 0)
      (setq num (sslength sset))
      (setq fn (getfiled "Export File" "" "csv" 1))
      (if (/= fn nil)
        (progn
          (setq fh (open fn "w"))
          (while (< itm num)
            (setq hnd (ssname sset itm))
;            (setq ent (entget hnd))
;            (setq stv (cdr (assoc 1 ent)))
            (setq stv (getpropertyvalue hnd "Text"))
            (princ (strcat stv "\n") fh)
            (setq itm (1+ itm))
          )
          (close fh)
        )
      )
    )
  )
  (setq sset nil)
  (princ)
)

[Anonymous]

One of the things you can do is a double sort on a list so you would have X Y & text so if you sort on X then Y you will end up with some sort of text order. A little hint is to get mtext get a bounding box remove the additional coding explode the mtext then add to selection,  using the bounding box for a multi line selection. Then write a csv etc.