How to import an Excel table into MySQL

In peacetime work study, inevitably encounter need to import Excel table data into MySQL, for example, to check the data in Excel, or to import test cases into Testlink. I collect the relevant information and practice to summarize the following methods: 1. UsePHP Excel Parser Pro software, but this software for paid software;
2. You can save the Excel table in CSV format and then import it via phpMyAdmin or SQLyog, SQLyog the method to import:• Save Excel tables in CSV format;• Open SQLyog, right click on the table to be imported, click "Import"-"Import using local CSV data loading";• In the dialog box that pops up, click "Change ..." and select "Fill Excel Friendly Value" and click OK;• In the "Import from File" Select the CSV file path to import, click "Import" to import data into the table;
3. A rather clumsy manual method is to use Excel to generate SQL statements and then run them in MySQL, which is suitable for importing Excel tables into various SQL databases: • Suppose your table has a, B, c three columns of data that you want to import into your database table tablename, corresponding fields are col1, col2, col3

         • Add a column to your table and use Excel's formulas to generate SQL statements automatically, as follows:          1) Add a column (assuming D column)          2) in column D of the first row, that is, enter the formula in D1: =concatenate ("Insert   Into   tablename   (col1,col2,col3)   values   (", A1,", ", B1,", ", C1,"); ")          3) At this point, D1 has generated the following SQL statement: INSERT INTO   table   (col1,col2,col3)   values   (' A ', ' one ', ' a ');          4) Copy the D1 formula to column D of all rows (just drag down the lower right corner of the cell with the mouse D1)          5) At this point the D column has generated all the SQL statements          6) Copy the column D into a plain text file, assuming the Sql.txt         · Put the sql.txt in the database to run, you can use the command line to import, you can also run with Phpadmin.

How to import an Excel table into MySQL