How to import data into MySQL using Excel

How to import EXCEL content into mysql

There are many methods, but I suggest you read the mysql development documentation first, which is very detailed. If you are too lazy to read, you can read the following

1. There is software PHP Excel Parser Pro v4.2, you can download it here:

2. Excel can be saved in csv format. Then pour mysql

through phpmyadmin

3. First import it into Access, and then get it into MySQL, or write your own program to read the data in excel and then store it in mysql

4. There is also a relatively stupid manual method, which is to use excel to generate sql statements first, and then run them in mysql. This method is suitable for importing excel tables into various sql databases:

1. Suppose your table has three columns of data A, B, and C. You want to import it into the table table in your database. The corresponding fields are col1, col2, and col3

2. Add a column to your table and use excel formulas to automatically generate sql statements. The specific method is as follows:

1. Add a column (assuming it is column D)

2. Enter the formula in column D of the first row, which is D1:

=CONCATENATE("insert into table (col1,col2,col3) values ​​('＂,A1,＂','＂,B1,＂','＂,C1,"');＂)

3. At this time, D1 has generated the following sql statement:

insert into table (col1,col2,col3) values ​​('a','11','33');

4. Copy the formula of D1 to column D of all rows (just click and hold the lower right corner of cell D1 with the mouse and drag it down)

5. At this time, column D has generated all sql statements

6. Copy column D to a plain text file, assuming it is sql.txt

3. Just put sql.txt into the database and run it. You can import it using the command line or run it using phpadmin.

How to import EXCEL data table into SQL

After entering the data in Excel, you may need to import it into the database. At this time, you need to use some skills to import.

How to import excel table into database:

1. For storing large amounts of data in a database, it is best to use graphical database management tools. However, if there are no tools and you can only execute commands, this will be very time-consuming. You can only combine the data, form the data into an insert statement, and then execute it in batches on the command line.

2. Combine the following data, which uses a function in excel.

There is an fx input box in excel, just fill in the assembled string here.

Note: String 1 & A2 & String 2 & ...

A2 can be entered directly, or you can use the mouse to click on the corresponding cell.

3. Use the & symbol to connect each string. The following is the first connection string

=＂insert into tavern values('＂&A2&＂','＂&B2&＂','＂&C2&＂','＂&D2&"');＂

After writing, press Enter to combine the corresponding string.

insert into tavern values('jw_agi','Shuguang','0','1');

4. After arranging the strings in the first row, place the mouse on the lower right corner of the cell. A bold cross will appear. Click and press (don’t let go), and keep pulling down until the last row is reached. That's it.

5. In this way, all the insert statements have been sorted out. The next step is to execute these statements in batches.

How to import data from excel into mysql database

1. If there are four columns of data in excel and you want to import it into mytable in the database, the corresponding fields are field1, field2, field3, field4.

2. Add a column to the excel table (?excel should be column E), and use excel formulas to automatically generate SQL statements (this is very important, don’t write it wrong). The specific method is as follows? ：

1. Add a column (excel should be column E, because we have 4 original columns of data, namely columns A\B\C\D)

2. In column E of the first row (this row must be a valid data row otherwise an error occurs), enter the formula =CONCATENATE("insert into table (field1,field2,field3,field4) values ​​('", A1,＂','＂,B1,＂','＂,C1,＂','＂,D1,"');")

The above is the detailed content of How to import data into MySQL using Excel. For more information, please follow other related articles on the PHP Chinese website!