How to Load CSV file into Database

Posted: May 22, 2013 in Java Posts, Java Posts, Java Tutorials
Tags: , , , , , , , , , , , , , ,
0

Deutsch: Logo der Christlich Sozialen Volkspartei

Loading CSV file into Database can be cumbersome task if your Database provider does not offer an out of box feature for this. Most of the time you’ll spend up in creating valid insert statements and putting up values escaping all special characters. Importing CSV files gets a bit complicated when you start doing things like importing files with description fields that can contain punctuation (such as commas or single-double quotation marks).

So here’s a simple Java Utility class that can be used to load CSV file into Database. Note how we used some of the best practices for loading data. The CSV file is parsed line by line and SQL insert query is created. The values in query are binded and query is added to SQL batch. Each batch is executed when a limit is reached (in this case 1000 queries per batch).

Import CSV into Database example

Let’s us check an example. Below is the sample CSV file that I want to upload in database table Customer.

employee.csv – Sample CSV file:

EMPLOYEE_ID,FIRSTNAME,LASTNAME,BIRTHDATE,SALARY
1,Dean,Winchester,27.03.1975,60000
2,John,Winchester,01.05.1960,120000
3,Sam,Winchester,04.01.1980,56000

The Table customer contains few fields. We added fields of different types like VARCHAR, DATE, NUMBER to check our load method works properly.

Table: Customer – Database table

CREATE TABLE Customer (
  EMPLOYEE_ID  NUMBER,
  FIRSTNAME    VARCHAR2(50 BYTE),
  LASTNAME     VARCHAR2(50 BYTE),
  BIRTHDATE    DATE,
  SALARY       NUMBER
)

Following is a sample Java class that will use CSVLoader utility class (we will come to this shortly).

Main.java – Load sample.csv to database

