Importing CSV to my SQL

Hi all,
It is possible to import a csv or text file directly in to mysql.

LOAD DATA INFILE filename INTO TABLE csvtest FIELDS TERMINATED BY ','(field name)


There are few things to watch out for , consider a csv file which has two rows of data and a header row.

when you try to import it even the headers will be inserted as a row. That is

Name,Age
A,12
B,15

When you try to import a csv of this kind you will see three rows getting inserted into the table.
Conclusion, mysql is not capable of identifying headers in csv,so whatever data is given with the comma separated, it will be considered as a column.
Let us modify the above query,

LOAD DATA INFILE filename INTO TABLE csvtest FIELDS TERMINATED BY ','(Name)
12,A


When you try to import the csv i mentioned above, it cannot guarantee the the exact value, here it will try to take 12 . So configuring csv is in our hands.

Sample program as follows,

public class ImportFullCSV {

    Connection con = null;

    Statement pstmt = null;

    ResultSet rs = null;

//DataConnection dc=null;

    public ImportFullCSV() {

        try {

            Class.forName("com.mysql.jdbc.Driver");

            con = DriverManager.getConnection("jdbc:mysql://localhost:3306/hibernatetest", "root", "root");

        } catch (Exception ex) {

            Logger.getLogger(ImportFullCSV.class.getName()).log(Level.SEVERE, null, ex);

        }

    }

    public void importData(String filename) {

        try {

            pstmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);

            String query = "LOAD DATA INFILE '" + filename + "'INTO TABLE csvtest FIELDS TERMINATED BY ','(name)";

            pstmt.executeUpdate(query);

            System.out.println(query);

        } catch (Exception e) {

            e.printStackTrace();

        }

    }

    public void selctTable(){

        try{

        pstmt=con.createStatement();

        rs=pstmt.executeQuery("select * from csvtest");

        System.out.println("NAME    ---   CITY" );

        while(rs.next()){

            System.out.println(rs.getString("name")+"  ----  "+rs.getString("city"));

        }

        }

        catch(Exception e){

            e.printStackTrace();

        }

    }

    public static void main(String[] args) {

        ImportFullCSV c = new ImportFullCSV();

        c.importData("D:/Raghuram/testproject/test1.csv");

        c.selctTable();

    }

}