PhpMyAdmin batch import Excel content to MySQL

I have previously published the article "batchSQL, a batch SUBSTITUTION OF EXPRESSION variables". Some children's shoes say that the csv import function provided by phpMyAdmin is not better for data import. Indeed, it is very good to use this function to import data into mysql. However, if you want to perform batch update or other operations, for example, to update a part of data in batches from a new excel file, you cannot delete all the tables to be rebuilt. The biggest advantage of the small software is that it is random, because SQL commands can be written by ourselves.

Now I will introduce how to import Excel content to MySQL in batches using phpMyAdmin. First, you need to know what phpMyAdmin is (skip this article if you do not know it). The version I use today is phpMyAdmin 3.2.4 and MySQL version is 5.1.41.

1. In the first step, we got an excel table containing a lot of data that needs to be imported.

2. Delete row 1st "Admission Ticket No." XXX "... only the data section we need.

3. Click "file" -- "Save as" and select "CSV (separated by commas) (*. csv)" as the type to save the excel table as a csv file. No matter what the prompt is, "Yes" is enough...

4. Key points! For example, slyar.csv can be opened in a text editor such as NotePad or Editplus. Let's take a look.

You can see that the csv file is actually a txt file (that is, you can use your own txt file and change the suffix), but the fields are separated by commas. Since it is a txt file, it involves the file encoding problem! The default file encoding is ANSI. If your database (data table) uses UTF-8 encoding, be sure to save this csv file as UTF-8 format!

5. Go to phpMyAdmin to create a table and create a new field. The field name is associated with the field of the excel table you want to import and the order is the same. It's too simple.

6. Open the table you created in phpMyAdmin and click "import" at the top ".

7. Select the second CSV file to use load data for "format of the imported File ".

8. Select the csv file you saved as for "file to be imported.

9. "field separator" changed to ",", that is, save excel as the default "(separated by commas)", you can freely select the separator, generally, "," appears in your excel table data.

10. "field name" is an advanced application. The field name here is the field name in your data table. It indicates the fields you want to fill in. Separate multiple fields. If it is null, it indicates that all data is filled, and the data is filled in order. No extra data is required.

10. If the check is correct, you can press "execute.

11. "Import successful, 1 query executed ". If you see this, it indicates that the import is successful. If the import is not successful, redo it from start to end. If not, read it...

12. Some excel-exported data may become very strange. For example, there may be special characters in the middle. If this method doesn't work, you can still use the software I wrote before...