how to read the values for text and csv files and store those values into database in multiple rows..means one value for one row

Hai,

I need a program by using servlets.. the program is like this

i upload a text and csv file.it was stored in perticular directory now i have to read the stored(.csv or .txt) file and get the values from that file and store them into database table in multiple rows(which means one value for one row).

eg: my file containes the data as content of file ==> siva,divya,ravi

now it should stored in database like names(column)

siva divya

ravi

one value for one row.... can any one help in this program?

Regards, P.Divya

View Answers

August 12, 2011 at 1:56 AM

hi sridivya,

Just follows the steps which I have mentioned below. I have created this project in eclipse

Setup database

Here, I used MySql database, so type the following queries in mysql

create database test;
use test
create table namelist(name varchar(20));

Required files

1. uploadform.jsp
2. Upload.java (Servlet)
3. confirm.jsp
4. UpdateDB.java (Servlet)
5. error.jsp
6. namelist.csv

Required library

1.javacsv.jar
2.mysql-connector-java-5.1.10-bin.jar

You can download these library from&nbsp;&nbsp;
[mysql driver][1]&nbsp;&nbsp;
[CSVparser][2]

Add the above library to "ContextRoot/WEB-INF/lib/" directory to use servlet

Coding

1. uploadform.jsp

    <%@ page language="java" contentType="text/html; charset=UTF-8"
        pageEncoding="UTF-8"%>
    <!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=UTF-8">
    <title>Upload file</title>
    </head>
    <body>
        <form action="upload" enctype="multipart/form-data" method="post">
            <table border="1">
                <tr>
                    <td>CSV or TXT file:</td>
                    <td><input type="file" name="csvfile" /></td>
                </tr>
                <tr>
                    <td></td>
                    <td><input type="submit" value="submit"></td>
                </tr>
            </table>            
        </form>
    </body>
    </html>


2. Upload.java (Servlet)

    package com.file;

    import java.io.DataInputStream;
    import java.io.File;
    import java.io.FileOutputStream;
    import java.io.IOException;
    import java.io.PrintWriter;

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

    /**
     * Servlet implementation class UploadServlet
     */
    public class Upload extends HttpServlet {
        private static final long serialVersionUID = 1L;
        PrintWriter out;
        private void setOut(PrintWriter out){
            this.out = out;
        }

        private void println(String content){
            out.print(content+"\n");
        }
        /**
         * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
         */
        protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {  
            setOut(response.getWriter());
            boolean done = false;
            //to get the content type information from JSP Request Header
            String contentType = request.getContentType();

            //here we are checking the content type is not equal to Null and as well as the passed data from mulitpart/form-data is greater than or equal to 0
            if ((contentType != null) && (contentType.indexOf("multipart/form-data") >= 0)) {
                DataInputStream in = new DataInputStream(request.getInputStream());
                //we are taking the length of Content type data
                int formDataLength = request.getContentLength();
                byte dataBytes[] = new byte[formDataLength];
                int byteRead = 0;
                int totalBytesRead = 0;
                //this loop converting the uploaded file into byte code
                while (totalBytesRead < formDataLength) {
                    byteRead = in.read(dataBytes, totalBytesRead, formDataLength);
                    totalBytesRead += byteRead;
                    }

                String file = new String(dataBytes);

                //for saving the file name
                String saveFile = file.substring(file.indexOf("filename=\"") + 10);
                saveFile = saveFile.substring(0, saveFile.indexOf("\n"));
                saveFile = saveFile.substring(saveFile.lastIndexOf("\\") + 1,saveFile.indexOf("\""));

                int lastIndex = contentType.lastIndexOf("=");
                String boundary = contentType.substring(lastIndex + 1,contentType.length());
                int pos;
                //extracting the index of file 
                pos = file.indexOf("filename=\"");
                pos = file.indexOf("\n", pos) + 1;
                pos = file.indexOf("\n", pos) + 1;
                pos = file.indexOf("\n", pos) + 1;
                int boundaryLocation = file.indexOf(boundary, pos) - 4;
                int startPos = ((file.substring(0, pos)).getBytes()).length;
                int endPos = ((file.substring(0, boundaryLocation)).getBytes()).length;

                if(saveFile.endsWith(".txt") || saveFile.endsWith(".csv")){
                    // creating a new file with the same name and writing the content in new file
                    FileOutputStream fileOut = new FileOutputStream(new File("../uploads/"+saveFile));
                    fileOut.write(dataBytes, startPos, (endPos - startPos));
                    fileOut.flush();
                    fileOut.close();
                    done = true;
                    getServletContext().setAttribute("fileName", saveFile);             
                    request.setAttribute("fileName", saveFile);
                }else{
                    request.setAttribute("error", "Unsupported file format");
                }
            }
            if(done)
                request.getRequestDispatcher("confirm.jsp").forward(request, response);
            else
                request.getRequestDispatcher("error.jsp").forward(request, response);
        }
    }


3. confirm.jsp

    <%@ page language="java" contentType="text/html; charset=UTF-8"
        pageEncoding="UTF-8"%>
    <!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=UTF-8">
    <title>Success Page</title>
    </head>
    <body>
        <%=request.getAttribute("fileName") %> is uploaded successfully.
        <a href="updatedb">Click here to update database</a>
    </body>
    </html>

4. updatedb.java (Servlet)

    package com.db;

    import java.io.FileNotFoundException;
    import java.io.IOException;
    import java.io.PrintWriter;
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.Statement;

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

    import com.csvreader.CsvReader;

    /**
     * Servlet implementation class UpdateDatabase
     */
    public class UpdateDatabase extends HttpServlet {
        private static final long serialVersionUID = 1L;

        /**
         * @see HttpServlet#HttpServlet()
         */
        public UpdateDatabase() {
            super();
            // TODO Auto-generated constructor stub
        }

        /**
         * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
         */
        protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
            // TODO Auto-generated method stub
            PrintWriter out = response.getWriter();
            try {           
                String filename = (String) getServletContext().getAttribute("fileName");
                out.println("FileName : "+filename);
                CsvReader products = new CsvReader("../uploads/"+filename);
                products.readHeaders();

                Class.forName("com.mysql.jdbc.Driver");
                Connection con = DriverManager.getConnection("jdbc:mysql://localhost/stack", "root", "");
                Statement st = con.createStatement();

                while (products.readRecord())
                {
                    String productID = products.get("ProductID");
                    String productName = products.get("ProductName");
                    String supplierID = products.get("SupplierID");
                    String categoryID = products.get("CategoryID");
                    String quantityPerUnit = products.get("QuantityPerUnit");
                    String unitPrice = products.get("UnitPrice");
                    String unitsInStock = products.get("UnitsInStock");
                    String unitsOnOrder = products.get("UnitsOnOrder");
                    String reorderLevel = products.get("ReorderLevel");
                    String discontinued = products.get("Discontinued");             

                    String query = "insert into product values ("; 
                    query += productID+", '";
                    query += productName+"', ";
                    query += supplierID+", ";
                    query += categoryID+", '";
                    query += quantityPerUnit+"', ";
                    query += unitPrice+", ";
                    query += unitsInStock+", ";
                    query += unitsOnOrder+", ";
                    query += reorderLevel+", '";
                    query += discontinued+"')";

                    out.println("Query : "+query);
                    st.executeUpdate(query);
                }
                out.println("Data inserted...");
                products.close();
            } catch (FileNotFoundException e) {
                e.printStackTrace();
            } catch (Exception e) {
                e.printStackTrace();
            }

        }

        /**
         * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
         */
        protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
            // TODO Auto-generated method stub
        }

    }


5. error.jsp

    <%@ page language="java" contentType="text/html; charset=UTF-8"
        pageEncoding="UTF-8"%>
    <!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=UTF-8">
    <title>Fail Page</title>
    </head>
    <body>
        <%=request.getAttribute("error") %>
        Choose correct file format <br />
        <a href="uploadform.jsp">Upload file</a>
    </body>
    </html>

6. namelist.csv 

    name
    siva
    divya
    ravi

Regards,
M.Sriram

August 12, 2011 at 8:31 AM

Hi,
There was an error in the above demo. Need to change database and namelist.csv file.

Setup database
    create database stack;
    use stack
    create table product(ProductID int, ProductName varchar(20), SupplierID int, CategoryID int, QuantityPerUnit varchar(50), UnitPrice int, UnitsInStock int, UnitsOnOrder int, ReorderLevel int, Discontinued varchar(8));

**6. namelist.csv**

    ProductID,ProductName,SupplierID,CategoryID,QuantityPerUnit,UnitPrice,UnitsInStock,UnitsOnOrder,ReorderLevel,Discontinued
    1,Chai,1,1,10 boxes x 20 bags,18,39,0,10,FALSE
    2,Chang,1,1,24 - 12 oz bottles,19,17,40,25,FALSE

August 12, 2011 at 12:35 PM

can u tel me the file size limit for this program?

Regards, P.Divya

August 12, 2011 at 9:45 PM

Which file are you asking about? csv file?

Regards,
M.Sriram