Pasting tabs into Excel

classic Classic list List threaded Threaded
15 messages Options
Reply | Threaded
Open this post in threaded view
|

Pasting tabs into Excel

Mark Wieder via use-livecode
Someone asked me this and I don't have an answer:

Before LC 8, you could put tabbed strings into the clipboard, paste into
Excel or Google Sheets, and each tabbed item would go into a separate
cell. After LC 8, the tabbed items go into a single cell as one string.

How can we make Excel recognize the tabs?

For example:

     put 1 &tab& 2 &tab& 3 into temp
     set the clipboardData["text"] to temp

--
Jacqueline Landman Gay         |     [hidden email]
HyperActive Software           |     http://www.hyperactivesw.com

_______________________________________________
use-livecode mailing list
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
Reply | Threaded
Open this post in threaded view
|

Re: Pasting tabs into Excel

Mark Wieder via use-livecode
Jacque.

As if you needed this, confirmed here.

What on earth can v.8 be doing to the tab character???

Craig



--
Sent from: http://runtime-revolution.278305.n4.nabble.com/Revolution-User-f278306.html

_______________________________________________
use-livecode mailing list
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
Reply | Threaded
Open this post in threaded view
|

Re: Pasting tabs into Excel

Mark Wieder via use-livecode
It's strange. If you paste into excel, then select the cell and use the
text to columns tool, using Space as delimiter, poof. Columns.  Also, in
libreoffice, paste special can be used to choose "unformatted text" and
that works also.  Surely there is a way to pop a simple tab delimited
string into the clipboard and have it just work..

On Fri, Feb 2, 2018 at 10:22 PM, dunbarx via use-livecode <
[hidden email]> wrote:

> Jacque.
>
> As if you needed this, confirmed here.
>
> What on earth can v.8 be doing to the tab character???
>
> Craig
>
>
>
> --
> Sent from: http://runtime-revolution.278305.n4.nabble.com/
> Revolution-User-f278306.html
>
> _______________________________________________
> use-livecode mailing list
> [hidden email]
> Please visit this url to subscribe, unsubscribe and manage your
> subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-livecode
>
_______________________________________________
use-livecode mailing list
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
Reply | Threaded
Open this post in threaded view
|

Re: Pasting tabs into Excel

Mark Wieder via use-livecode
In reply to this post by Mark Wieder via use-livecode
Hi,

i cannot reproduce that here on Mac OS X and Excel 2016 (V 15.30).
I executed your sample code in the messagebox and was able to paste successfully the clipboard to Excel.
I even tried with alpha numeric values.

But what i noticed is the following:
When i just click on a cell, so that it is selected without showing the blinking write cursor in the cell, then pasting is working w/o problems.

When i double click a cell, so that it is selected and the blinking write cursor is shown, then i cannot paste it correctly.

Tried with LC 8.19RC2 and LC9DP11.

Will try on Windows later this day.


Regards,
Matthias


> Am 03.02.2018 um 05:19 schrieb J. Landman Gay via use-livecode <[hidden email] <mailto:[hidden email]>>:
>
> Someone asked me this and I don't have an answer:
>
> Before LC 8, you could put tabbed strings into the clipboard, paste into Excel or Google Sheets, and each tabbed item would go into a separate cell. After LC 8, the tabbed items go into a single cell as one string.
>
> How can we make Excel recognize the tabs?
>
> For example:
>
>    put 1 &tab& 2 &tab& 3 into temp
>    set the clipboardData["text"] to temp
>
> --
> Jacqueline Landman Gay         |     [hidden email] <mailto:[hidden email]>
> HyperActive Software           |     http://www.hyperactivesw.com <http://www.hyperactivesw.com/>
>
> _______________________________________________
> use-livecode mailing list
> [hidden email] <mailto:[hidden email]>
> Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-livecode

_______________________________________________
use-livecode mailing list
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
Reply | Threaded
Open this post in threaded view
|

Re: Pasting tabs into Excel

Mark Wieder via use-livecode
In reply to this post by Mark Wieder via use-livecode
Possibly this is not LC but an Excel update that changed
Excel's default delimiter from tab to comma.
Does comma as delimiter work?

_______________________________________________
use-livecode mailing list
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
Reply | Threaded
Open this post in threaded view
|

Re: Pasting tabs into Excel

Mark Wieder via use-livecode
I got the same results as Matthias on 6, 7, and 9 (Mac Sierra)
On Sat, Feb 3, 2018 at 8:56 AM hh via use-livecode <
[hidden email]> wrote:

> Possibly this is not LC but an Excel update that changed
> Excel's default delimiter from tab to comma.
> Does comma as delimiter work?
>
> _______________________________________________
> use-livecode mailing list
> [hidden email]
> Please visit this url to subscribe, unsubscribe and manage your
> subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-livecode
>
_______________________________________________
use-livecode mailing list
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
Reply | Threaded
Open this post in threaded view
|

