Excel Friendly Copy/Paste

[expired user #5015]'s profile image [expired user #5015] posted 13 years ago in Feature discussion Permalink
I can't say this enough... thank you for creating such a great program.

One thing that would be very helpful for me and my coworkers would be an easy way to copy/paste between HeidiSQL and Excel. The only way to copy data from HeidiSQL to Excel currently is HTML (it doesn't recognize CSV on the clipboard), so I have to remove the formatting and the footer from the HTML data each time I paste. Conversely, there doesn't seem to be any way to paste data on the clipboard back into a table in HeidiSQL either without exporting to a CSV.

In my own applications, I paste data on the clipboard destined for Excel in TSV (Tab Separated Values) format, which it pastes very readily. Likewise, when copying data in Excel to the clipboard, the plain text version of the clipboard contains TSV data as well.

It certainly isn't practical to include the power of Excel in HeidiSQL, but it would certainly be nice to use a lot of its power in editing and manipulating database data. Being able to copy/paste TSV data would be a greatly appreciated feature.
ansgar's profile image ansgar posted 13 years ago Permalink
Copy+Paste for Excel already works, when you set the right control characters: Tools > Preferences > Export. Just set \t as field separator, \r\n as line separator.
[expired user #5015]'s profile image [expired user #5015] posted 13 years ago Permalink
I can see that that is used for copying data, but often I still need true CSV files with commas as delimiters as well. Treating the Copy and Export operations separately would be immensely helpful. And this doesn't seem to have anything to do with pasting into HeidiSQL. Am I wrong on that?
ansgar's profile image ansgar posted 13 years ago Permalink
Not wrong, there is no way to paste grid rows into HeidiSQL. I'm however always for the more SQL like approach: Save a CSV file and either use the "Import textfile" dialog in Heidi or manually execute a LOAD DATA command.
ansgar's profile image ansgar posted 13 years ago Permalink
Think I'm going to follow your proposal to include a separate "Excel TSV" action. In that case, the export should not contain the column names in a first row, right?
ansgar's profile image ansgar posted 13 years ago Permalink
Ah, even better: Not an additional "Copy as ..." action - instead I'll modify the "Copy as CSV" action in a way that it pops up and asks for delimiter, separator and encloser. With a radio button for "Excel friendly" and "User defined". Plus a checkbox asking for "Include column names as a first row". The dialog stores settings and restores them for the next call. More options? Encoding, probably.
[expired user #5015]'s profile image [expired user #5015] posted 13 years ago Permalink
Sounds great. Encoding isn't really an issue for me, but I could see it being one for others. Copying to the clipboard in Unicode should cover that, right?
[expired user #5015]'s profile image [expired user #5015] posted 13 years ago Permalink
Some might object to having a dialog pop up each time they select Copy as CSV... maybe making a separate Copy as TSV command would be a better way to go?
ansgar's profile image ansgar posted 13 years ago Permalink
Not necessarily, I don't think that users click that item that often so they are even annoyed of having the popup dialog asking for details. How often do you export grid data? I do it once or 5 times a day.
[expired user #5015]'s profile image [expired user #5015] posted 13 years ago Permalink
The new feature is working great. Thank you! Both I and my business partners really appreciate it.

One other thought on copy/paste... currently if you highlight multiple table cells and press Control+C (or right-click and select Copy), it only copies data from a single cell rather than the entire selected range. Would it make sense to copy everything that is highlighted, since the user has taken the time to create a selection before selecting the Copy option? One could define the default data format in the program options.

Just a thought.

Please login to leave a reply, or register at first.