Test: Import Excel table data to MySQL database

Source: Internet
Author: User

recently to develop a small answer system, if the topic manual input that is really troublesome. So think about whether you can import data from a file with some existing data format. Look it up online and see a way to import Excel data into MySQL. So the question bank data is organized into Excel.  






workaround: I opened the file in. csv format with EditPlus ("Encode" is selected as system default when open), and then saved as a file encoded as Utf-8. (I am only using editplus to do this operation, if there are other feasible and convenient software, friends can also message exchange)  
         

All options are default, the import succeeds, but the data table is a new table and is indicated as table 2 (there is a table in the database), and the table field is named Col1,col2 ......



after the inspection, when the data is imported, when you finish selecting the file, you will be prompted as follows


Tick the box and click on Execute.
The field name of the imported data table is set by the first line in Excel.


Although the data was imported into MySQL, but I still have a slight deviation from my original purpose, I would like to import the data in the Excel table into the corresponding table in the database.

After careful analysis, it is possible that the problem with the catalog level is when you choose to import.
The first two select import operations, are operating under the database.

this time, under localhost, select the Import operation under table.
 
        
one comes up with an error, but looks like the location of the data insertion is right. As prompted, the error is the primary key column. When I create a new table, the primary key column is set to self-increment.
try removing the self-increment attribute first.
still error.
then remove the primary key and try again.

  

The data is finally plugged in, but the first row name in the Excel table is also inserted into the database as input. And the number is starting from 0, which may be the cause of the error just now.
To find out the real cause, re-test. Delete the table to re-create an empty table.
Open a. csv file that was previously saved as a utf-8 encoding, and delete the column name row and save the file.


Smooth insertion, done, roar.

Test: Import Excel table data to MySQL database

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.