Re: Pasting tabs into Excel

Mark Wieder via use-livecode
I have no access to Excel at the moment, but I cannot believe that the
default field delimiter could possibly have changed from tab. May just as
well change the record delimiter from return

 It would break just about everything.

Craig



--
Sent from: http://runtime-revolution.278305.n4.nabble.com/Revolution-User-f278306.html

_______________________________________________
use-livecode mailing list
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
Reply | Threaded
Open this post in threaded view
|

Re: Pasting tabs into Excel

Mark Wieder via use-livecode
Like Mike, I found that the text to columns tool, using space as
delimiter, will separate the entry into columns. That seems to imply that
somewhere the tabs are changed to spaces. Also, pasting into a text editor
does show the tabs, and copying from there preserves them when pasting into
Excel, and they act normally and span columns.

I also tried fullclipboardData and rawclipboardData with no luck. There
seems to be something different about in LC's implementation. I found two
bugs that might be related:

http://quality.livecode.com/show_bug.cgi?id=19206
http://quality.livecode.com/show_bug.cgi?id=20336

--
Jacqueline Landman Gay         |     [hidden email]
HyperActive Software           |     http://www.hyperactivesw.com



On February 3, 2018 10:42:02 AM dunbarx via use-livecode
<[hidden email]> wrote:

> I have no access to Excel at the moment, but I cannot believe that the
> default field delimiter could possibly have changed from tab. May just as
> well change the record delimiter from return
>
>  It would break just about everything.
>
> Craig
>
>
>
> --
> Sent from:
> http://runtime-revolution.278305.n4.nabble.com/Revolution-User-f278306.html
>
> _______________________________________________
> use-livecode mailing list
> [hidden email]
> Please visit this url to subscribe, unsubscribe and manage your
> subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-livecode



_______________________________________________
use-livecode mailing list
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
Reply | Threaded
Open this post in threaded view
|

Re: Pasting tabs into Excel

Mark Wieder via use-livecode
In reply to this post by Mark Wieder via use-livecode
I don’t think it changed. But Excel currently supports tab and return
characters within cells. If you paste text with tabs/returns into the
formula bar area it will all go into that cell.  I'm using Excel 16.9
(180116)

Try this function (LC 8/9):

*on* stripClipboard
   *local* tData
   *lock* the clipboard
   *put* the rawClipboardData["public.utf8-plain-text"] into tData
   *set* the rawClipboardData to empty
   *set* the rawClipboardData["public.utf8-plain-text"] to tData
   *unlock* the clipboard
*end* stripClipboard

This will ensure the only thing on the clipboard is the text format (no RTF
and no HTML).  Even when I use this function after putting the text on the
clipboard, I get the same results.  And I get the same in LC 6 & 7 too.

You mentioned raw clipboard, what did you try there?

Oddly, when I use the text to columns tool, it recognizes the tabs (when I
pasted the text into a single cell).


More info on those 2 bugs:

The first bug is about LC placing too much on the system clipboard.  Prior
to 8, LC only put text+RTF on the clipboard.  With 8, HTML was added - LC
ran everything through its internal conversion to LC styled text and put LC
HTMLtext on the system clipboard as HTML.  The bug fix on the first on
stops that conversion so that when plain text is placed on the clipboard,
only plain text is put on the system clipboard (and it no longer puts
anything styled).

The second bug is tightly related.  It is actually the same process that is
causing the problem, but the solution will be slightly different.  There
isn't a PR on this one yet.  It would be easy to disable LC HTMLtext going
to the clipboard unless specifically requested (which would be more like
pre LC-8), but fixing the actual HTMLtext is a little more complicated.
I've written some LCS code that can convert HTMLtext into a format that
works (mostly) with copy/paste to other apps, but have not figured out how
to put that into the message path to work seamlessly (would need to be
called from the engine copy code).  Ideally, something like:
set the fullclipboarddata["htmltext"] to the htmltext of field "test"
would put something on the clipboard that would not render with all of the
extra lines and missing spaces when pasted to other apps.

Thanks,
Brian
_______________________________________________
use-livecode mailing list
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
Reply | Threaded
Open this post in threaded view
|

Re: Pasting tabs into Excel

Mark Wieder via use-livecode
On 2/3/18 12:23 PM, Brian Milby via use-livecode wrote:

