I've googled this but all the examples are on an old excel. My current excel does not have the option of MySQL on the Data tab. i tried importing through Other Source, SQL Server but it doesn't connect through Server name. I don't understand why or if i'm putting in the wrong Server name. Should it be something other than localhost?
3 Answers
You cannot import an excel file in MySQL Workbench. That was never possible as it would require to parse the excel file. Instead export your excel file in Excel to CSV and import that in MySQL Workbench.
Importing CSV into MySQL via MySQL Workbench is easy. Open the Table Data Import Wizard from the schema tree:
It allows you to import CSV and JSON data. Select your file on the next page, set import options too (e.g. separator and quote char) and then let it run.
You can copy paste your Excel data in the result grid of Mysql Workbench.
Simply select in your Excel sheet all the rows and columns you want to insert in your database and copy them.
Copying cells containing formulas works, but pay attention to:
- disable the thousand separator for numbers;
- change the decimal separator to be a dot for numbers;
- avoid empty cells as they trigger an error that the column number does not match.
After having copied the cells, right-click in Mysql Workbench on your table and choose "Select Rows" and the results of your query will appear in a result grid.
Right-click in the result grid in the empty row at the bottom and choose "Paste Row" from the menu.
Then click on "Apply" and confirm the insert queries. If everything goes well, your data will be inserted. Otherwise MySQL errors (constraint violations, etc.) are displayed. These errors can help you debugging what went wrong and where you need to correct your Excel data.
For me this works well and I didn't encounter any encoding issues yet.
However there are performance issues if you want to paste large datasets.
Easiest option is to use a dedicated application like Excel2MySQL. It's fast and more thorough for complexities like embedded quotes or special characters. Especially if you have millions of records to import because copy/paste has limitations.
I also recommend HeidiSQL over MySQL workbench because it is also more intuitive. I use workbench for server administration tasks and HeidiSQL for database and table administration.
Related