Friday, July 20, 2012

Upload CSV file into MySql Database based on columns using Servlets and Java



The CSV file is having multiple values to insert in perticular columns and rows in mysql database.

Here we have two files so we have to insert in two tables .

*** one is Header part file

*** another is Detail part file

Basically these two are to upload the questions and answers into the examination portal to upload question papers and answer paper in (Merit Tracking System project).

First we have to do the things are Uploaded file has to save in perticular folder and then they have to insert the values into the database table.This is our main task to do.


The required files to this program are 

#1. index.jsp
#2. succ.jsp
#3. web.xml
#4. DBConnection.java
#5. SaveFile.java
#6. UploadFile2DB.java

and Some other libraries to add 


#1. mysql-connector-java-3.1.11.jar
#2. servlet-api.jar

These are enough to our requirement add this files to your "lib" folder.



#1. index.jsp

<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
 pageEncoding="ISO-8859-1"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>

<form name="fileuploadform"
 action="http://localhost:8080/UploadCSV/uploadfile">Upload CSV
File<br>
Select the header file to upload <input type="file" name="filehdr" /><br>
Select the detail file to upload <input type="file" name="filedtl" /><br>

Please select a folder to which the file has to be uploaded. <input
 type="file" name="filefolder" /><br>
<input type="submit" name="submit" value="submit"> </form>
</html>

#2. succ.jsp 

<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
    pageEncoding="ISO-8859-1"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Insert title here</title>
</head>
<body>
<b>The Files has been Uploaded into particular tables.</b>
</body>
</html>

#3. DBConnection.java


package com;

import java.sql.Connection;
import java.sql.DriverManager;

public class DBConnection {
static Connection con;
    
    public static Connection getConnection(){
        con=null;
        try{
            System.out.println("----------I am in DBConnection----------");
            Class.forName("com.mysql.jdbc.Driver");
            con=DriverManager.getConnection("jdbc:mysql://192.168.1.101:3306/test?user=test&password=test" );
            System.out.println("---------end of DBConnection----------");
        }catch(Exception e){
            e.getMessage();
        }
        return con;
    }
}


#4. SaveFile.java


package com;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;

import javax.servlet.ServletConfig;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

public class SaveFile extends HttpServlet {
    public void init(ServletConfig config)throws ServletException{
        super.init(config);
        System.out.println("The SaveFile iniated.^^^^^^^^^^^^^^^^^^^################");
    }
    
    public void service(HttpServletRequest request, HttpServletResponse response)throws ServletException,IOException{
        try{
            String pathheader=request.getParameter("filehdr");
            System.out.println("The pathheader is : "+pathheader);
            String pathdetail=request.getParameter("filedtl");
            System.out.println("The pathdetail is : "+pathdetail);
            
            String folderpath=request.getParameter("filefolder");    
            
            String filenamehdr=folderpath+pathheader.substring(pathheader.lastIndexOf('\\'));
            System.out.println("The file output path is : "+filenamehdr);            
            String filenamedtl=folderpath+pathdetail.substring(pathdetail.lastIndexOf('\\'));
            System.out.println("The file output path is : "+filenamedtl);
            
            FileInputStream fis=new FileInputStream(pathheader);
            FileOutputStream fos=new FileOutputStream(filenamehdr);
            
            byte buf[]=new byte[11024];
            fis.read(buf);
            fos.write(buf,0,buf.length);
            
            fis=new FileInputStream(pathdetail);
            fos=new FileOutputStream(filenamedtl);
            fis.read(buf);
            fos.write(buf,0,buf.length);
            
            if(fis!=null)
                fis.close();
            if(fos!=null)
                fos.close();
            
            System.out.println("------------------ Files are Saved in Folder-------------------");
            request.getRequestDispatcher("/uploaddata").forward(request, response);

        }catch(FileNotFoundException e){
            System.out.println(e.getMessage());
        }catch(IOException e){
            System.out.println(e.getMessage());
        }
    }


}



#5. UploadFile2DB.java


package com;

import java.io.BufferedReader;
import java.io.FileReader;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.StringTokenizer;

import javax.servlet.ServletConfig;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

public class UploadFile2DB extends HttpServlet {
    public void init(ServletConfig config) throws ServletException{
        super.init(config);
        System.out.println("The UploadDataServlet2 iniated.");
    }



    public void service(HttpServletRequest request, HttpServletResponse response) throws ServletException,IOException {
        
        
        String filepathhdr=request.getParameter("filehdr");
        String filepathdtl=request.getParameter("filedtl");
        Connection con=DBConnection.getConnection();
        System.out.println("connection=----------->"+con);
        PreparedStatement pstmthdr=null;
        PreparedStatement pstmtdtl=null;

        int rowshdr=0;
        BufferedReader brhdr=new BufferedReader(new FileReader(filepathhdr));
        BufferedReader brdtl=new BufferedReader(new FileReader(filepathdtl));
        System.out.println("reading the file");
        String strLineHdr="";
        String strLineDtl="";
        String hdrstr="";
        String dtlstr="";
        StringTokenizer sthdr=null;
        StringTokenizer stdtl=null;
        //        String firstColumnData[]=new String[10];
        int lineNumberHdr=0;
        int lineNumberDtl=0;
        //        int line=1;
        try{
            pstmthdr=con.prepareStatement("insert into omts_onlinehdr values (?,?,?,?,?,?,?)");
            System.out.println("statement executed");

            while((strLineHdr=brhdr.readLine())!=null){
                System.out.println("HEADERLINE"+strLineHdr);
                int i=1;
                if(!(lineNumberHdr==0)){
                    sthdr=new StringTokenizer(strLineHdr,",");
                    while(sthdr.hasMoreTokens()){
                        hdrstr=sthdr.nextToken();
                        System.out.println("HeaderString: "+hdrstr);
                        pstmthdr.setString(i++,hdrstr);
                        System.out.println("below insertion");
                    }
                    rowshdr=pstmthdr.executeUpdate();
                    System.out.println(rowshdr+" rows updated.");
                }
                lineNumberHdr++;
            }
            System.out.println("not in detail");
            pstmtdtl=con.prepareStatement("insert into omts_onlinedtl values (?,?,?,?,?,?,?)");
            System.out.println("ps executed");

            while((strLineDtl=brdtl.readLine())!=null){
                System.out.println("detailLINE"+strLineDtl);
                int i=1;
                if(!(lineNumberDtl==0)){
                    stdtl=new StringTokenizer(strLineDtl,",");
                    while(stdtl.hasMoreTokens()){
                        dtlstr=stdtl.nextToken();
                        System.out.println("detail: "+dtlstr);
                        pstmtdtl.setString(i++,dtlstr);
                        System.out.println("below insertion");
                    }
                    int rowsdtl=pstmtdtl.executeUpdate();
                    System.out.println(rowsdtl+" rows are updated.");
                }
                lineNumberDtl++;
            }
            //con.commit();
        }
        catch(Exception e){
            System.out.println(e.getMessage());
        }
        finally
        {
            try {
                con.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
        response.sendRedirect("http://localhost:8080/UploadCSV/succ.jsp");
    }
}


Run your application using Tomcat webserver and it will displays index.jsp as welcome-file-list.



By selecting CSV file  using browse button  of header part and detail part and set the path of the folder where you want to save the uploaded files and click on submit button.

After completion of accessing two files into folders and databases it will displays as succ.jsp file as "successfully uploaded the two files".


The Console will displays as the all the information what is going inside.
Note: Use the MySql database or Oracle and give the driver name and url of the driver to connect Database in the DBConncetion class.


DOWNLOAD SOURCE

For Reference on Files:

36 comments:

  1. Can yo please tell me web.xml file?

    ReplyDelete
  2. The CSV format is primarily used to transfer tabular data from a database into a simpler text format. QIF or Quicken Interchange Format is another popular data format.excel reporting dashboard

    ReplyDelete
  3. When you change the configuration file, you will always need to restart the MySQL server to allow the changes to reload.DB Designer

    ReplyDelete
  4. In any case, on the off chance that you don't have any reinforcement, visit now you should attempt to fix SQLite database physically. In case you're utilizing SQLite database program, you simply need to pursue these straightforward advances:

    ReplyDelete
  5. It's imperative to likewise keep your framework perfect and very much ventilated, since exorbitant warmth can harm the electrical segments on the drive. manchester data recovery

    ReplyDelete
  6. This is an awesome post.Really very informative and creative contents.
    Wordpress Development company in Chennai

    ReplyDelete
  7. It was really an interesting blog, Thank you for providing unknown facts.
    Twin Cities Web Design

    ReplyDelete
  8. Such a very useful article. Very interesting to read this article.I would like to thank you for the efforts you had made for writing this awesome article.
    360DigiTMG data analytics courses in hyderabad

    ReplyDelete
  9. You need to take part in a contest for one of the highest quality blogs on the web. I'm going to recommend this informative website!

    ReplyDelete
  10. Hi, I do think this is an excellent site. I stumbledupon it ;) I may come back once again since I saved as a favorite it. Money and freedom is the best way to change, study may you be rich and continue to guide others.

    ReplyDelete
  11. This is an awesome post.Really very informative and creative contents about AZURE. These concept is a good way to enhance the knowledge.I like it and help me to development very well.Thank you for this brief explanation and very nice information.Well, got a good knowledge.
    This is an awesome post.Really very informative and creative contents about Java. These concept is a good way to enhance the knowledge.I like it and help me to development very well.Thank you for this brief explanation and very nice information.Well, got a good knowledge.

    ReplyDelete
  12. This is an awesome post.Really very informative and creative contents about Java. These concept is a good way to enhance the knowledge.I like it and help me to development very well.Thank you for this brief explanation and very nice information.Well, got a good knowledge.
    Microsoft Windows Azure Training | Online Course | Certification in chennai | Microsoft Windows Azure Training | Online Course | Certification in bangalore | Microsoft Windows Azure Training | Online Course | Certification in hyderabad | Microsoft Windows Azure Training | Online Course | Certification in pune

    ReplyDelete
  13. This comment has been removed by the author.

    ReplyDelete
  14. I think about it is most required for making more on this get engaged.

    Data Science Training

    ReplyDelete
  15. Awesome blog. I enjoyed reading your articles. This is truly a great read for me. I have bookmarked it and I am looking forward to reading new articles. Keep up the good work!
    data analytics course in hyderabad

    ReplyDelete
  16. I am sure that this is going to help a lot of individuals. Keep up the good work. It is highly convincing and I enjoyed going through the entire blog.

    Cyber Security course training in Vizag

    ReplyDelete
  17. Nice and descent post i have come across found to be very impressive while going through this post. Thanks for sharing and keep posting such an informative content.

    Data Science Course in Raipur

    ReplyDelete
  18. Tremendous blog quite easy to grasp the subject since the content is very simple to understand. Obviously, this helps the participants to engage themselves in to the subject without much difficulty. Hope you further educate the readers in the same manner and keep sharing the content as always you do.

    360DigiTMG Data Science Course

    ReplyDelete
  19. It’s really helpful blog. I really appreciate your information which you shared with us. If anyone who want to create his/her carrier in JAVA. Get a free demo call on 9311002620 or visit https://www.htsindia.com/java-training-courses

    ReplyDelete
  20. Thanks for posting these kinds of post its very helpful and very good content a really appreciable post apart from that if anyone looking for best Advanced Excel training institute in delhi so contact here +91-9311002620 visit https://www.htsindia.com/Courses/business-analytics/adv-excel-training-course

    ReplyDelete
  21. This is my first time visit to your blog and I am very interested in the articles that you serve. Provide enough knowledge for me. Thank you for sharing useful and don't forget, keep sharing useful info: video upload

    ReplyDelete
  22. You have worked nicely with your insights that makes our work easy. The information you have provided is really factual and significant for us. Keep sharing these types of article, Thank you.Advance Java Course in Delhi

    ReplyDelete

  23. Thank you for sharing an amazing & wonderful blog. This content is very useful, informative and valuable in order to enhance knowledge. Keep sharing this type of content with us & keep updating us with new blogs. Apart from this, if anyone who wants to join the MIS Training institute in Delhi, can contact 9311002620 or visit our website-
    https://htsindia.com/Courses/business-analytics/mis-training-instiute-in-delhi

    ReplyDelete