> Try this function (LC 8/9):
>
> *on*  stripClipboard
>     *local*  tData
>     *lock*  the clipboard
>     *put*  the rawClipboardData["public.utf8-plain-text"] into tData
>     *set*  the rawClipboardData to empty
>     *set*  the rawClipboardData["public.utf8-plain-text"] to tData
>     *unlock*  the clipboard
> *end*  stripClipboard
>
> This will ensure the only thing on the clipboard is the text format (no RTF
> and no HTML).  Even when I use this function after putting the text on the
> clipboard, I get the same results.  And I get the same in LC 6 & 7 too.
>
> You mentioned raw clipboard, what did you try there?

Your handler works. :) I haven't used rawClipboardData much and I don't
have that particular experiment any more, but I think I just used the
example verbatim from the dictionary.

--
Jacqueline Landman Gay         |     [hidden email]
HyperActive Software           |     http://www.hyperactivesw.com

_______________________________________________
use-livecode mailing list
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
Reply | Threaded
Open this post in threaded view
|

Re: Pasting tabs into Excel

Mark Wieder via use-livecode
In reply to this post by Mark Wieder via use-livecode
On 2/3/18 1:52 AM, Matthias Rebbe via use-livecode wrote:
> But what i noticed is the following:
> When i just click on a cell, so that it is selected without showing the blinking write cursor in the cell, then pasting is working w/o problems.
>
> When i double click a cell, so that it is selected and the blinking write cursor is shown, then i cannot paste it correctly.

Interesting. I don't have Excel so I've been testing in Google Sheets.
This trick doesn't work there. :(

--
Jacqueline Landman Gay         |     [hidden email]
HyperActive Software           |     http://www.hyperactivesw.com

_______________________________________________
use-livecode mailing list
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
Reply | Threaded
Open this post in threaded view
|

Re: Pasting tabs into Excel

Mark Wieder via use-livecode
In reply to this post by Mark Wieder via use-livecode
Brian Milby wrote:

 > More info on those 2 bugs:
 >
 > The first bug is about LC placing too much on the system clipboard.
 >  Prior to 8, LC only put text+RTF on the clipboard.  With 8, HTML was
 > added - LC ran everything through its internal conversion to LC styled
 > text and put LC HTMLtext on the system clipboard as HTML.  The bug fix
 > on the first on stops that conversion so that when plain text is
 > placed on the clipboard, only plain text is put on the system
 > clipboard (and it no longer puts anything styled).
 >
 > The second bug is tightly related.  It is actually the same process
 > that is causing the problem, but the solution will be slightly
 > different.  There isn't a PR on this one yet.  It would be easy to
 > disable LC HTMLtext going to the clipboard unless specifically
 > requested (which would be more like pre LC-8), but fixing the actual
 > HTMLtext is a little more complicated.

I think I missed something earlier in this thread:  what are those two
bug report IDs?

--
  Richard Gaskin
  Fourth World Systems
  Software Design and Development for the Desktop, Mobile, and the Web
  ____________________________________________________________________
  [hidden email]                http://www.FourthWorld.com

_______________________________________________
use-livecode mailing list
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
Reply | Threaded
Open this post in threaded view
|

Re: Pasting tabs into Excel

Mark Wieder via use-livecode
http://quality.livecode.com/show_bug.cgi?id=19206
http://quality.livecode.com/show_bug.cgi?id=20336
On Sat, Feb 3, 2018 at 4:28 PM Richard Gaskin via use-livecode <
[hidden email]> wrote:

> Brian Milby wrote:
>
>  > More info on those 2 bugs:
>  >
>  > The first bug is about LC placing too much on the system clipboard.
>  >  Prior to 8, LC only put text+RTF on the clipboard.  With 8, HTML was
>  > added - LC ran everything through its internal conversion to LC styled
>  > text and put LC HTMLtext on the system clipboard as HTML.  The bug fix
>  > on the first on stops that conversion so that when plain text is
>  > placed on the clipboard, only plain text is put on the system
>  > clipboard (and it no longer puts anything styled).
>  >
>  > The second bug is tightly related.  It is actually the same process
>  > that is causing the problem, but the solution will be slightly
>  > different.  There isn't a PR on this one yet.  It would be easy to
>  > disable LC HTMLtext going to the clipboard unless specifically
>  > requested (which would be more like pre LC-8), but fixing the actual
>  > HTMLtext is a little more complicated.
>
> I think I missed something earlier in this thread:  what are those two
> bug report IDs?
>
> --
>   Richard Gaskin
>   Fourth World Systems
>   Software Design and Development for the Desktop, Mobile, and the Web
>   ____________________________________________________________________
>   [hidden email]                http://www.FourthWorld.com
>
> _______________________________________________
> use-livecode mailing list
> [hidden email]
> Please visit this url to subscribe, unsubscribe and manage your
> subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-livecode
>
_______________________________________________
use-livecode mailing list
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
Reply | Threaded
Open this post in threaded view
|

Re: Pasting tabs into Excel

