How can I copy-and-paste Data into MySQL Workbench table?

Question

I've used MySQL Workbench and MS SQL Server Management Studio off & on over the years. The one thing I enjoy with SSMS is the ability to copy information from an Excel document and paste it into the results pane of SSMS. Is there a similar means of importing information into tables for MySQL Workbench? This method is quick and easy. Right now the way I do it for MySQL is I export from excel to CSV file, then import to MySQL from CSV file. Thanks!

Solution 1

There's no way to insert an entire Excel table into a result set in MySQL Workbench using only the clipboard. For this task your approach via a CSV file is the best one. There's an option to copy/paste entire rows, though, which might be of help for small changes.

Solution 2

Third Party Tool with CSV

If you are willing to use a third party tool, you can paste from an Excel worksheet select-copy or a CSV open in a text editor into this tool to generate INSERT INTO or UPDATE statements. It can also add the CREATE TABLE with appropriately sized VARCHARs as an option (among many other output options). You copy and paste from the tool directly into a blank SQL page in mySQL Workbench.

Third Party Tool with Regex

Alternatively, you can use a regular expression tool such as https://regex101.com to generate the SQL code. For example, a three field csv that's tab separated can be decoded by this regex

^([^\t]*)\t([^\t]*)\t([^\t]*)$

and then in the Substitution expression, this

INSERT INTO `myschema`.`mytable` SET `first_field`='$1', `second_field`='$2', `third_field`= TRIM('$3'), `outbox_sent_text` = 'Friday, December 04, 2020 12:28 PM', `inbox_received_text` = 'Fri, 4 Dec 2020 15:43:08 +0000' AS `NEW` ON DUPLICATE KEY UPDATE id=id;

Note this substitution uses the updated INSERT...ON DUPLICATE KEY UPDATE method that will replace the deprecated VALUES syntax

Copy the block of INSERTs into an empty Workbench SQL window, and run the query.

Third party not OK? Not a problem, works locally too

If sending this through a web site is not allowable, the same regex substitution can be done with a local script or local text editing tool (Notepad++, etc.).