Getting Apple Numbers data from Clipboard is Inconsistent

I have an app where users can copy and paste columns of numbers from other apps into mine.

Excel and other apps put a carriage return or CR/LF after each row’s contents, so I parse the incoming clipboard data based on LF/CR or just CR depending on the platform.

However, it turns out that Apple Numbers uses a carriage return before every entry, and then another one at the very end of the clipboard text.

This produces a phantom empty first cell. I could assume that users would never copy a blank cell as the first entry, but I know there are cases where that’s what the data is, so that’s what should be pasted.

So how do I make logical sense of this extra CR and know when it’s meaningful and when it’s not? Is there a RawData type I can look for to see when the data is coming from Numbers?

  • John

if the user DID copy data with a blank cell as the first entry…wouldn’t it have TWO CR at the beginning?
One to start the blank cell, and one to start the “2nd” cell

dim cellData() as string
cellData=split("X"+stringofData,endofline)

now just start with cellData(1) instead of zero

That would be true if the data comes from Numbers, but not if it comes from the other spreadsheets.

I just create the same spreadsheet in both EXCEL and Numbers

row 1 blank
row 2 [1] [2]
row 3 [3] [4]
row 4 [5] [6]

cut A1…B3 from each the results were IDENTICAL

[tab][LF]
1[tab]2[LF]
3[tab]4[LF]
5[tab]6[LF]

so the cut/paste format between NUMBERS and EXCEL under OSX are NOT different they are the same and should require no fancy processing.

@Dave, Try it with content in all the cells, including the first row, and all in a single column.

Here’s my code:

dim c as new Clipboard
dim Astr as string

if c.TextAvailable then

  if c.Text.Encoding = nil then
    c.Text = c.Text.DefineEncoding(Encodings.SystemDefault)
  end
  
  Astr=c.Text
  
 break

end if

At the break, look at the value of Astr in the IDE.

When copying 10 cells from Numbers, with the numbers 1-10 in them (all in the same column), I get this in binary:

0D31 0D32 0D33 0D34 0D35 0D36 0D37 0D38 OD39 0D31 300D 0D

When copying the same 10 cells from an Excel spreadsheet, I get this in binary:

310D 320D 330D 340D 350D 360D 370D 380D 390D 3130

That is strange… I had just pasted into a Hex Editor and got this for BOTH

31 0A 32 0a 33 0a 34 0a 35 0A 36 0A 37 0A 38 0A 39 0A 31 30

Notice… I get 0A which makes sense since LF is standard for OSX, while you say you get OD

but if I run your “code” I get what you say… which in my opinion is WRONG, since it should be OA for both EXCEL and NUMBERS
and the order should be the SAME (which for any other program it IS

Personally I “think” it has to do with how XOJO is dealing with the clipboard, as in EXCEL there is no “default” formatting, but in NUMBERS there is (shading in 1st row/col), although in NUMBERS it doesn’t seem to matter where you cut the cells from

The plot thickens, indeed. I copied from Excel (MS Office 2008) and Numbers and pasted into 0xED (my favorite Hex editor) and got the same results from both apps, EXCEPT that Numbers used 0A and Excel used 0D.

Which sure makes it look like there’s a problem with the Xojo Clipboard, and I don’t see any workarounds. I hate telling my users that unexpected results aren’t my fault…:frowning:

Hopefully one of the Xojo engineers will read this and clarify what’s happening!

John… Numbers obviously under OSX… but which EXCEL (OSX or WIN)?

I am using OSX Excel, and get 0A not 0D, but I would expect (can’t prove) that WIN Excel would be 0D0A

Years ago when I first started dealing with the clipboard, the Mac documentation mentioned that the application can and should copy the data to the clipboard in multiple formats so that the destination application could then take its pick of the best format to use. For example, Numbers could be copying both formatted and unformatted text as well as a Numbers internal format (for formulas etc.). I don’t know if the clipboard still works the same way, but if so, you may have to check that you’re using the correct clipboard data.

Both Excel and Numbers are on Mac OS X, Excel is Microsoft Office 2008 for Mac.

That’s exactly what I was looking for in my initial message: What RawData UTI should I use to know that the data came from Numbers instead of Excel?

Here is some info on Windows clipboard data types:
https://msdn.microsoft.com/en-us/library/windows/desktop/ms649013(v=vs.85).aspx

I tried to find a similar one for Mac, but wasn’t able to locate any. From dealing with the clipboard years ago on Mac (pre-OSX) each data type had a four character type code similar (and in many cases identical with) four character file types, such a TEXT, PICT, etc. There were quite a few different ones, for all of the different image formats and various data structures. I was using Think Pascal in those days, and you had to go digging through the info in the clipboard object to find the data in the format that you were looking for.

This is probably moot, because I just fired up one of my old XOJO projects to see how it is handled, and realized that you are given only two choices: text or picture. So, obviously XOJO is picking and choosing which of the many clipboard formats to present to you, and you don’t have any choice.

I think there are some utility programs that will allow you to examine the contents of the clipboard to see all of the formats of the data currently available. If you can find one that is suitable, then maybe you can use some alternative code to extract it (either a plug-in or some declares).

PasteBoard Peeker is handy

Thanks to Norman and PboardPeeker, here’s the answer:

[code] if c.RawDataAvailable(“public.utf8-plain-text”) then 'Apple Numbers has this available

Astr=c.RawData("public.utf8-plain-text")

elseif c.textAvailable then

 Astr=c.Text

end if

if Astr.Encoding=nil then 'Encoding is always nil
Astr=Astr.DefineEncoding(Encodings.UTF8)
end if
[/code]

actually the right one to grab is ClipBoardViewer
https://developer.apple.com/library/mac/samplecode/ClipboardViewer/ClipboardViewer.zip

The Clipboard class already looks for public.utf8-plain-text, so I’d be surprised if TextAvailable was false.

There’s differences in what the two programs put on the clipboard, but I’d be amazed if they were documented and any sort of stable interface.

You could check to see if the raw data type com.apple.iwork.TSPNativeData exists, and if so, you know that it came from one of the iWork apps.

Also, UTI’s public.utf8-plain-text and NSStringPboardType have no leading CR
while UTI’s TEXT and com.apple.traditional-mac-plain-text have a leading CR

Except it’s totally possible that iWorks will stop doing that in the next release. These are undocumented implementation details.