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:

70 comments:

  1. Excellent post!!! Your article helped to under the future of java development. Being an open source platform, java is integrated in most of the software development industries to create rich featured applications. Java Training in Chennai

    ReplyDelete
  2. brillant piece of information, I had come to know about your web-page from my friend hardkik, chennai,i have read atleast 9 posts of yours by now, and let me tell you, your webpage gives the best and the most interesting information. This is just the kind of information that i had been looking for, i'm already your rss reader now and i would regularly watch out for the new posts, once again hats off to you! Thanx a million once again, Regards, obiee training in hyderabad,informatica training in hyderabad

    ReplyDelete
  3. Can yo please tell me web.xml file?

    ReplyDelete
  4. Java Training Institutes Java Training Institutes Java EE Training in Chennai Java EE Training in Chennai Java Spring Hibernate Training Institutes in Chennai J2EE Training Institutes in Chennai J2EE Training Institutes in Chennai Core Java Training Institutes in Chennai Core Java Training Institutes in Chennai

    Hibernate Online Training Hibernate Online Training Hibernate Training in Chennai Hibernate Training in Chennai Java Online Training Java Online Training Hibernate Training Institutes in ChennaiHibernate Training Institutes in Chennai

    ReplyDelete
  5. 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
  6. Thanks for your informative article on cloud technology. Salesforce Training in Chennai

    ReplyDelete
  7. I really enjoyed while reading your article, the information you have delivered in this post was damn good. Keep sharing your post with efficient news.
    Regards,
    cloud computing training in chennai|cloud computing training

    ReplyDelete
  8. a pride for me to be able to discuss on a quality website because I just learned to make an article on
    cara menggugurkan kandungan

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

    ReplyDelete
  10. 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
  11. 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
  12. This is an awesome post.Really very informative and creative contents.
    Wordpress Development company in Chennai

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

    ReplyDelete
  14. Great Article. Thank you for sharing! Really an awesome post for every one.

    IEEE Final Year projects Project Centers in Chennai are consistently sought after. Final Year Students Projects take a shot at them to improve their aptitudes, while specialists like the enjoyment in interfering with innovation. For experts, it's an alternate ball game through and through. Smaller than expected IEEE Final Year project centers ground for all fragments of CSE & IT engineers hoping to assemble. Final Year Project Domains for IT It gives you tips and rules that is progressively critical to consider while choosing any final year project point.

    Spring Framework has already made serious inroads as an integrated technology stack for building user-facing applications. Spring Framework Corporate TRaining the authors explore the idea of using Java in Big Data platforms.
    Specifically, Spring Framework provides various tasks are geared around preparing data for further analysis and visualization. Spring Training in Chennai

    ReplyDelete
  15. keep up the good work. this is an Assam post. this to helpful, i have reading here all post. i am impressed. thank you. this is our digital marketing training center. This is an online certificate course
    digital marketing training in bangalore | https://www.excelr.com/digital-marketing-training-in-bangalore

    ReplyDelete
  16. When I originally commented I seem to have clicked the -Notify me when new comments are added- checkbox and from now on each time a comment is added I recieve 4 emails with the exact same comment. Perhaps there is an easy method you are able to remove me from that service? Thanks a lot!
    Tech PC

    ReplyDelete
  17. 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
  18. 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
  19. 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
  20. It’s hard to come by experienced people about this technology subject, but you seem like you know what you’re talking about! Thanks

    ReplyDelete
  21. I would like to comment on this quality content. I can see you have done a lot of homework and given this topic much thought.
    Best Data Science training in Mumbai

    Data Science training in Mumbai

    ReplyDelete
  22. I am genuinely thankful to the holder of this web page who has shared this wonderful paragraph at at this place
    Courses in Digital Marketing in Pune

    ReplyDelete
  23. 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
  24. 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
  25. Excellent Blog! I would like to thank for the efforts you have made in writing this post. I am hoping the same best work from you in the future as well. I wanted to thank you for this websites! Thanks for sharing. Great websites!
    Data Science Certification in Bangalore

    ReplyDelete
  26. I feel very grateful that I read this. It is very helpful and very informative and I really learned a lot from it. data science course in coimbatore

    ReplyDelete
  27. 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 science course in vizag

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

    ReplyDelete

  29. I see some amazingly important and kept up to length of your strength searching for in your on the site.

    Data Science Course

    ReplyDelete
  30. I feel very grateful that I read this. It is very helpful and very informative and I really learned a lot from it.
    Data Science Training Institute in Bangalore

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

    Data Science Training

    ReplyDelete
  32. After reading your article I was amazed. I know that you explain it very well. And I hope that other readers will also experience how I feel after reading your article.
    Best Data Science Courses in Bangalore

    ReplyDelete
  33. 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 science course in vizag

    ReplyDelete
  34. 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
  35. 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
  36. 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
  37. Attend The Data Analyst Course From ExcelR. Practical Data Analyst Course Sessions With Assured Placement Support From Experienced Faculty. ExcelR Offers The Data Analyst Course.
    Data Analyst Course

    ReplyDelete
  38. Extraordinary blog went amazed with the content that they have developed in a very descriptive manner. This type of content surely ensures the participants to explore themselves. Hope you deliver the same near the future as well. Gratitude to the blogger for the efforts.

    360DigiTMG Cloud Computing Course

    ReplyDelete
  39. 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
  40. You may remark on the request arrangement of the blog. You should talk it's breathtaking. Your blog review would grow your guests. I was exceptionally satisfied to discover this site.I needed to thank you for this incredible read!!
    data scientist training

    ReplyDelete
  41. Very good points you wrote here..Great stuff...I think you've made some truly interesting points.Keep up the good work.
    cyber security course training in Hyderabadi

    ReplyDelete
  42. A good blog always comes-up with new and exciting information and while reading I have feel that this blog is really have all those quality that qualify a blog to be a one.
    360digitmg

    ReplyDelete
  43. Oracle Corporation is an American multinational computer technology corporation headquartered in Redwood Shores, California.
    tally training in chennai

    hadoop training in chennai

    sap training in chennai

    oracle training in chennai

    angular js training in chennai




    ReplyDelete
  44. I would like to thank you for the efforts you have made in writing this article. I am hoping the same best work from you in the future as well. In fact your creative writing abilities has inspired me to start my own Blog Engine blog now. Really the blogging is spreading its wings rapidly. Your write up is a fine example of it.
    360digitmg

    ReplyDelete
  45. 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
  46. 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