Skip to content

Why are we quoting the clipboard contents when copying from a table view? #1244

Closed

Description

@MKleusberg

In issue #1058 @sky5walk wrote:

Why are all entries returned in double quotes?
"xx"[TAB]"yy"[TAB]"1234"[CR][LF]
Is this to allow a TAB character within the data?
Could you add an option to drop all double quotes and just return contents [TAB] or [Comma] delimited?

I'm trying to mimic the CSV export functionality in a quicker action and on smaller selections.

Ok, let me summarize this way.
Is it risky to follow the cut/paste behavior of MS Excel?
Why add double quotes around each cell's contents?

@mgrojo already answered that it does seem we're using this to differentiate between NULL values and empty strings or something similar. Because I don't have any idea either I'll try to figure out why we're doing this and if we can change it 😄

Activity

  1. self-assigned this
    on Nov 29, 2017
  2. added a commit that references this issue on Nov 29, 2017
  3. mgrojo commented on Nov 30, 2017

    @mgrojo
    Member

    I'm starting to think that we add quotes only for better spreadsheet compatibility in any case. Both Excel and LibreOffice are parsing well the quoted data. But our parsing is broken both for multi-line cells and cells containing tabs.

    Maybe we could get rid of the internal buffer and use another MIME type (in addition to text and HTML) for our internal copy/pasting. This way we could facilitate our parsing and at the same time set a sensible text format, compatible with spreadsheets and other applications, with quotes oly around multi-line data and data containing tabs. Maybe we could use text/csv for that as defined in RFC 4180 and reuse the CSV parsing.

  4. mgrojo commented on Nov 30, 2017

    @mgrojo
    Member

    Another problem, that we have, is pasting from LibreCalc (5.1.6.2). An empty cell is pasted because LibreCalc adds an end of line after the last cell.

  5. justinclift commented on Dec 1, 2017

    @justinclift
    Member

    From my point of view, pretty much anything which gets this working better is welcome. 😄

    As a data point with RFC 4180 though... it's pretty crap for database data. There's no good way to differentiate between fields which contain (say) NULL vs an empty string. That's just the first example which I generally remember, but I'm pretty sure I've hit others before too.

    So, using a different MIME type might be a really good idea. But lets pick a non-CSV thing. 😀

  6. MKleusberg commented on Dec 1, 2017

    @MKleusberg
    MemberAuthor

    Yeah, we would need to choose some binary format in order to store BLOBs and NULL values as well. Both don't really work well with CSV. JSON could handle NULL values easily but as it too is a text format, storing binary data would be a pain. I'm not sure either if we can just invent our own MIME type and expect it to work on all system (pretty sure it's at least discouraged).

    The alternative is to tweak the internal buffer system to work more reliably and to be used in more situations. But the coordination between internal buffer and system clipboard would always remain hack-ish.

    Looks like a dilemma to me 😦

  7. sky5walk commented on Dec 1, 2017

    @sky5walk

    Focusing on the 90% use case:
    csv cut and paste
    Just double quote data whose column is defined as text. Leave all others unadorned.
    I never presumed to cut or paste blobs.

  8. mgrojo commented on Dec 1, 2017

    @mgrojo
    Member

    I think we can add quotes only around multi-line cells and cells containing tabs. I've made these changes and it seems to work well for spreadsheets (only tested with LibreCalc under Linux) and our pasting is no more broken as before.

    @@ -242,11 +242,11 @@ void ExtendedTableWidget::copy(const bool withHeaders)
                 QString headerText = model()->headerData(i, Qt::Horizontal, Qt::DisplayRole).toString();
                 if (i != firstColumn) {
                     result.append(fieldSepText);
                     htmlResult.append("</th><th>");
                 }
    -            result.append(QString("\"%1\"").arg(headerText));       // TODO: Check if we need the quotes here
    +            result.append(headerText);       // We don't need quotes for the column names
                 htmlResult.append(headerText);
             }
             result.append(rowSepText);
             htmlResult.append("</th></tr>");
         }
    @@ -277,11 +277,14 @@ void ExtendedTableWidget::copy(const bool withHeaders)
             // TODO: Do we really want to do this for the non-internal buffer case? The only case where we should definitely quote the data is when there are line breaks
             //       in the text, again for spreadsheet compatability. We also need to quote when there are tabs in the string (or replace the tabs by spaces, that's what
             //       LibreOffice seems to be doing here).
             if (!data.isNull()) {
                 text.replace("\"", "\"\"");
    -            result.append(QString("\"%1\"").arg(text));
    +            if (text.contains("\n") || text.contains("\t"))
    +                result.append(QString("\"%1\"").arg(text));
    +            else
    +                result.append(text);
             }
         }
     
         QMimeData *mimeData = new QMimeData;
         mimeData->setHtml(htmlResult + "</td></tr></table></body></html>");
    
    

    @MKleusberg and @justinclift How do you see this for a commit?

  9. justinclift commented on Dec 1, 2017

    @justinclift
    Member

    To play devils advocate 😀, if we invent our own MIME type just for internal use, we could then use (say) XML (ugh!) which gives us a lot of flexibility to put whatever-the-heck-we-want in between tags. Including binary. Maybe BASE64 it to be platform neutral, but still... binary when it comes to it. eg:

    <our-friggin-awesome-internal-format>
      <oh-crap-its-binary-base64>DEADBEEF</oh-crap-its-binary-base64>
    </our-friggin-awesome-internal-format>
    
    <our-friggin-awesome-internal-format>
      <null/>
    </our-friggin-awesome-internal-format>
    
    <our-friggin-awesome-internal-format>
      <what-we-have-here-is-a-string></what-we-have-here-is-a-string>
    </our-friggin-awesome-internal-format>
    

    I should probably have less alcohol before replying to stuff... 😉

  10. sky5walk commented on Dec 1, 2017

    @sky5walk

    Is there a new build with mgrojo's changes?

  11. justinclift commented on Dec 1, 2017

    @justinclift
    Member

    @MKleusberg and @justinclift How do you see this for a commit?

    I'll defer to @MKleusberg for now. My head is getting back into Golang programming after a bit of a break, and my C++ is nearly non-existent now. (C++ was prior to Go for me, and my head doesn't seem to contain more than 1 language well 😦)

  12. MKleusberg commented on Dec 1, 2017

    @MKleusberg
    MemberAuthor

    @mgrojo Looks good, feel free to commit that. Maybe check for tabs (and line breaks) in the header names, too, because SQLite allows tabs in column and table names. Or just change the TODO comment so it mentions that we could add this later 😉

  13. MKleusberg commented on Dec 1, 2017

    @MKleusberg
    MemberAuthor

    @justinclift I thought about XML, too 😉 But it's such an annoying format to read and write that I honestly prefer the hack-ish internal buffer solution 😀

    @sky5walk We definitely need to have a way to copy and paste binary data, too. The code is only in there because some people asked for it. It's also a common use case to copy entire rows or parts of them from one table to another or inside the same table and this should work for all cells no matter their content. But that's what the internal buffer is for. So that shouldn't affect the quoting problem at all 😃

  14. mgrojo commented on Dec 1, 2017

    @mgrojo
    Member

    @sky5walk you can try the next nightly build that only puts quotes around data containing end-of-line characters, tabs or quotes.

    @MKleusberg Another option would be to copy always to the system clipboard, replacing binary data with BLOB or some ASCII encoding, and, at the same time, saving in the internal buffer. The way to coordinate the system clipboard and the internal buffer would be with a different and proprietary MIME format that wouldn't contain the data, but some kind of reference as if saying 'hey since you copied this data to clipboard, you can still paste your internal data'. That could be a checksum, a timestamp, sequence number or any hardcoded string (I think there will be no need for any check, but it could be used, just in case). Whenever other application writes to the clipboard, it would overwrite our MIME data (application/x-sqlitebrowser, e.g.), allowing us to copy/paste from other applications.

  15. 40 remaining items

  16. mgrojo commented on Jul 25, 2018

    @mgrojo
    Member

    With my last commit for this issue (65c670a) the copy and paste inside the application is broken for multi-line cells. This is due to not attaching the HTML version but only the unquoted text version of the cell data. I want t fix that and a direct approach is to not differentiating the single cell case for the HTML version, but only for the text version.

    But I'm starting to consider removing all the quoting altogether. I think the HTML version is already providing good compatibility with spreadsheet applications (which seems to me now the only reason to make the quoting and escaping) and it is also the mechanism for recognizing our own copies so we use use the internal buffer, so the copy is perfect inside the application. A clean text version (even though being a data-loss version) seems also the best approach for applications like plain text editors, which won't understand nor use the rich-format HTML version, but should be provided with a readable version of the data.

    @justinclift @MKleusberg @chrisjlocke and users involved in this issue, any opinions on that are welcome. Is anybody depending on the quoting of the text version? How is the clipboard text version being used?

  17. MKleusberg commented on Sep 28, 2018

    @MKleusberg
    MemberAuthor

    I just pushed a commit which implements @mgrojo's suggestions as I understand them. We're now never quoting cells when copying them into the text clipboard and we're now using our internal buffer for single cells as well. The only exception to this is when copying a single cell which contains an image because graphics programs don't support the HTML embedded image we're creating.

    I think this also answers the original question why we're quoting the clipboard contents. We only did that for two reasons:

    1. Allow pasting the data again without any losses. This was made obsolete a long time ago by the internal buffer.
    2. Allow pasting the data in spreadsheet application. This was made obsolete some time ago by the HTML clipboard.

    So originally it was needed but not anymore. Also the new solutions are far superior. So I guess Manuel is right that for the text mode we can stop quoting and just copy the simple data as it is.

    Anybody wants to give the new code a try? Can we close this issue then? 😄

  18. mgrojo commented on Sep 28, 2018

    @mgrojo
    Member

    Yes, you've understood my arguments and the implementation is exactly as I expected.

    Moreover, I've tested a bit and everything seems to work as expected.

  19. MKleusberg commented on Sep 28, 2018

    @MKleusberg
    MemberAuthor

    @sky5walk Do you want to give tomorrow's nightly build another try? We believe it should be working as expected now - but you never know 😄

  20. sky5walk commented on Sep 28, 2018

    @sky5walk

    Yes, will do. Thanks!

  21. pamtbaau commented on Sep 28, 2018

    @pamtbaau

    I'll have a look at it too.

  22. MKleusberg commented on Sep 28, 2018

    @MKleusberg
    MemberAuthor

    Cool, thanks to both of you 👍 I'm almost sure I have missed something because there were so many comments on this issue 😉

  23. pamtbaau commented on Sep 29, 2018

    @pamtbaau

    I've compared copy/paste behaviour between nightly Sep 28 2018 and Sep 29 2018.

    • Copying multi rows/columns into text editor:
      • Sep 28 2018: Cells containing tabs are quoted, columns separated by tabs
      • Sep 29 2018: Cells containing tabs are NOT quoted, columns separated by tabs
        This is undesirable, since columns are now no longer distinguishable.
    • Copying multi rows/columns into LibreOffice/Calc:
      • Sep 28 2018: Tabs are converted into 7 spaces
      • Sep 29 2018: Tabs are converted into 7 spaces
        I think this is not desirable
    • Copy single cell in select mode into LibreOffice/Calc:
      • Sep 28 2018: Paste buffer is empty
      • Sep 29 2018: Tabs are converted into 7 spaces
        I think this is not desirable
    • Copy single cell in edit mode into LibreOffice/Calc:
      • Sep 28 2018: Tabs are split into multiple columns
      • Sep 29 2018: Tabs are split into multiple columns
        I think this is not desirable
  24. mgrojo commented on Sep 29, 2018

    @mgrojo
    Member

    Thanks for the report, @pamtbaau.

    • Copying multi rows/columns into text editor:

    This is expected. The text version is not considered for transfering data losslessly. For that you should use the HTML version. Which is the use case that you are imagining? Do you want to parse the data from a program or only get a text version of the data for including in a text file? If you want to parse, use the HTML version from your program, it shouldn't be too hard to parse. Another option is the CSV export, which is also lossless. If you want to get a text version of the data for a human-readable file, all the quoting and escaping is counterproductive and some users have here complained. I guess we cannot satisfy all users 😄

    • Copying multi rows/columns into LibreOffice/Calc:
    • Copy single cell in select mode into LibreOffice/Calc:
    • Copy single cell in edit mode into LibreOffice/Calc:

    All of these are unepexted and doesn't match with my tests with LibreOffice/Calc. It is suppose to get the HTML version and consequently all the data is inserted as a table and all the data (except non-image blobs) are preserved in the pasting. Which version are you using and how do you exactly paste? Just Ctrl+C and Ctrl+P or via the special copy menu option?

  25. pamtbaau commented on Sep 29, 2018

    @pamtbaau

    I'm afraid I have been out of touch with SqliteBrowser for some time and may have missed something...

    For that you should use the HTML version.

    What is the HTML version?

    In my tests, I am using SqliteBrowser (latest nightly) and copy from the "Browse Data" panel. On right-click, the only options I see are "Copy", "Copy with Headers" and "Copy as SQL". I used plain "Copy" (Ctrl+C) and paste using Ctrl+V in my tests

    NB. I'm using Windows 10

  26. mgrojo commented on Sep 29, 2018

    @mgrojo
    Member

    What is the HTML version?

    The clipboard have a text and an HTML version in all the options that you've referred to, except the "Copy as SQL" where it wouldn't make sense. The receiving application is free to choose the version it prefers. LibreOffice should be using the HTML version. At least the Linux version does that. I'm now under Windows. I'll try to reproduce the problem with LibreOffice.

  27. mgrojo commented on Sep 29, 2018

    @mgrojo
    Member

    I've tested with both Excel 2013 and LibreOffice 5.2.3.3. They are using the HTML when copying from our table browser. If you copy from the Cell Editor or from the editor mode inside the table, Qt copies a text version only. So I understand now your comments:

    Tabs are split into multiple columns

    This seems to be what these spreadsheets do. I think we cannot do anything about it, because even copying from notepad have that behaviour.

    Tabs are converted into 7 spaces

    Again, they are converting tabs to spaces when copying from other applications like a simple notepad, so we cannot do anything about it.

  28. mgrojo commented on Feb 8, 2019

    @mgrojo
    Member

    Closing this as we considered it solved in the new v3.11.0 release. If you consider there are still some rough edges, you are welcome to reopen this again or open a new issue as you see fit.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

Type

No type

Projects

No projects

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions