Как импортировать данные из excel в mysql workbench

Обновлено: 07.07.2024

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?

185 1 1 gold badge 1 1 silver badge 10 10 bronze badges

3 Answers 3

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.

39.7k 14 14 gold badges 93 93 silver badges 149 149 bronze badges

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.

Can any one explain how to import a Microsoft Excel file in to a MySQL database?

For example, my Excel table looks like this:

12.2k 44 44 gold badges 108 108 silver badges 157 157 bronze badges +1 for a new and different idea. The processing is insanely slow, it first reads each row in file and then upload anything. It took about 15 mins to import 5.2K rows For some spreadsheets it can be slow, due to number of columns and rows. But, I blame microsofts com-object library which is what is used to read the spreadsheet. My program reads the spreadsheet as fast as the library will allow. The mysql inserts are very fast. If you can eliminate unnecessary columns before importing. that can help.

14 Answers 14

There's a simple online tool that can do this called sqlizer.io.

You upload an XLSX file to it, enter a sheet name and cell range, and it will generate a CREATE TABLE statement and a bunch of INSERT statements to import all your data into a MySQL database.

(Disclaimer: I help run SQLizer)

14.4k 8 8 gold badges 39 39 silver badges 50 50 bronze badges @doxsi it seems fine to me, what sort of problem did you have with it? @ChrisSchmitz we are super-cautious with security and wrote a blog post recently about how we operate SQLizer blog.sqlizer.io/posts/privacy-at-sqlizer

Export it into some text format. The easiest will probably be a tab-delimited version, but CSV can work as well.

Look half way down the page, as it will gives a good example for tab separated data:

FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\'

Check your data. Sometimes quoting or escaping has problems, and you need to adjust your source, import command-- or it may just be easier to post-process via SQL.

5,264 7 7 gold badges 37 37 silver badges 49 49 bronze badges 20.5k 4 4 gold badges 33 33 silver badges 49 49 bronze badges Note that this procedure requires that you first create the table, with the appropriate fields. @afk5min what do you mean by "poison data". All those tags and markup are very useful for the users that are going to obviously import that into another MS product.

Below is another method to import spreadsheet data into a MySQL database that doesn't rely on any extra software. Let's assume you want to import your Excel table into the sales table of a MySQL database named mydatabase .

Select the relevant cells:

Paste into Mr. Data Converter and select the output as MySQL:

Change the table name and column definitions to fit your requirements in the generated output:

If you're using MySQL Workbench or already logged into mysql from the command line, then you can execute the generated SQL statements from step 3 directly. Otherwise, paste the code into a text file (e.g., import.sql ) and execute this command from a Unix shell:

mysql mydatabase < import.sql

Other ways to import from a SQL file can be found in this Stack Overflow answer.

16.1k 1 1 gold badge 50 50 silver badges 43 43 bronze badges

There are actually several ways to import an excel file in to a MySQL database with varying degrees of complexity and success.

Excel2MySQL. Hands down, the easiest and fastest way to import Excel data into MySQL. It supports all verions of Excel and doesn't require Office install.

LOAD DATA INFILE: This popular option is perhaps the most technical and requires some understanding of MySQL command execution. You must manually create your table before loading and use appropriately sized VARCHAR field types. Therefore, your field data types are not optimized. LOAD DATA INFILE has trouble importing large files that exceed 'max_allowed_packet' size. Special attention is required to avoid problems importing special characters and foreign unicode characters. Here is a recent example I used to import a csv file named test.csv.

phpMyAdmin: Select your database first, then select the Import tab. phpMyAdmin will automatically create your table and size your VARCHAR fields, but it won't optimize the field types. phpMyAdmin has trouble importing large files that exceed 'max_allowed_packet' size.

MySQL for Excel: This is a free Excel Add-in from Oracle. This option is a bit tedious because it uses a wizard and the import is slow and buggy with large files, but this may be a good option for small files with VARCHAR data. Fields are not optimized.

185 1 1 gold badge 1 1 silver badge 10 10 bronze badges

3 Answers 3

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.

39.7k 14 14 gold badges 93 93 silver badges 149 149 bronze badges

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.

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.

Я гуглил это, но все примеры на старом Excel. Мой текущий Excel не имеет опции MySQL на вкладке "Данные". Я попытался импортировать через другой источник, SQL Server, но он не подключается через имя сервера. Я не понимаю, почему или если я вношу неправильное имя сервера. Должно ли это быть что-то кроме localhost?

Вы не можете импортировать файл Excel в MySQL Workbench. Это никогда не было возможно, так как это потребовало бы разбора файла Excel. Вместо этого экспортируйте файл Excel в Excel в CSV и импортируйте его в MySQL Workbench.

Импортировать CSV в MySQL через MySQL Workbench очень просто. Откройте мастер импорта табличных данных из дерева схемы:

Это позволяет импортировать данные CSV и JSON. Выберите файл на следующей странице, задайте параметры импорта (например, разделитель и кавычка) и дайте ему запуститься.

Вы можете скопировать и вставить данные Excel в таблицу результатов Mysql Workbench.

Просто выберите на листе Excel все строки и столбцы, которые вы хотите вставить в свою базу данных, и скопируйте их.

Затем в Mysql Workbench щелкните правой кнопкой мыши таблицу и выберите "Выбрать строки", и результаты вашего запроса появятся в таблице результатов.

Щелкните правой кнопкой мыши в таблице результатов в пустой строке внизу и выберите "Вставить строку" из меню.

Затем нажмите "Применить" и подтвердите запросы на вставку.

Для меня это работает хорошо, и я не сталкивался с какими-либо проблемами кодирования.

Однако существуют проблемы с производительностью, если вы хотите вставить большие наборы данных.

Читайте также: