Getting data out of Excel

[Michael Julian Lew]

I'm trying to paste data from MS Excel into a table in my Livecode stack 
without success. I suspect that either OSX or Excel have added unwelcome 
complications.

I used to be able to just paste, but nowadays (Excel 2011 Mac OSX 10.7.4) the 
data come in as a long string, with six spaces where a return should be and 
three where a tab should be. Putting the clipboardData["text"] makes no 
difference, and the clipboardData["RTF"] is not what I want.

I can make it work by copying cells in Excel, then Paste Text Only in Nisus 
Writer Pro, then copying and pasting the text from there.

I can write a function to clean up the clipboardData, but it seems silly to 
need to do so. Does anyone have a quick solution?

Regards,
Michael Lew

___
use-livecode mailing list
use-livecode@lists.runrev.com
Please visit this url to subscribe, unsubscribe and manage your subscription 
preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode

Re: Getting data out of Excel

[Kay C Lan]

If you are absolutely sure that the clipboardData["text"] has return
represented by six spaces, and tabs by 3 spaces, then:
put clipboardData["text"] into tData
replace "  " with return in tData
repace "   " with tab in tData

Will only take milliseconds to do what you need! How much quicker do you
want?

On Wed, Jul 24, 2013 at 11:09 AM, Michael Julian Lew <
micha...@unimelb.edu.au> wrote:

> I'm trying to paste data from MS Excel into a table in my Livecode stack
> without success. I suspect that either OSX or Excel have added unwelcome
> complications.
>
> I used to be able to just paste, but nowadays (Excel 2011 Mac OSX 10.7.4)
> the data come in as a long string, with six spaces where a return should be
> and three where a tab should be. Putting the clipboardData["text"] makes no
> difference, and the clipboardData["RTF"] is not what I want.
>
> I can make it work by copying cells in Excel, then Paste Text Only in
> Nisus Writer Pro, then copying and pasting the text from there.
>
> I can write a function to clean up the clipboardData, but it seems silly
> to need to do so. Does anyone have a quick solution?
>
> Regards,
> Michael Lew
>
> ___
> use-livecode mailing list
> use-livecode@lists.runrev.com
> Please visit this url to subscribe, unsubscribe and manage your
> subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-livecode
>
___
use-livecode mailing list
use-livecode@lists.runrev.com
Please visit this url to subscribe, unsubscribe and manage your subscription 
preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode

Re: Getting data out of Excel

[dunbarx]

Kay.


I think Michael mentioned that he could do the housekeeping. He was wondering 
why excel padded the data so oddly, So do I. I have seen excel do odd things 
now and then.


But Michael, if you take an excel spreadsheet and copy a few lines of data that 
each have a few columns, and, say, in a brand new dataGrid set the dgText to 
the clipboardData, does the info load correctly, with the columns set and all?


And if you get the length of the clipBoardData, knowing that each tab and all 
returns but the last one count as a char, does the value seem right?


Craig Newman



-Original Message-
From: Kay C Lan 
To: How to use LiveCode 
Sent: Wed, Jul 24, 2013 2:42 am
Subject: Re: Getting data out of Excel


If you are absolutely sure that the clipboardData["text"] has return
represented by six spaces, and tabs by 3 spaces, then:
put clipboardData["text"] into tData
replace "  " with return in tData
repace "   " with tab in tData

Will only take milliseconds to do what you need! How much quicker do you
want?

On Wed, Jul 24, 2013 at 11:09 AM, Michael Julian Lew <
micha...@unimelb.edu.au> wrote:

> I'm trying to paste data from MS Excel into a table in my Livecode stack
> without success. I suspect that either OSX or Excel have added unwelcome
> complications.
>
> I used to be able to just paste, but nowadays (Excel 2011 Mac OSX 10.7.4)
> the data come in as a long string, with six spaces where a return should be
> and three where a tab should be. Putting the clipboardData["text"] makes no
> difference, and the clipboardData["RTF"] is not what I want.
>
> I can make it work by copying cells in Excel, then Paste Text Only in
> Nisus Writer Pro, then copying and pasting the text from there.
>
> I can write a function to clean up the clipboardData, but it seems silly
> to need to do so. Does anyone have a quick solution?
>
> Regards,
> Michael Lew
>
> ___
> use-livecode mailing list
> use-livecode@lists.runrev.com
> Please visit this url to subscribe, unsubscribe and manage your
> subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-livecode
>
___
use-livecode mailing list
use-livecode@lists.runrev.com
Please visit this url to subscribe, unsubscribe and manage your subscription 
preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode

 
___
use-livecode mailing list
use-livecode@lists.runrev.com
Please visit this url to subscribe, unsubscribe and manage your subscription 
preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode

Re: Getting data out of Excel

[Richard Gaskin]

Michael Julian wrote:

> I used to be able to just paste, but nowadays (Excel 2011 Mac OSX
> 10.7.4) the data come in as a long string, with six spaces where
> a return should be and three where a tab should be.

That seems a silly format, even by Microsoft standards (ever looked at 
the bizarre range of variance among their own CSV export formats? ).


For example, what does the format look like if you happen to have three 
spaces within a cell's data?


If you paste into a text editor do you see the same pattern?

I'm hoping this is some odd anomaly in LiveCode, rather than one of the 
lamest design decisions I've ever seen from a company as occasionally 
smart as Microsoft.


Kay C Lan's suggestion should get you going, but I'd be interested to 
learn if the oddity is on Excel's side or LiveCode's.  If LiveCode, 
please file a bug report.


If it would be more convenient to import the file directly, Currey 
Kenworthy's been working on a nifty library to do that for Excel files - 
I don't see it on his site yet, but here's the link to his announcement 
on this list:



And if this is just for internal use, here's an alternative that's both 
free and written by sane people:




--
 Richard Gaskin
 Fourth World
 LiveCode training and consulting: http://www.fourthworld.com
 Webzine for LiveCode developers: http://www.LiveCodeJournal.com
 Follow me on Twitter:  http://twitter.com/FourthWorldSys


___
use-livecode mailing list
use-livecode@lists.runrev.com
Please visit this url to subscribe, unsubscribe and manage your subscription 
preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode

Re: Getting data out of Excel

[Dr. Hawkins]

On Wed, Jul 24, 2013 at 5:37 AM, Richard Gaskin
 wrote:


> 

Which is my solution.  Copy from libreoffice, paste into a table, and I'm done.



-- 
Dr. Richard E. Hawkins, Esq.
(702) 508-8462

___
use-livecode mailing list
use-livecode@lists.runrev.com
Please visit this url to subscribe, unsubscribe and manage your subscription 
preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode