Author Topic: [Help] Converting a string with special characters into a series of lists  (Read 9933 times)

0 Members and 1 Guest are viewing this topic.

nivuahc

  • Guest
What I'm trying to accomplish: I'm in the process of writing a routine that would allow the user to copy a range of cells in Excel and use that data to populate a table, minus the formatting that Excel insists on putting in there, using our company standard table style instead.

What I'm having trouble with: Well, I looked to Doslib for the dos_clipboard function to return the contents of the clipboard but I'm having difficulty breaking the returned string into an appropriate list that I can work my way through to populate the cells of the Autocad table. For example, I've got a sample spreadsheet with a 5x5 table of junk, which I've selected and copied to the windows clipboard. When I do this:

Code: [Select]
Command: (setq test (dos_clipboard))I get this:
Code: [Select]
"Column 1\tColumn 2\tColumn 3\tColumn 4\tColumn 5\r\n1\t5\t1\t5\t1\r\n2\t6\t2\t6\t2\r\n3\t7\t3\t7\t3\r\n4\t8\t4\t8\t4\r\n"
As you can see, the cells in my selection are separated by tabs (\t) and the rows of my selection are separated by a carriage return (\r\n). If I now do this:
Code: [Select]
Command: (setq test2 (dos_strtokens test "\t" t))I get a single list which still contains the carriage returns like this:
Code: [Select]
("Column 1" "Column 2" "Column 3" "Column 4" "Column 5\r\n1" "5" "1" "5" "1\r\n2" "6" "2" "6" "2\r\n3" "7" "3" "7" "3\r\n4" "8" "4" "8" "4\r\n")If I try to take out the carriage returns along with the tabs, like this:
Code: [Select]
Command: (setq test3 (dos_strtokens test "\t\r\n" t))I get this:
Code: [Select]
("Column 1" "Column 2" "Column 3" "Column 4" "Column 5" "1" "5" "1" "5" "1" "2" "6" "2" "6" "2" "3" "7" "3" "7" "3" "4" "8" "4" "8" "4")What I actually need, though, is something like this:
Code: [Select]
(
("Column 1" "Column 2" "Column 3" "Column 4" "Column 5")
("1" "5" "1" "5" "1")
("2" "6" "2" "6" "2")
("3" "7" "3" "7" "3")
("4" "8" "4" "8" "4")
)

Maybe it's too early in the morning, maybe I haven't had enough coffee, but I'm hitting a mental road-block with this and I'm looking for advice, a nudge in the right direction, or alternative ideas.

Lee Mac

  • Seagull
  • Posts: 12906
  • London, England
Using Str-Break code I already had:

Code: [Select]
(defun test (str / Str-Break)

  (defun Str-Break (str del / pos lst)
    (while (setq pos  (vl-string-search del str))
      (setq lst (cons (substr str 1 pos) lst)
            str (substr str (+ pos 1 (strlen del)))))
    (reverse (cons str lst)))

  (mapcar
    (function
      (lambda (x)
        (Str-Break x "\t"))) (Str-Break str "\r\n")))


Code: [Select]
(test "Column 1\tColumn 2\tColumn 3\tColumn 4\tColumn 5\r\n1\t5\t1\t5\t1\r\n2\t6\t2\t6\t2\r\n3\t7\t3\t7\t3\r\n4\t8\t4\t8\t4\r\n")
(("Column 1" "Column 2" "Column 3" "Column 4" "Column 5") ("1" "5" "1" "5" "1") ("2" "6" "2" "6" "2") ("3" "7" "3" "7" "3") ("4" "8" "4" "8" "4") (""))

Maybe needs a tweak to forget the empty string  :-)

PS> I haven't got DosLib ;)
« Last Edit: January 06, 2010, 08:09:53 AM by Lee Mac »

Kerry

  • Mesozoic relic
  • Seagull
  • Posts: 11654
  • class keyThumper<T>:ILazy<T>
perhaps break the string on \r first
then break each resulting list on \t


added:  :-D sortof just like Lee posted
kdub, kdub_nz in other timelines.
Perfection is not optional.
Everything will work just as you expect it to, unless your expectations are incorrect.
Discipline: None at all.

