How to import excel file into MySQL Workbench?

Question

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?

Rowley

Answer 1

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.

Veteran

I understand this, I already have it in CSV form. I guess my question was, how do you import a CSV into MySQL. I still don't understand how to do this. – Rowley

Answer 2

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.

Hanson

easiest way ! Thanks. – Oddson

Could you post a screenshot of your Excel table? Do we need to create the unique index column in Excel before copying and pasting? This answer is incomplete. – Hazem

@Hazem I updated my answer. You do not need to create anything in Excel, just copy the raw cell values. – Hanson

The problem was that not every cell I was copying from Excel had stuff in it. For some reason Workbench freaks out if you are pasting cells without any content in them. Once I figured that out things progressed much more smoothly. – Hazem

Answer 3

You can first export your excel data to a CSV file. Then you can import them with the following sql command; this approach works well and does not have problems with special characters (which the import wizard does):

LOAD DATA INFILE "C:\\ProgramData\\MySQL\\MySQL Server 8.0\\Uploads\\name_of_your_file.csv"
INTO TABLE name_fo_your_db_table
CHARACTER SET utf8mb4
FIELDS TERMINATED BY '\t' ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(column1, column2, column3)
SET id = NULL;

Notes to some lines

The path to your csv file: You need to put the file to this folder because it needs to have specific privileges, otherwise you will into an error. You can find out which folder has the right privileges by running this command: SHOW VARIABLES LIKE "secure_file_priv";

IGNORE 1 LINES will skip the first line of your csv data; it is useful if your data in excel had a heading. If not, just delete this line.

(column1, column2, column3) expects that your database table has got three columns named column1, column2 and column3; if it has more or fewer columns, add or delete items from this line respectively. And of course, change the names according to your table.

SET id = NULL: If your DB table has a column for the primary key (which will be added/filled in automatically) but your excel table does not, use this line (ie. your db table has one column more than the excel table)

Circlet

Answer 4

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.

Estrous

"Some records loaded BLANK because software is not licensed." – Ambur

Excel2MySQL is not free, but the license is worth the cost to avoid data translations problems and headaches. – Estrous