You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
{{ message }}
Repository navigation
Why are we quoting the clipboard contents when copying from a table view? #1244
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 😄
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.
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. 😀
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.
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.
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>");
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:
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 😦)
@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 😉
@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 😃
@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.
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?
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:
Allow pasting the data again without any losses. This was made obsolete a long time ago by the internal buffer.
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? 😄
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?
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
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.
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.
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.
Activity
Clean up copy & paste code
mgrojo commentedon Nov 30, 2017
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.
mgrojo commentedon Nov 30, 2017
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.
justinclift commentedon Dec 1, 2017
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. 😀
MKleusberg commentedon Dec 1, 2017
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 😦
sky5walk commentedon Dec 1, 2017
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.
mgrojo commentedon Dec 1, 2017
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.
@MKleusberg and @justinclift How do you see this for a commit?
justinclift commentedon Dec 1, 2017
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:
I should probably have less alcohol before replying to stuff... 😉
sky5walk commentedon Dec 1, 2017
Is there a new build with mgrojo's changes?
justinclift commentedon Dec 1, 2017
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 😦)
MKleusberg commentedon Dec 1, 2017
@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 😉
MKleusberg commentedon Dec 1, 2017
@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 😃
Quote only data with end-of-lines, tabs or double-quotes
mgrojo commentedon Dec 1, 2017
@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.
40 remaining items
mgrojo commentedon Jul 25, 2018
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?
Never quote cells when copying them to clipboard in text mode
MKleusberg commentedon Sep 28, 2018
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:
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? 😄
mgrojo commentedon Sep 28, 2018
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.
MKleusberg commentedon Sep 28, 2018
@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 😄
sky5walk commentedon Sep 28, 2018
Yes, will do. Thanks!
pamtbaau commentedon Sep 28, 2018
I'll have a look at it too.
MKleusberg commentedon Sep 28, 2018
Cool, thanks to both of you 👍 I'm almost sure I have missed something because there were so many comments on this issue 😉
pamtbaau commentedon Sep 29, 2018
I've compared copy/paste behaviour between nightly Sep 28 2018 and Sep 29 2018.
This is undesirable, since columns are now no longer distinguishable.
I think this is not desirable
I think this is not desirable
I think this is not desirable
mgrojo commentedon Sep 29, 2018
Thanks for the report, @pamtbaau.
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 😄
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?
pamtbaau commentedon Sep 29, 2018
I'm afraid I have been out of touch with SqliteBrowser for some time and may have missed something...
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
mgrojo commentedon Sep 29, 2018
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.
mgrojo commentedon Sep 29, 2018
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:
This seems to be what these spreadsheets do. I think we cannot do anything about it, because even copying from notepad have that behaviour.
Again, they are converting tabs to spaces when copying from other applications like a simple notepad, so we cannot do anything about it.
mgrojo commentedon Feb 8, 2019
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.