nivuahc

  • Guest
You guys rock :D

nivuahc

  • Guest
PS> I haven't got DosLib ;)

Is there an easier way to get that data from the windows clipboard?  :?

Lee Mac

  • Seagull
  • Posts: 12906
  • London, England
PS> I haven't got DosLib ;)

Is there an easier way to get that data from the windows clipboard?  :?

Probably not, from what I remember you need to exploit the InternetExplorer App   :|

nivuahc

  • Guest
Maybe needs a tweak to forget the empty string  :-)

For my purposes:

Code: [Select]
;; With assistance from Lee Mac and Kerry Brown @ theswamp.org
;; the following is © 2009 by Lee Mac
(defun clip2list (str / Str-Break)
(defun Str-Break (str del / pos lst)
(while
(setq pos (vl-string-search del str))
(setq lst (cons (substr str 1 pos) lst)
str (substr str (+ pos 1 (strlen del)))
) ;_ end setq
) ;_ end while
(reverse (cons str lst))
) ;_ end defun Str-Break

(mapcar
(function
(lambda (x)
(Str-Break x "\t")
) ;_ end lambda
) ;_ end function
(Str-Break str "\r\n")
) ;_ end mapcar
) ;_ end defun


(setq TableData (clip2list (dos_clipboard))
Rows (1- (length TableData))
Columns (length (car TableData))
)

Code: [Select]
Command: !TableData
(("Column 1" "Column 2" "Column 3" "Column 4" "Column 5") ("1" "5" "1" "5" "1")
("2" "6" "2" "6" "2") ("3" "7" "3" "7" "3") ("4" "8" "4" "8" "4") (""))

Command: !Rows
5

Command: !Columns
5

So, basically, my solution to the empty string at the end is to just ignore it  :lol:


//sidenote: I find that the older I get, the more I have to put the ;_ end comments at the end of everything, just so I can keep track of where the heck I am...  :|

Joe Burke

  • Guest
Just a thought using 2008.

You can paste a range of cells from Excel to ACAD and automatically create a table using the PasteSpecial command. Select the AutoCAD Entities option within the PasteSpecial dialog.

Lee Mac

  • Seagull
  • Posts: 12906
  • London, England
So, basically, my solution to the empty string at the end is to just ignore it  :lol:

 :-) Try this:

Code: [Select]
;; With assistance from Lee Mac and Kerry Brown @ theswamp.org
;; the following is © 2009 by Lee Mac
(defun clip2list (str / Str-Break)
(defun Str-Break (str del / pos lst)
(while
(setq pos (vl-string-search del str))
(setq lst (cons (substr str 1 pos) lst)
str (substr str (+ pos 1 (strlen del)))
) ;_ end setq
) ;_ end while
[color=red](reverse lst)[/color]
) ;_ end defun Str-Break

(mapcar
(function
(lambda (x)
(Str-Break x "\t")
) ;_ end lambda
) ;_ end function
(Str-Break str "\r\n")
) ;_ end mapcar
) ;_ end defun

EDIT: Scrap that - it misses the last element of the rest - I thought it was too easy a solution...

EDIT EDIT: I hate this kind of workaround, but I can't immediately see an elegant way to do it...

Code: [Select]
;; With assistance from Lee Mac and Kerry Brown @ theswamp.org
;; the following is © 2009 by Lee Mac
(defun clip2list (str / Str-Break)
(defun Str-Break (str del [color=red]flag [/color]/ pos lst)
(while
(setq pos (vl-string-search del str))
(setq lst (cons (substr str 1 pos) lst)
str (substr str (+ pos 1 (strlen del)))
) ;_ end setq
) ;_ end while
(reverse [color=red](if flag (cons str lst) lst)[/color])
) ;_ end defun Str-Break

(mapcar
(function
(lambda (x)
(Str-Break x "\t" [color=red]t[/color])
) ;_ end lambda
) ;_ end function
(Str-Break str "\r\n" [color=red]nil[/color])
) ;_ end mapcar
) ;_ end defun


//sidenote: I find that the older I get, the more I have to put the ;_ end comments at the end of everything, just so I can keep track of where the heck I am...  :|

Hehe, I suppose its what you get used to - I used to use them also, but now I find them distracting  :-)
« Last Edit: January 06, 2010, 08:42:55 AM by Lee Mac »

xianaihua

  • Guest
Code: [Select]
(defun _GetClipBoardText( / htmlfile result )

    ;;  Attribution: Reformatted version of
    ;;  post by Patrick_35 at theswamp.org.
    ;;
    ;;  See http://tinyurl.com/2ngf4r.

    (setq result
        (vlax-invoke
            (vlax-get
                (vlax-get
                    (setq htmlfile (vlax-create-object "htmlfile"))
                   'ParentWindow
                )
               'ClipBoardData
            )
           'GetData
            "Text"
        )
    )

    (vlax-release-object htmlfile)

    result

)

nivuahc

  • Guest
Re: [Help] Converting a string with special characters into a series of lists
« Reply #10 on: January 06, 2010, 09:34:49 AM »
Code: [Select]
(defun _GetClipBoardText( / htmlfile result )

    ;;  Attribution: Reformatted version of
    ;;  post by Patrick_35 at theswamp.org.
    ;;
    ;;  See http://tinyurl.com/2ngf4r.

    (setq result
        (vlax-invoke
            (vlax-get
                (vlax-get
                    (setq htmlfile (vlax-create-object "htmlfile"))
                   'ParentWindow
                )
               'ClipBoardData
            )
           'GetData
            "Text"
        )
    )

    (vlax-release-object htmlfile)

    result

)

That's purdy :)

nivuahc

  • Guest
Re: [Help] Converting a string with special characters into a series of lists
« Reply #11 on: January 06, 2010, 09:40:24 AM »
Here's where I am, if anyone wants to give it a whirl:

