Pasting as table … doesn't

Vince's Avatar

Vince

26 Apr, 2018 04:33 PM

I copied (Ctrl-C) a 5x6 section of an Excel spreadsheet (see picture). I then "Pasted as table" in MMD. I got … weirdness.
If I paste it into a text editor (BBEdit in my case), I get the expected, i.e. text separated by tabs.

Also, I was surprised there wasn't a "Convert selection to table" menu item. If you can paste something as a table, I would think the same logic could be applied to a selection. Please consider adding that.

| distance   |   | angle | separation    | sep   | 
    ------- | ----   | ---- | ---------- | --- |
| ------    | ------    | ------    | ------    | ------    | ------    |  
| 10    | feet  | 0.5   | 0.087266186   | 1.05  | inches
    |   | | | |
| 300   | feet  | 0.5   | 2.617985571   |   | 
    |   | | | |
| 5280  | feet  | 0.5   | 46.07654605   |   | 
    |   | | | |
| 240000    | miles | 0.5   | 2094.388457   |   |
  1. Support Staff 1 Posted by Fletcher on 26 Apr, 2018 06:30 PM

    Fletcher's Avatar

    Can you send me the excel file you you tried to use and the resulting Composer file you get when pasting?

    It works fine for me.

    Thanks,

    F-

    --
    Fletcher T. Penney
    Manager, Founder
    MultiMarkdown Software, LLC
    [email blocked]

  2. 2 Posted by Vince Rice on 26 Apr, 2018 06:50 PM

    Vince Rice's Avatar

    Sure. I'm going to send another reply to this email (you should at least get this if you don't get the other).

  3. 3 Posted by Vince Rice on 26 Apr, 2018 06:58 PM

    Vince Rice's Avatar

    Here you go. I actually re-constructed this on a different computer using a different version of Excel into a different MMD document, with the same results. (I had already closed Excel and didn't save the spreadsheet, I just created it to get the data to copy into MMD.)

    In Excel, I highlighted just the area in question (A1 thru F5; I think it should be highlighted when you open it), hit Cmd-C to copy it, then Alt-tabbed over to MMD and selected Paste/Paste as Table from the menu.

    Original issue was on an iMac running 10.12.6, Excel 16.12, MMD 4.3.3. This was an MMD document I'd been working on for most of the day.
    This one on a MacBook Pro running 10.12.6, Excel 14.7.7 (i.e. Office Mac 2011), MMD 4.3.3. This was a brand new MMD document created just for this purpose; I typed the "This is a test" line, did the paste, and that was it.

  4. Support Staff 4 Posted by Fletcher on 26 Apr, 2018 07:29 PM

    Fletcher's Avatar

    I don't actually have Excel -- when I open your file in LibreOffice and copy the relevant section of the document, it works fine.

    Are you only copying the visible portion of the table?

    Can you send me a text file when you paste into BBEdit?

    I'm out of town, so am limited to what I have installed on my laptop and have limited internet...

    F-

    --
    Fletcher T. Penney
    Manager, Founder
    MultiMarkdown Software, LLC
    [email blocked]

  5. 5 Posted by Vince Rice on 26 Apr, 2018 07:40 PM

    Vince Rice's Avatar
    In Excel, I highlighted just the area in question (A1 thru F5; I think it should be highlighted when you open it)

  6. Support Staff 6 Posted by Fletcher on 26 Apr, 2018 07:58 PM

    Fletcher's Avatar

    What happens if you copy the text from the BBEdit file and paste that into Composer as a table?

    When I do that, it works fine.

    F-

    --
    Fletcher T. Penney
    Manager, Founder
    MultiMarkdown Software, LLC
    [email blocked]

  7. 7 Posted by Vince Rice on 26 Apr, 2018 08:58 PM

    Vince Rice's Avatar

    Yes, that does work.
    But I'm not pasting from BBEdit. :) If I'm pasting as a table, it's going to be coming from Excel. Because it's, you know, tabular. (Not tubular, that's, like, a whole different thing, ya know?)

    Aside: Why are the rows (except for the last line) pasted with double-spaces on the end of the line? A table row doesn't need a hard break in any form of MD, including this one. (Deleting the extraneous spaces makes no difference in formatting.)

    For completeness, here's an updated version of the test .md file. It contains three sets of pairs of data: data pasted via Cmd-V and via "Paste as table," from Excel, from Numbers, and from Excel to BBEdit to MMD. I did it twice — once with "Use elastic tabstops" checked (which is where I normally have it), once with it unchecked. I've attached pictures of both (since the file itself is the same in both cases, I believe). The file attached was the one with it checked.

    Excel pasted normally looks fine, so there's nothing inherently wrong with the data. But it's sure getting messed up in Paste as Table.

    With elastic tabs checked.

    With elastic tabs unchecked.

  8. Support Staff 8 Posted by Fletcher on 26 Apr, 2018 10:41 PM

    Fletcher's Avatar

    Surprisingly, a Microsoft product seems to be doing something non-standard.... ;) I'll have to try and figure out what Excel is putting on the pasteboard, and why it doesn't match what is expected.

    In meantime, you could probably paste from Excel as regular text, then copy/cut, and then re-paste as table. That *should* provide you with the proper result, albeit with an extra two steps.

    Markdown doesn't support tables, so the default formatting for MultiMarkdown tables is to include spaces at the end so that it degrades to something cleaner than a run-on paragraph if it's passed through a MD processor. I do the same thing for metadata for the same reasons. Neither syntax structure *requires* the line breaks, but including them makes for a slightly "safer" document. (You can turn on the "Limit to Strict Markdown functionality" preference and look at the preview to see what I mean if you care, but can probably imagine it just fine.)

    Elastic tabstops don't change the underlying text, merely sets the tab widths for the editor view based on content, rather than a fixed width. So that shouldn't play any role here, except in terms of how the tables (or almost tables) look.

    Instead of paste as table, do you see anything strange if you paste as something else (e.g. bulleted/enumerated list or block quote)? They use the same underlying access to the macOS pasteboard, but then process the text differently). If you do see something strange happening, let me know and maybe that will help me identify the issue.

    Otherwise, I could create a dummy app that records exactly what gets pasted and would allow me to see exactly what Excel is sending.

    Thanks, and as usual you managed to find an interesting problem for me to solve.... ;)

    F-

    --
    Fletcher T. Penney
    Manager, Founder
    MultiMarkdown Software, LLC
    [email blocked]

  9. 9 Posted by Vince Rice on 27 Apr, 2018 02:51 PM

    Vince Rice's Avatar

    Hah! I just use the product, man, I just use the product. :)

    The harsh reality is that they're still the 800-pound gorilla. (They used to be 1500-pound, they're getting smaller.) Whatever they do has to be accounted for. But it pastes correctly when pasting normally, so it can't be that different can it? (Rhetorical, don't answer that. :) )

    Re elastic tabs, that's what I thought. (That's why I didn't send two files.) Interesting thing about them, though. Given that they don't impact the file, I would expect toggling the setting off and then back on to put the display back to where it was before. It doesn't. Once it gets messed up from the toggling off, it stays messed up.

    This is honestly the first time I've ever "pasted as". I'm rarely pasting anything in an MD document, I'm typing it from scratch. I only did the table because I had already created the spreadsheet to get the numbers, and figured it was easier to paste than re-type it. Boy was I wrong…

    Speaking of, you didn't say why there isn't a "Convert to table" menu item. That's what I was actually looking for in the first place.

    Do you mean past as something else from Excel specifically? If I get a chance I'll play with it.

  10. Support Staff 10 Posted by Fletcher on 27 Apr, 2018 04:03 PM

    Fletcher's Avatar

    How would you want a convert to table to work? Convert all tabs to pipes? I use something similar when pasting as a table because I "assume" that I'm getting accurate data. Could do the same with selected text and leave it to user to ensure properly formatted data. Or is there a different way you would want it to work?

    Yes - if you copy from excel and then paste as list it might give a clue as to what excel is sending composer.

    F-

    Sent from my iPhone

  11. 11 Posted by Vince Rice on 27 Apr, 2018 04:20 PM

    Vince Rice's Avatar

    I'm fine with that, i.e. doing essentially the same thing as Paste as Table. I think you have to make some assumptions on any "Convert as", and that's a decent one for a table.

    Here's an update to the "Table Test.md" file. I added three things:
    The first column from the Excel spreadsheet pasted as bulleted list.
    The entire table (A1-F5) pasted as enumerated list.
    The actual data (B1-F5) pasted as table rows. I copied the first two rows (header and underscores) from one of the tables above so it would format right.

    All of those look like they should. That's looks to me like it's pointing to something in Paste as Table specifically.

    Also, on a whim, I copied the table (A1-F5) to a different position in the middle of the spreadsheet, then copied it from there and pasted it as table. Same behavior, so I didn't bother saving it. (I was eliminating the possibility that since pasting from B1-F5 as table rows worked, that maybe it was something with including the first row. I know, insanity, but I've seen stranger things.)

  12. Support Staff 12 Posted by Fletcher on 27 Apr, 2018 04:55 PM

    Fletcher's Avatar

    Hmmmm... Nothing jumps out immediately from the lists....

    Though I do see that the last table has a pipe before the `10` in the first row, but no leading pipes in the subsequent rows. When I paste as table or as table rows, I get leading pipes...

    I think I'll have to create that test app to record exactly what Composer is getting from the pasteboard. I'll send you a download link when I do.

    Thanks again for helping me troubleshoot this,

    F-

    --
    Fletcher T. Penney
    Manager, Founder
    MultiMarkdown Software, LLC
    [email blocked]

  13. Support Staff 13 Posted by Fletcher on 27 Apr, 2018 05:23 PM

    Fletcher's Avatar

    Ok -- here is a quick app with a window and text view.

    1. Copy from excel and paste into the textview. It will show you the path to a file that contains only the string that was received.

    2. Paste from excel into bbedit. Then copy from BBEdit and paste into the test app. It will created another log file.

    3. Send me the two log files and I'll take a look.

    The test app uses the same approach to grabbing text from the clipboard that Composer uses for creating tables. So this should tell us if something strange is happening. If not, I'm at a loss....

  14. 14 Posted by Vince Rice on 27 Apr, 2018 06:47 PM

    Vince Rice's Avatar

    It says I need 10.13. I'm still on Sierra.

  15. Support Staff 15 Posted by Fletcher on 27 Apr, 2018 07:43 PM

    Fletcher's Avatar

    Uggh.. Stupid defaults.

    This should run on 10.12.

  16. 16 Posted by Vince Rice on 27 Apr, 2018 08:48 PM

    Vince Rice's Avatar

    It says it's not from a trusted developer. What do they know I don't know? Why don't they trust you? How do I even know you're you? I'm going to need a copy of your driver's license and SS# and probably a credit card.

    Here are three files, one from Excel, from BBEdit (via Excel), and from Numbers. I added to the end of the name how each was created.
    Spoiler alert: they're all identical, except that Numbers has more digits of precision in one of the cells.

  17. Support Staff 17 Posted by Fletcher on 27 Apr, 2018 09:32 PM

    Fletcher's Avatar

    OMG..... I am beginning to think that your computer has been cursed by someone for the sole purpose of torturing me.... ;)

    I just looked at the code again to make sure I am not missing something obvious... I don't see it.

    Time to revisit the seemingly obvious...

    To confirm -- you are using Copy/Paste, not drag and drop, right? (Clearly you didn't when using the test app, b/c it would not have worked). (Speaking of, you could try drag/drop into Composer (not test app), to see if it provides different input).

    Also, do you have any utility apps running that could modify the pasteboard?

    I might have a really old copy of Excel sitting around (circa 15 years ago?) I will see if I can run it still (my guess is no), so I can try to see what's happening that way. Will have to do that next week when I am back home from vacation.

    Do the same/similar mistakes happen when you copy/paste different tables from Excel? Or is it just this one particular instance?

    Another next step will be to build a custom beta version of Composer for you that logs the text like the test app, but in the actual production use of creating a table.... I don't know what else to do.

    (Speaking off, when I work some more this coming week on scrolling, I'll build a test version of that as well. It will be a big change to make without testing for other users besides myself.)

    This has become frustrating, but I refuse to let Microsoft beat me!! ;)

    F-

    --
    Fletcher T. Penney
    Manager, Founder
    MultiMarkdown Software, LLC
    [email blocked]

  18. 18 Posted by Vince Rice on 27 Apr, 2018 10:15 PM

    Vince Rice's Avatar

    So this is the point where you re-read my very detailed problem report, where I answered all of those questions. :)

    I am Cmd-C'ing in Excel and Cmd-V'ing in MMD (for regular paste). For "Paste as Table" I'm Ctrl-C'ing in Excel and then in MMD clicking the Edit menu, then Paste, then Paste as table. Drag-and-drop never occurs to me between apps. (I know, I'm a troglodyte.) D&D doesn't work from Excel (either version), anyway; highlighting the table and clicking just starts a new selection.*

    And, as I also said in that awesome blossom of a problem report, I've done this on two completely different computers (iMac and MBP) using two completely different versions of Excel (2011 and 2016). What I probably didn't say (maybe it isn't that awesome of a report) was that I re-created the file in Excel both times. (Oops, I just checked, I did say that. Back to awesome.) The only thing in common is the version of the OS and the version of MMD. They're also not running the same set of startup items (there are some overlaps, certainly). To answer your specific question, I have no idea if there are any that would modify the pasteboard. I don't think I do, but one never knows, does one?

    So, just for you, I started my MBP in safe mode and did another test; copying (Cmd-C) from Excel and Edit/Paste/Paste as table in MMD. Same screwed up result (I didn't bother saving the MMD file, since you already have an example.)

    MS has a one month free trial <https://products.office.com/en-us/try> for Office 365, so you can get the current version free for a month if you want to troubleshoot it that way. But I'll be glad to run a debug version of MMD.

    On a cost per hour basis, I think this might be the most expensive application I've ever used.
    Of course, on a cost per hour basis, I'm the most expensive customer you've ever had.
    Which one of us is going to give up first? I'm pretty stubborn… lol

    Stop working on this while you're on vacation. When you said you were out of town I thought you were on a business trip.

    *I thought that was weird, so I duck'ed it. Turns out you have to put your cursor exactly on one of the edges, and the cursor then turns into a hand that lets it be dragged. The results are worse — dragging it into MMD, every tab is turned into a newline, so it looks like this. This is why I don't use drag-and-drop. (That's not why. I don't use it because I'm a troglodyte.)

  19. Support Staff 19 Posted by Fletcher on 27 Apr, 2018 10:54 PM

    Fletcher's Avatar

    Hopefully the app has also saved you a lot of time as well as costing you a lot. ;)

    And yes - you probably win the award for taking the most time on my end, but conversely the app is much better for the things I've fixed based on your feedback.

    If I can do a trial of excel that is much better. It allows me to do more testing and does not require more of your time. If I could have recreated this issue I would much prefer to test and fix it myself without bothering someone else. But sometimes that is not possible. Kind of like my day job as a doctor - sometimes I have to get the history from the patient. Sometimes from the family. But some things can't be solved without a good history, no matter how smart the doctor is.

    But I've also learned that sometimes to you have to ask the same questions again to be sure you're getting the right answer, no matter how smart the patient is.... ;)

    I don't mind dealing with emergencies on vacation. And while not an emergency anymore, this has piqued my interest.

    F

    Sent from my iPhone

  20. 20 Posted by Vince Rice on 27 Apr, 2018 11:27 PM

    Vince Rice's Avatar

    If you're going to take me seriously this isn't going to be nearly as much fun.
    I was joking, just joking. I think it's funny we keep ending up (temporarily) stymied by weird behavior.

    I have no doubt you will be much more productive if I'm not involved. :) But, I'll be glad to run a debug version (or test a new version) when/if the time comes.
    Note that the trial is for all of Office, not just Excel, and unfortunately it insists on installing everything. I know, I don't like it, either. (I don't use Powerpoint or Outlook and I don't want them on my computer. Too bad, says MS. Oh, and it phones home constantly. If you don't already run something like Little Snitch, you should think about it. Not because of Office, because that behavior is the norm these days.)

    And no one said the patient was smart. Awesome, yes, smart, no. (OK, maybe not awesome, either.)
    This is why I (think I) tend to over-provide information; I've been on your side of a problem too many times and I'm always terrified I'm going to miss something obvious.

    This is definitely not an emergency. The emergency is where the heck is the iOS version? (See first sentence above.)

  21. Support Staff 21 Posted by Fletcher on 28 Apr, 2018 12:51 PM

    Fletcher's Avatar

    I know you are mostly joking. Just wanted to emphasize that (and I think by now you know) that I take my software seriously, at least in certain respects. I don't like it when it's broken, and I especially don't like it when customers lose their data or waste their time. Given your willingness in the past to help me track things down, I think that at least part of you is intrigued by the weird edge cases that keep turning up... ;)

    Here is a beta version of Composer (basically same as latest release, except that when you "paste as table", it creates 2 log files -- one is the text string it receives, and the the next is the table it creates. So if you will copy from Excel, and then paste as table, and then send the two log files. For the moment, I don't need any other tests (e.g. BBEdit, etc.) A "positive" test will be that the second log file will have a messed up looking table. (There are no messages in this version, the 2 files just appear in your home folder).

    And yes, I do still have an iOS version in development, but for a variety of non-technical reasons it is on hold (real life keeps moving on even if that means software development doesn't.....). I hope to get back to it soon if only people would stop bugging me about flaws in the Mac version..... ;)

    The test build works on my machine, and hopefully it works on yours, but if you get an error let me know what it is and I can recompile. Also, some features are disabled, and I don't recommend using this build for real work.

  22. 22 Posted by Vince Rice on 28 Apr, 2018 05:35 PM

    Vince Rice's Avatar

    Right. This was my point — you don't have to defend yourself. First, because I was joking, and second, I'm obviously well aware by now that you want things to work correctly. :)

    And, yes, I am equally obviously always intrigued by these things. :)

    Here you go. It was a "positive" test.
    The first thing I see is that the "normal" file is delimited by carriage returns instead of line feeds. I'm assuming/guessing that's the problem? Pasting normally manages to handle it, but as table doesn't?
    I did it twice just to make sure. (Results were the same, so I'm only sending the first pair of files.)

  23. Support Staff 23 Posted by Fletcher on 28 Apr, 2018 06:07 PM

    Fletcher's Avatar

    Thank goodness there was a difference. I had no idea what to do next if there wasn't....
    ;)

    I should have figured it was a line ending issue. Shame that Microsoft still has not figured line endings out after so many decades.... Interestingly, they don't seem to make the mistake I would have expected (using Windows line endings), but instead using the wrong kind of Mac line endings (\r instead of \n, which was the line ending format used in the pre-OS X days. So they're only 17 years behind the times....)

    Short version -- Composer normalizes line endings every time it opens a file, and saves files using the line endings specified in preferences. Apparently pasting the Excel text as is fixes the line endings. But the paste as table acts on the raw string before any changes are made. That is why pasting alone, and pasting/copying/paste as table, both work.

    Attached is a new beta version that should fix the line endings before processing the table. Interestingly, the programs I use to copy the "wrong" text must fix it before pasting, so I can't actually replicate the problem without jumping through some major hoops.... ;)

    Hopefully this works for you, and will be included in the next release.

    Thanks again for your help!!!

    F-

  24. 24 Posted by Vince Rice on 28 Apr, 2018 06:40 PM

    Vince Rice's Avatar

    Yes to all the MS stuff.

    Yes to the beta working (pause for celebration). Just a couple of niggles (that have, I assume, nothing to do with this problem).
    The last line doesn't have a hard break, i.e. the two trailing spaces. The cursor is left immediately following the last pipe on the last line.
    The last line is using space before that last pipe instead of a tab. In the picture, what's between "miles" and that last pipe are two spaces instead of a tab. The rest of the table is formatted with tabs.

    From Excel in the new beta:

    Hmmm. I just copied a "normal" copy of the text (just the tab-delimited text, not the formatted-as-a-table text) from "Table test.md" and pasted it as a table. Interestingly, it still used spaces following the "miles", but it used three spaces after, so it lined up. And if you look at the "Table test.md" file, the data pasted as table from Numbers uses a tab following "miles", as does the data pasted from BBEdit. So, in summary:
    "Pasted as table" from Numbers and from BBedit "works" in that tabs are used throughout, including the final one.
    "Pasted as table" from Excel (with the new logic) has tabs except for the final one, where it uses two spaces.
    "Pasted as table" from MMD itself (the data is tab-delimited in the file; you have it, so you can see for yourself), when the selection stops immediately after the "s" in miles (IOW, the entire last line isn't selected), has tabs except for the final one, where it uses three spaces.
    "Pasted as table" from MMD itself, when the selection includes the entire last line, has tabs throughout, as well as the final hard break.

    And, you obviously know this, but to state the obvious — we have control over what we highlight when we're highlighting from a text editor (although I would argue "Paste as table" should behave the same regardless), but we don't when copying from Excel (or Numbers, but pasting from Numbers does the right thing).

    In short, IMO, "Paste as table" should always look like the very last example below, i.e. with tabs throughout including the final one between the last cell and the last pipe, and with a hard break on the final line.

    From MMD, copying only through the last character:

    Turns into this table (what follows "miles" are spaces, not a tab):

    From MMD, copying the entire last line:

    Turns into this table:

  25. Support Staff 25 Posted by Fletcher on 01 May, 2018 12:51 PM

    Fletcher's Avatar

    I posted a 4.4 beta release that should fix the major issue with copying from Excel, as well as a few other things. Once it's been tested, it will be pushed out for general use:

    http://support.multimarkdown.com/discussions/betas/384-composer-44-...

  26. Fletcher closed this discussion on 01 May, 2018 12:51 PM.

Comments are currently closed for this discussion. You can start a new one.

Keyboard shortcuts

Generic

? Show this help
ESC Blurs the current field

Comment Form

r Focus the comment reply box
^ + ↩ Submit the comment

You can use Command ⌘ instead of Control ^ on Mac