Import Data from Txt or CSV files into MYSQL database tables

最新推荐文章于 2025-05-16 13:53:37 发布

转载最新推荐文章于 2025-05-16 13:53:37 发布 · 1k 阅读

文章标签：

#csv #mysql #import #database #string #query

本文介绍如何使用MySQL的LOAD DATA INFILE功能从txt文件批量导入数据到数据库表中。通过一个具体示例展示了连接数据库、执行导入操作的全过程，并提供了一段完整的Java代码实现。

Mysql-connector-java-3.1.10 is a JDBC connector for MYSQL database. MYSQL provides LOAD DATA INFILE utility to import data from files like csv, txt or xls into database tables.

The example below imports data from .txt file into table.

temp.txt file is a tab separated file:

"1 string"      100
"2 string"      102
"3 string"      104
"4 string"      106

testtable structure
CREATE TABLE testtable
(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
text varchar(45) NOT NULL,
price integer not null);

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.SQLException;

public 
        
         class
         automateImport
{
    public static void main(String[] args) 
    {
        DBase 
       
        db
        = new DBase();
        Connection conn = db.connect(
    "jdbc:mysql://localhost:3306/test","root","caspian");
        db.importData(conn,args[0]);
    }

}

class DBase
{
    public DBase()
    {
    }

    public Connection connect(String db_connect_str, 
  String db_userid, String db_password)
    {
        Connection conn;
        try 
        {
            Class.forName(  
    "com.mysql.jdbc.Driver").newInstance();

            conn = DriverManager.getConnection(db_connect_str, 
    db_userid, db_password);
        
        }
        catch(Exception e)
        {
            e.printStackTrace();
            conn = null;
        }

        return conn;    
    }
    
    public void importData(Connection conn,String filename)
    {
        Statement stmt;
        String query;

        try
        {
            stmt = conn.createStatement(
    ResultSet.TYPE_SCROLL_SENSITIVE,
    ResultSet.CONCUR_UPDATABLE);

            query = "LOAD DATA INFILE &apos;"+filename+
    "&apos; INTO TABLE testtable (text,price);";

            stmt.executeUpdate(query);
                
        }
        catch(Exception e)
        {
            e.printStackTrace();
            stmt = null;
        }
    }
};

If you want to import a CSV file, you can use the following query:

query = "LOAD DATA INFILE &apos;"+filename+"&apos; INTO TABLE testtable  FIELDS
 TERMINATED BY &apos;,&apos; (text,price)";