Code: [Select]
(defun c:xl2tbl ( / column columns row rows *doc* TableData table inspt)
;; Based, originally, on LotTable.lsp by Jeff Mischler @ theswamp.org
;; http://www.theswamp.org/index.php?topic=14458.0
;;
;; This routine inserts a table in AutoCAD and populates it with the data
;; that a user copied to the windows clipboard from Excel.
;;
;; With assistance from Lee Mac, xianaihua, and Kerry Brown @ theswamp.org
;;

(setq *doc* (vla-get-activedocument (vlax-get-acad-object)))

;; the following is © 2007 by Jeff Mishler
(defun get_space ()
(if (= 1 (vla-get-activespace *doc*))
(vla-get-modelspace *doc*) ;modelspace
(if (= (vla-get-mspace *doc*) :vlax-true)
(vla-get-modelspace *doc*) ;modelspace through VPort
(vla-get-paperspace *doc*) ;paperspace
) ;_ end if
) ;_ end if
) ;_ end defun

(defun _GetClipBoardText( / htmlfile result )
    ;;  Attribution: Reformatted version of
    ;;  post by Patrick_35 at theswamp.org.
    ;;
    ;;  See http://tinyurl.com/2ngf4r.
    (setq result
        (vlax-invoke
            (vlax-get
                (vlax-get
                    (setq htmlfile (vlax-create-object "htmlfile"))
                   'ParentWindow
                )
               'ClipBoardData
            )
           'GetData
            "Text"
        )
    )
    (vlax-release-object htmlfile)
    result
)

;; the following is (awesome, and) © 2009 by Lee Mac
(defun clip2list (str / Str-Break)
(defun Str-Break (str del / pos lst)
(while
(setq pos (vl-string-search del str))
(setq lst (cons (substr str 1 pos) lst)
str (substr str (+ pos 1 (strlen del)))
) ;_ end setq
) ;_ end while
(reverse (cons str lst))
) ;_ end defun Str-Break

(mapcar
(function
(lambda (x)
(Str-Break x "\t")
) ;_ end lambda
) ;_ end function
(Str-Break str "\r\n")
) ;_ end mapcar
) ;_ end defun

;; A lot of what follows is hacked/Frankenstiened from the LotTable routine posted by Jeff Mishler
(if
(and
(setq TableData (clip2list (_GetClipBoardText))
Rows (1- (length TableData))
Columns (length (car TableData))
) ;_ end setq
) ;_ end and
    (progn
;; I have a routine that creates our company standard table style if it doesn't already exist, which runs right here
;; and I also set that table style to current using (setvar 'CTABLESTYLE "XXX"). The table will be created using the
;; current table style so I'll leave it to you to decide how you want it to look
(setq inspt (getpoint "\nSelect insertion point for table: "))
(setq table (vlax-invoke (get_space) 'addtable inspt (1+ rows) columns 0.06 2))
(vla-put-RegenerateTableSuppressed table :vlax-true) ;;don't regen table with every addition/change
(setq row 0)
(repeat Rows
(setq column 0)
(repeat Columns
(vla-settext table (1+ row) column (nth column (nth row TableData)))
(setq column (1+ column))
)
(setq row (1+ row))
) ;_ end repeat
(vla-put-RegenerateTableSuppressed table :vlax-false);;Now we can regen the table
    ) ;_ end progn
    (princ "\nTable not created due to errors or cancellation.....")
) ;_ end if
(princ)
) ;_ end defun

CAB

  • Global Moderator
  • Seagull
  • Posts: 10401
Re: [Help] Converting a string with special characters into a series of lists
« Reply #12 on: January 06, 2010, 10:42:30 AM »
Some stuff:

Code: [Select]
;;  MP
;;  http://www.theswamp.org/index.php?topic=21764.msg263322#msg263322
(defun _SetClipBoardText ( text / htmlfile result )

    ;;  Caller's sole responsibility is to pass a
    ;;  text string. Anything else? Pie in face.

    ;;  Attribution: Reformatted version of
    ;;  post by XShrimp at theswamp.org.
    ;;
    ;;  See http://tinyurl.com/2ngf4r.

    (setq result
        (vlax-invoke
            (vlax-get
                (vlax-get (setq htmlfile (vlax-create-object "htmlfile")) 'ParentWindow)
               'ClipBoardData
            )
           'SetData  "Text"  text)
    )
    (vlax-release-object htmlfile)
    text
)

;;  MP
(defun _GetClipBoardText( / htmlfile result )

    ;;  Attribution: Reformatted version of
    ;;  post by Patrick_35 at theswamp.org.
    ;;
    ;;  See http://tinyurl.com/2ngf4r.

    (setq result
        (vlax-invoke
            (vlax-get
                (vlax-get (setq htmlfile (vlax-create-object "htmlfile")) 'ParentWindow)
               'ClipBoardData
            )
           'GetData "Text")
    )
    (vlax-release-object htmlfile)
    result
)



Code: [Select]
;;  Patrick
;; 
(defun GetClipText(/ html result)
  (setq html (vlax-create-object "htmlfile")
result (vlax-invoke (vlax-get (vlax-get html 'ParentWindow) 'ClipBoardData) 'GetData "Text")
  )
  (vlax-release-object html)
  result
)
I've reached the age where the happy hour is a nap. (°¿°)
Windows 10 core i7 4790k 4Ghz 32GB GTX 970
Please support this web site.

nivuahc

  • Guest
Re: [Help] Converting a string with special characters into a series of lists
« Reply #13 on: January 06, 2010, 12:20:31 PM »
The only issue I have, now, is trying to get that damned Title row to not show up... I mean, I tried setting vla-put-titlesuppressed to every value I can think of and it doesn't seem to make a bit of difference.  :ugly:

Any ideas?  :?

nivuahc

  • Guest
Re: [Help] Converting a string with special characters into a series of lists
« Reply #14 on: January 06, 2010, 12:22:55 PM »
Just a thought using 2008.

You can paste a range of cells from Excel to ACAD and automatically create a table using the PasteSpecial command. Select the AutoCAD Entities option within the PasteSpecial dialog.

Right. Only some funky formatting comes along with it, no matter what you do (at least as far as I can tell).