package net.viralpatel.java;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class Main {
    private static String JDBC_CONNECTION_URL =
            "jdbc:oracle:thin:SCOTT/TIGER@localhost:1500:MyDB";
    
    public static void main(String[] args) {
        try {
            CSVLoader loader = new CSVLoader(getCon());
            
            loader.loadCSV("C:\\employee.sql", "CUSTOMER", true);
            
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    private static Connection getCon() {
        Connection connection = null;
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
            connection = DriverManager.getConnection(JDBC_CONNECTION_URL);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return connection;
    }
}

In above Main class, we created an object of class CSVLoader using parameterized constructor and passed java.sql.Connection object.

Then we called the loadCSV method with three arguments. First the path of CSV file, second the table name where data needs to be loaded and third boolean parameter which decides whether table has to be truncated before inserting new records.

Execute this Java class and you’ll see the records getting inserted in table.

csv-load-java-database-example

The CSV is successfully loaded in database.

Let’s check the Utility class now. I strongly recommend you to go through below tutorials as the Utility class combines the idea from these tutorials.

  1. Batch Insert In Java – JDBC
  2. Read / Write CSV file in Java
  3. Check if String is valid Date in Java

The utility class uses OpenCSV library to load and parse CSV file. Then it uses the idea of Batching in JDBC to batch insert queries and execute them. Each CSV value is checked if it is valid date before inserting.

CSVLoader.java – Utility class to load CSV into Database

package net.viralpatel.java;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.util.Date;
import org.apache.commons.lang.StringUtils;
import au.com.bytecode.opencsv.CSVReader;
/**
 *
 * @author viralpatel.net
 *
 */
public class CSVLoader {
    private static final
        String SQL_INSERT = "INSERT INTO ${table}(${keys}) VALUES(${values})";
    private static final String TABLE_REGEX = "\\$\\{table\\}";
    private static final String KEYS_REGEX = "\\$\\{keys\\}";
    private static final String VALUES_REGEX = "\\$\\{values\\}";
    private Connection connection;
    private char seprator;
    /**
     * Public constructor to build CSVLoader object with
     * Connection details. The connection is closed on success
     * or failure.
     * @param connection
     */
    public CSVLoader(Connection connection) {
        this.connection = connection;
        //Set default separator
        this.seprator = ',';
    }
    
    /**
     * Parse CSV file using OpenCSV library and load in
     * given database table.
     * @param csvFile Input CSV file
     * @param tableName Database table name to import data
     * @param truncateBeforeLoad Truncate the table before inserting
     *          new records.
     * @throws Exception
     */
    public void loadCSV(String csvFile, String tableName,
            boolean truncateBeforeLoad) throws Exception {
        CSVReader csvReader = null;
        if(null == this.connection) {
            throw new Exception("Not a valid connection.");
        }
        try {
            
            csvReader = new CSVReader(new FileReader(csvFile), this.seprator);
        } catch (Exception e) {
            e.printStackTrace();
            throw new Exception("Error occured while executing file. "
                    + e.getMessage());
        }
        String[] headerRow = csvReader.readNext();
        if (null == headerRow) {
            throw new FileNotFoundException(
                    "No columns defined in given CSV file." +
                    "Please check the CSV file format.");
        }
        String questionmarks = StringUtils.repeat("?,", headerRow.length);
        questionmarks = (String) questionmarks.subSequence(0, questionmarks
                .length() - 1);
        String query = SQL_INSERT.replaceFirst(TABLE_REGEX, tableName);
        query = query
                .replaceFirst(KEYS_REGEX, StringUtils.join(headerRow, ","));
        query = query.replaceFirst(VALUES_REGEX, questionmarks);
        System.out.println("Query: " + query);
        String[] nextLine;
        Connection con = null;
        PreparedStatement ps = null;
        try {
            con = this.connection;
            con.setAutoCommit(false);
            ps = con.prepareStatement(query);
            if(truncateBeforeLoad) {
                //delete data from table before loading csv
                con.createStatement().execute("DELETE FROM " + tableName);
            }
            final int batchSize = 1000;
            int count = 0;
            Date date = null;
            while ((nextLine = csvReader.readNext()) != null) {
                if (null != nextLine) {
                    int index = 1;
                    for (String string : nextLine) {
                        date = DateUtil.convertToDate(string);
                        if (null != date) {
                            ps.setDate(index++, new java.sql.Date(date
                                    .getTime()));
                        } else {
                            ps.setString(index++, string);
                        }
                    }
                    ps.addBatch();
                }
                if (++count % batchSize == 0) {
                    ps.executeBatch();
                }
            }
            ps.executeBatch(); // insert remaining records
            con.commit();
        } catch (Exception e) {
            con.rollback();
            e.printStackTrace();
            throw new Exception(
                    "Error occured while loading data from file to database."
                            + e.getMessage());
        } finally {
            if (null != ps)
                ps.close();
            if (null != con)
                con.close();
            csvReader.close();
        }
    }
    public char getSeprator() {
        return seprator;
    }
    public void setSeprator(char seprator) {
        this.seprator = seprator;
    }
}

The class looks complicated but it is simple :)

The loadCSV methods combines the idea from above three tutorials and create insert queries.

Following is the usage of this class if you want to use it in your project:

Usage

CSVLoader loader = new CSVLoader(connection);
loader.loadCSV("C:\\employee.csv", "TABLE_NAME", true);

Load file with semicolon as delimeter:

CSVLoader loader = new CSVLoader(connection);
loader.setSeparator(';');
loader.loadCSV("C:\\employee.csv", "TABLE_NAME", true);

Load file without truncating the table:

CSVLoader loader = new CSVLoader(connection);
loader.loadCSV("C:\\employee.csv", "TABLE_NAME", false);

Hope this helps.

Download Source Code

Load_CSV_Database_Java_example.zip (2.05 MB)

About these ads

Create a RESTful Services API in PHP.

Posted: May 21, 2013 in Php
Tags: , , , , , , , , , ,
0

Are you working with multiple devices like iPhone, Android and Web then take a look at this post that explains you how to develop a RESTful API in PHP.  Representational state transfer (REST) is a software system for distributing the data to different kind of applications. The web service system produce status code response in JSON or XML format.
Create a RESTful Services API in PHP.


Download Script

Database
Sample database users table columns user_id, user_fullname, user_email, user_password and user_status.

CREATE TABLE IF NOT EXISTS `users`
(
`user_id` int(11) NOT NULL AUTO_INCREMENT,
`user_fullname` varchar(25) NOT NULL,
`user_email` varchar(50) NOT NULL,
`user_password` varchar(50) NOT NULL,
`user_status` tinyint(1) NOT NULL DEFAULT ’0′,
PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

Rest API Class: api.php
Contains simple PHP code, here you have to modify database configuration details like database name, username and password.

<?php
require_once(“Rest.inc.php”);class API extends REST
{
public $data = “”;
const DB_SERVER = “localhost”;
const DB_USER = “Database_Username”;
const DB_PASSWORD = “Database_Password”;
const DB = “Database_Name”;private $db = NULL;

public function __construct()
{
parent::__construct();// Init parent contructor
$this->dbConnect();// Initiate Database connection
}

//Database connection
private function dbConnect()
{
$this->db = mysql_connect(self::DB_SERVER,self::DB_USER,self::DB_PASSWORD);
if($this->db)
mysql_select_db(self::DB,$this->db);
}

//Public method for access api.
//This method dynmically call the method based on the query string
public function processApi()
{
$func = strtolower(trim(str_replace(“/”,”",$_REQUEST['rquest'])));
if((int)method_exists($this,$func) > 0)
$this->$func();
else
$this->response(”,404);
// If the method not exist with in this class, response would be “Page not found”.
}

private function login()
{
…………..
}

private function users()
{
…………..
}

private function deleteUser()
{
………….
}

//Encode array into JSON
private function json($data)
{
if(is_array($data)){
return json_encode($data);
}
}
}

// Initiiate Library
$api = new API;
$api->processApi();
?>

Login POST
Displaying users records from the users table Rest API URL http://localhost/rest/login/. This Restful API login status works with status codes if status code 200 login success else status code 204 shows fail message. For more status code information check Rest.inc.php in download script.

private function login()
{
// Cross validation if the request method is POST else it will return “Not Acceptable” status
if($this->get_request_method() != “POST”)
{
$this->response(”,406);
}$email = $this->_request['email'];
$password = $this->_request['pwd'];// Input validations
if(!empty($email) and !empty($password))
{
if(filter_var($email, FILTER_VALIDATE_EMAIL)){
$sql = mysql_query(“SELECT user_id, user_fullname, user_email FROM users WHERE user_email = ‘$email’ AND user_password = ‘”.md5($password).”‘ LIMIT 1″, $this->db);
if(mysql_num_rows($sql) > 0){
$result = mysql_fetch_array($sql,MYSQL_ASSOC);

// If success everythig is good send header as “OK” and user details
$this->response($this->json($result), 200);
}
$this->response(”, 204); // If no records “No Content” status
}
}

// If invalid inputs “Bad Request” status message and reason
$error = array(‘status’ => “Failed”, “msg” => “Invalid Email address or Password”);
$this->response($this->json($error), 400);
}

Users GET
Displaying users records from the users table Rest API URL http://localhost/rest/users/

private function users()
{
// Cross validation if the request method is GET else it will return “Not Acceptable” status
if($this->get_request_method() != “GET”)
{
$this->response(”,406);
}
$sql = mysql_query(“SELECT user_id, user_fullname, user_email FROM users WHERE user_status = 1″, $this->db);
if(mysql_num_rows($sql) > 0)
{
$result = array();
while($rlt = mysql_fetch_array($sql,MYSQL_ASSOC))
{
$result[] = $rlt;
}
// If success everythig is good send header as “OK” and return list of users in JSON format
$this->response($this->json($result), 200);
}
$this->response(”,204); // If no records “No Content” status
}

DeleteUser
Delete user function based on the user_id value deleting the particular record from the users table Rest API URL http://localhost/rest/deleteUser/

private function deleteUser()
{if($this->get_request_method() != “DELETE”){
$this->response(”,406);
}
$id = (int)$this->_request['id'];
if($id > 0)
{
mysql_query(“DELETE FROM users WHERE user_id = $id”);
$success = array(‘status’ => “Success”, “msg” => “Successfully one record deleted.”);
$this->response($this->json($success),200);
}
else
{
$this->response(”,204); // If no records “No Content” status
}
}

Chrome Extention
A Extention for testing PHP restful API response download here Advanced REST client Application

.htaccess code
Rewriting code for friendly URLs. In the download code you just modify htaccess.txt to .htaccess

<IfModule mod_rewrite.c>
RewriteEngine On
RewriteCond %{REQUEST_FILENAME} !-d
RewriteCond %{REQUEST_FILENAME} !-s
RewriteRule ^(.*)$ api.php?rquest=$1 [QSA,NC,L]RewriteCond %{REQUEST_FILENAME} -d
RewriteRule ^(.*)$ api.php [QSA,NC,L]RewriteCond %{REQUEST_FILENAME} -s
RewriteRule ^(.*)$ api.php [QSA,NC,L]
</IfModule>

 

Latest Tutorials

Posted: May 18, 2013 in Random Posts
0

Latest Tutorials.

Older Entries