Mark Wieder via use-livecode
PR 6293 should correct this issue (19206). When plain text is copied to the
clipboard, styled text will not be added.
On Sat, Feb 3, 2018 at 4:32 PM Brian Milby <[hidden email]> wrote:

> http://quality.livecode.com/show_bug.cgi?id=19206
> http://quality.livecode.com/show_bug.cgi?id=20336
> On Sat, Feb 3, 2018 at 4:28 PM Richard Gaskin via use-livecode <
> [hidden email]> wrote:
>
>> Brian Milby wrote:
>>
>>  > More info on those 2 bugs:
>>  >
>>  > The first bug is about LC placing too much on the system clipboard.
>>  >  Prior to 8, LC only put text+RTF on the clipboard.  With 8, HTML was
>>  > added - LC ran everything through its internal conversion to LC styled
>>  > text and put LC HTMLtext on the system clipboard as HTML.  The bug fix
>>  > on the first on stops that conversion so that when plain text is
>>  > placed on the clipboard, only plain text is put on the system
>>  > clipboard (and it no longer puts anything styled).
>>  >
>>  > The second bug is tightly related.  It is actually the same process
>>  > that is causing the problem, but the solution will be slightly
>>  > different.  There isn't a PR on this one yet.  It would be easy to
>>  > disable LC HTMLtext going to the clipboard unless specifically
>>  > requested (which would be more like pre LC-8), but fixing the actual
>>  > HTMLtext is a little more complicated.
>>
>> I think I missed something earlier in this thread:  what are those two
>> bug report IDs?
>>
>> --
>>   Richard Gaskin
>>   Fourth World Systems
>>   Software Design and Development for the Desktop, Mobile, and the Web
>>   ____________________________________________________________________
>>   [hidden email]                http://www.FourthWorld.com
>>
>> _______________________________________________
>> use-livecode mailing list
>> [hidden email]
>> Please visit this url to subscribe, unsubscribe and manage your
>> subscription preferences:
>> http://lists.runrev.com/mailman/listinfo/use-livecode
>>
>
_______________________________________________
use-livecode mailing list
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
Reply | Threaded
Open this post in threaded view
|

Re: Pasting tabs into Excel

Mark Wieder via use-livecode
In reply to this post by Mark Wieder via use-livecode
This is clearly closely related to the problem I had recently copying text from Livecode into Mac Mail.
YES, Livecode should copy PLAIN text when you ask it to copy PLAIN unformatted text. That said,

Here’s the script that I ended up using that work for copying a list of tabbed data into Excel. It’s a little different than Brian’s which he sent to help my question (thanks again!). It works in both cases (Mail and Excel), and is set up to work on three platforms.

Richard Burkett
[hidden email] <mailto:[hidden email]>

Here it is, as a version to copy text from a field in my test stack:

on mouseUp
   put fld "myField" into tClip
   lock the clipBoard
   set the ClipboardData to empty
   set the ClipboardData to tClip
   put the clipboardData["text"] into tClip
   set the rawclipBoardData to empty
   if the platform is "MacOS" then
      set the rawClipboardData["public.utf8-plain-text"] \
            to textEncode(tClip, "UTF-8" )   -- OSX
   else if the platform is "Linux" then
      set the rawClipboardData["text/plain;charset=utf-8"] \
            to textEncode(tClip, "UTF-8" ) -- Linux
   else if the platform contains "Win" then
      set the rawClipboardData["CF_UNICODE"] \
            to textEncode(tClip, "UTF-16" ) -- Windows
   end if
   unlock the clipBoard
end mouseUp


> From: "J. Landman Gay" <[hidden email] <mailto:[hidden email]>>
>
> On 2/3/18 12:23 PM, Brian Milby via use-livecode wrote:
>> Try this function (LC 8/9):
>>
>> *on*  stripClipboard
>>    *local*  tData
>>    *lock*  the clipboard
>>    *put*  the rawClipboardData["public.utf8-plain-text"] into tData
>>    *set*  the rawClipboardData to empty
>>    *set*  the rawClipboardData["public.utf8-plain-text"] to tData
>>    *unlock*  the clipboard
>> *end*  stripClipboard
>>
>> This will ensure the only thing on the clipboard is the text format (no RTF
>> and no HTML).  Even when I use this function after putting the text on the
>> clipboard, I get the same results.  And I get the same in LC 6 & 7 too.
>>
>> You mentioned raw clipboard, what did you try there?
>
> Your handler works. :) I haven't used rawClipboardData much and I don't
> have that particular experiment any more, but I think I just used the
> example verbatim from the dictionary.
>
> --
> Jacqueline Landman Gay         |     [hidden email] <mailto:[hidden email]>
> HyperActive Software           |     http://www.hyperactivesw.com <http://www.hyperactivesw.com/>
_______________________________________________
use-livecode mailing list
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode