mysqlexcelexportmysqlimport

Exporting to MySQL from Excel using "MySQL for Excel plugin" is very slow

I have data in excel sheet and have to export the data to MySQL. I am using the MySQL for Excel tool in Data tab to export the data to a new table.

MySQL Tool for Excel

I have a table with id (primary key) and name (unique, with index), around 1000 rows. Exporting it takes 3 - 5 minutes.

Why is it so slow? Any suggestions or tips on how I can speed up the export process? I have a sheet with 150,000 rows. So need some help..

Solution

  • Found one way to do it:

    1. Create the table from excel using MySQL plugin for Excel. It can be done manually, make sure to have the same columns in the excel and the table (order and data type).
    2. Before saving the sheet as a CSV, replace all comma (,) characters in the sheet with blank string; because the comma acts as separator for columns. Save the file as a .csv file.
    3. Open mysql command line tool and run the below sql to import data to the table. Replace the csv file path and table name.

    LOAD DATA INFILE "C:/data.csv" INTO TABLE mytable COLUMNS TERMINATED BY "," LINES TERMINATED BY "\r\n";

    This imported 155,000 columns in a matter of seconds.