Several ways to import Excel data into a MySQL database

Several ways to import Excel data into a MySQL database

"My Interview Sentiment" award essay contest results announced!

The first few days need to import the Excel table data into the MySQL database, on-line search for half a day, study a half-day, summed up the following methods, the following and you share:

One. Use Java to transfer data from Excel tables to MySQL

This is the first method we use, that is, in Java to thank you for a class, and then this class will be in the Excel table data stored in memory, and then read from memory inserted into the database, but note that this is stored in the string[] array inside, So the data taken out is also a string type, if your table has an int type of data, then you must take that one out and then cast, if your data table structure is not so complex, relatively simple, then you can use this method, if the structure of the field is complex that can also be used, Just slowly turn, write more code, not afraid of tired comrades can try. Here is the code I looked up from the Internet:

1. Add the POI jar package to the project's Lib directory

2. Excel file directory: D://excel.xls

3. Database field: Num1 num2 num3 num4 num5 num6

4. Database name: Blog

5. Table Name: Test

6. Writing class: String method to connect MySQL, insert method, entity class

Import Java.io.FileInputStream;

Import java.io.FileNotFoundException;

Import java.io.IOException;

Import Org.apache.commons.logging.Log;

Import Org.apache.commons.logging.LogFactory;

Import Org.apache.poi.hssf.usermodel.HSSFCell;

Import Org.apache.poi.hssf.usermodel.HSSFRow;

Import Org.apache.poi.hssf.usermodel.HSSFSheet;

Import Org.apache.poi.hssf.usermodel.HSSFWorkbook;

public class Testexcel {

Record the output information of a class

Static log log = Logfactory.getlog (Testexcel.class); ­

Get the path to an Excel document

public static String FilePath = "D://excel.xls";

public static void Main (string[] args) {

try {

Create a reference to an Excel workbook file

Hssfworkbook Wookbook = new Hssfworkbook (new FileInputStream (FilePath));

In an Excel document, the default index for worksheet one is 0,

Its statement is: hssfsheet sheet = workbook.getsheetat (0);

Hssfsheet sheet = wookbook.getsheet ("Sheet1");

Gets the number of rows in the Excel file

int rows = Sheet.getphysicalnumberofrows ();

Traversing rows

for (int i = 0; i < rows; i++) {

Read upper left Cell

Hssfrow row = Sheet.getrow (i);

Line is not empty

if (row! = null) {

Get all the columns in the Excel file

int cells = Row.getphysicalnumberofcells ();

String value = ""; ­

Traversing columns

for (int j = 0; J < cells; J + +) {

Gets the value to the column

Hssfcell cell = Row.getcell (j);

if (cell! = null) {

Switch (Cell.getcelltype ()) {

Case Hssfcell.cell_type_formula:

Break

Case Hssfcell.cell_type_numeric:

Value + = Cell.getnumericcellvalue () + ","; ­

Break ­

Case hssfcell.cell_type_string:

Value + = Cell.getstringcellvalue () + ",";

Break

Default

Value + = "0";

Break

}­

}       ­

}­

Inserting data into the MySQL database

String[] val = Value.split (",");

testentity entity = new testentity ();

Now start Data Conversion!!!!!! *************************************************

Double A = double.parsedouble (val[0]);

Long code = (long) A;

ENTITY.SETNUM1 (code);

Entity.setnum2 (val[1]);

ENTITY.SETNUM3 (val[2]);

ENTITY.SETNUM4 (Val[3]);

ENTITY.SETNUM5 (Val[4]);

ENTITY.SETNUM6 (Val[5]);

TestMethod method = new TestMethod ();

Method. ADD (entity);

}­

}­

} catch (FileNotFoundException e) {

E.printstacktrace ();

} catch (IOException e) {

E.printstacktrace ();

}­

}­

}­

Our database is too complex, so this method bye-bye!!!

Two. Manual Excel data and MySQL data conversion

This method is also found from the Internet, more than the first one to be simple, a command to do, the following copy to everyone:

If you want to import the Excel table into the MySQL database,

，

Step One:

Select the data you want to import, plus one more column, such as:

Step Two:

Copy the selected data to a new table workbook and then "Save as"-"Text File (tab split) (*.txt)" If it is stored in the "d:data.txt" position.

Step Three:

Create a MySQL database and table based on the data you want to import, and then use the command

Load data local infile ' d:data.txt ' into table exceltomysql fields terminated by ' t ';

The import operation. Such as:

Now that the data has all been imported into MySQL,

Let's select a little bit.

To this, the data from Excel to MySQL conversion has been completed.

Next, the data from MySQL to Excel conversion, the process is actually the use of the "tab split" text file.

You can do this if you want to import the boys ' information from this table into Excel.

SELECT * into outfile ' d:man.txt ' from exceltomysql where xingbie= "male";

In this way, all the boys ' information in the table is output to a text file in the D:man.txt file that ends with a tab segment, ' N '.

You can open Excel and choose Import data, import external data, and select D:man.txt to make sure it's all right.

Well, this method we have too, the feasibility is OK, but we still encountered a problem, that is when we import the data of Chinese characters, the database of that field is not displayed, there is a little garbled, but many are empty, do not know why, the property of the field is varchar, yes, So this problem is not solved, we have to change!!! Know how to tell the classmates, thank you first!!!

Three. Software Import

Haha, this method is the most cool, we finally found a so convenient east, the name is called "Xls2sql", we go to Baidu can also search, down the use is, but the problem is followed Ah, really super depressed!! Is that you in the data when he will be with your Excel automatic table, and then the field properties are also very smart to help you complete, is a fool software, but our table has been built, the field properties have been completed, it is not so obedient we have to find another way, helpless ah ...

Four. csv Import

This is the way we finally do, is that you first export the empty CSV data table from the MySQL database, and then copy the data in Excel into the corresponding CSV table, and then use MySQL to import the CSV data table ... But here is also a little trouble, that is sometimes when the CSV table saved when he will be wrong, you open the time maybe he will put more data into the first column, there is no relationship, in the MySQL when inserting data to choose the TAB key ~ ~, if the data in the CVS is normal, then there is nothing to choose , the default selection when inserting is the character ....

Several ways to import Excel data into a MySQL database