How to upload csv file into MySQL Database Example
Comma-Seperated-Value(CSV) file is a common file that used by most developers to extract data from database. Today i want to show how to upload or insert CSV file into database MySQL directly without need to read line by line in the CSV file.
Let say this is the example of CSV file
The above csv file are two example of csv files content. Lets start learn how to upload this file into MySQL Database.
MySQL have a powerful sql statement to read this file and insert into database as batch. This statement is the fastest way to insert into database. You can read more about LOAD DATA INFILE Syntax Here
By Mohd Zulkamal
NOTE : – If You have Found this post Helpful, I will appreciate if you can Share it on Facebook, Twitter and Other Social Media Sites. Thanks =)
Let say this is the example of CSV file
CSV File Content,Example 1
NO;DATE;TIME;ACCNO;STATUS
1;20131211;100856;12345;ACTIVE
2;20131211;095625;25896;NOT ACTIVE
CSV File Content, Example 2
"NO";"DATE";"TIME";"ACCNO";"STATUS"
"1";"20131211";"100856";"12345";"ACTIVE"
"2";"20131211";"095625";"25896";"NOT ACTIVE"
The above csv file are two example of csv files content. Lets start learn how to upload this file into MySQL Database.
MySQL have a powerful sql statement to read this file and insert into database as batch. This statement is the fastest way to insert into database. You can read more about LOAD DATA INFILE Syntax Here
For example 1, you need to use this sql statement :
LOAD DATA LOCAL INFILE '<file Name with path>' INTO TABLE <table name>
FIELDS TERMINATED BY ';'
FIELDS ENCLOSED BY ''
LINES TERMINATED BY '<\r\n - For WINDOWS, \n - For LINUX>'
IGNORE <row line no> LINES
Example 2 sql Statement
LOAD DATA LOCAL INFILE '<file Name with path>' INTO TABLE <table name>
FIELDS TERMINATED BY ';'
FIELDS ENCLOSED BY '"'
LINES TERMINATED BY '<\r\n - For WINDOWS, \n - For LINUX>'
IGNORE 1 LINES
Here is the full method
public Connection TestConnectionDb() {
String DbConnectionString ="jdbc:mysql://localhost:3306/blog";
//or you can directly connecto to your database schema like this :
//String DbConnectionString ="jdbc:mysql://localhost:3306/<schema name>";
String User = "root";
String Password = "P@ssw0rd";
Connection connection = null;
try {
Class.forName("com.mysql.jdbc.Driver");
connection = DriverManager.getConnection(DbConnectionString, User, Password);
} catch (ClassNotFoundException e) {
System.out.println("MySQL JDBC Driver not found");
} catch (SQLException ex) {
System.out.println("Connection Failed! Err Msg : " + ex.getMessage());
}
if (connection != null) {
System.out.println("You made it, take control your database now!");
} else {
System.out.println("Failed to make connection!");
}
return connection;
}
public void UploadcsvFileIntoTable(String TableName, String FileNamePath) {
Statement stmt = null;
Connection dbConn = TestConnectionDb();
try {
String strsql = "<replace with sql statement given>";
stmt = dbConn.createStatement();
stmt.executeUpdate(strsql);
} catch (SQLException e) {
System.out.println(e.getMessage());
} finally {
try {
if (stmt != null) {
stmt.close();
}
} catch (SQLException se) {
System.out.println(se.getMessage());
}
try {
if (dbConn != null) {
dbConn.close();
}
} catch (SQLException se) {
System.out.println(se.getMessage());
}
}
}
By Mohd Zulkamal
NOTE : – If You have Found this post Helpful, I will appreciate if you can Share it on Facebook, Twitter and Other Social Media Sites. Thanks =)
