Latest Tutorials| Questions and Answers|Ask Questions?|Site Map



Home Answers Viewqa JSP-Servlet 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
Login         

View Questions and Answers by Category

Advertisements


 
Have Programming Question? Ask it here!
 
 
 


sridivya pathaneni
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
4 Answer(s)      3 years and 3 months ago
Posted in : JSP-Servlet


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


Advertisement
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  
[mysql driver][1]  
[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



Related Tutorials/Questions & Answers:
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
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... table in multiple rows(which means one value for one row). eg: my file containes
How to store two integer value in a separate file and retrieve those values
How to store two integer value in a separate file and retrieve those values  I need to save two values so i can use those values later i came up with idea of saving those values in a separate file and updating values in file
Advertisements
retrieve multiple columns values from multiple csv files in java
retrieve multiple columns values from multiple csv files in java  Suppose there is folder in C:\fileupload which contains these two csv files... these two files and store in oracle database as: VendorID,Name,Address and plz
How to fetch entries/values from database to a jsp page one by one?
How to fetch entries/values from database to a jsp page one by one?  I have a table in Microsoft SQL server Management Studio with two columns title... to display different database entries of the each column in different blocks. Now
how can i store text box values as it is in database table
how can i store text box values as it is in database table  CUSTOMER DESCRIPTION
how to store multiple values from drop down in database where i am using java struts 1.3
how to store multiple values from drop down in database where i am using java... where i have to select multiple keyskills. but it is taking only one valuee.. i... is displaying.. i need code in java so that it takes multiple values
How to read and retrieve jtable row values into jtextfield on clicking at particular row ...
How to read and retrieve jtable row values into jtextfield on clicking at particular row ...  Hello Sir, I am developing a desktop... to read all the values of particular row at which mouse is clicked. and display
how can retrieve more than one values in text field using ajax?
how can retrieve more than one values in text field using ajax?  im...($result)) { echo "<input name='seat' type='text' value=".$row['Seat_Number... first then in mysql db retrieve seat numbers 1,2,3,4 in text boxes problem
How to store extracted values from xml in a database? - XML
How to store extracted values from xml in a database?  I want to store extracted xml values in a database... How can i store extacted xml values in a database... give me a example
How to store extracted values from xml in a database? - XML
How to store extracted values from xml in a database?  I want to store extracted xml values in a database... How can i store extacted xml values in a database... give me a example
How to store values in a database using JSTL? - JSP-Servlet
How to store values in a database using JSTL?  I want to store values in a database... How can i store in database? Here is my code... This wil extract details and displays in a table format... Now i want to store these values
how to read values from excel sheet and compare with database using jsp
how to read values from excel sheet and compare with database using jsp  hi sir i am arun how to read values from excel sheet and compare...,serialno) values of excelsheet we have to compare with database value if these 3
store dropdown box values in database server
store dropdown box values in database server  how to store dropdown box values in database server in jsp
how to display the values of one list in other upon clicking a button in struts2
how to display the values of one list in other upon clicking a button in struts2  Hello friends..Am new to struts2..Please any one has to guide me in struts2.. I have a problem, I have to display the values of one list in other
how to count unique and duplicate values from one table in mysql?
how to count unique and duplicate values from one table in mysql?  I have use EMP table.I want to count unique and duplicate records from emp table and how to use in java program
How to pass multiple values from a servlet to JSP?
How to pass multiple values from a servlet to JSP?  hi, I want to pass multiple values form a servlet to JSP. Hw do i do that? I am able to send one value at a time without any prb, but I am unable to carry multiple (from two
store values of drop down list box in database
store values of drop down list box in database  how to store values of drop down list box in oracle database in jsp?I have information inserting form where i have date of birth as drop down list box
How to Dragging and dropping HTML table row to another position(In Jsp) and save the new position (values) also in database(MySql)?
table)from Database, Now i am Dragging and dropping one HTML table row to another...How to Dragging and dropping HTML table row to another position(In Jsp) and save the new position (values) also in database(MySql)?  Hi members, I
Store values of dynamically generated textboxes into database
Store values of dynamically generated textboxes into database   I'm... for example the user enters 3 into textbox, three text boxes get generated in the new frame. now when the user write data into these text boxes I want
how to store a dynamic values - JSP-Servlet
how to store a dynamic values  Dear sir, i have a ArrayList in that i have stored a values from a excel sheet specified column values and i have one string that is as follows Dear ~2 , Your cl is ~3 ,el is ~4
help me plz:-Merge multiple jasper files into Single one
help me plz:-Merge multiple jasper files into Single one  how to Merge multiple jasper files into Single one word doc
Query to insert values in the empty fields in the last row of a table in Mysql database?
Query to insert values in the empty fields in the last row of a table in Mysql database?  I have some fields filled and some fields empty in the last row of my MYSQL database. Now I want to fill up those empty fields in the last
How to carry multiple values from a Servlet to a JSP?
How to carry multiple values from a Servlet to a JSP?  By using the below code I am able to carry the username to a JSP (single value). ----response.sendRedirect("index.jsp?uname="+username);---- But I want multiple values
how to read values from java in xml?
how to read values from java in xml?  how to read values from java in xml
fetch values from database into text field
fetch values from database into text field  please provide the example for fetching values from database into text field of table wth edit...;"); out.println("<td><input type=\"text\" name=\"LASTNAME\" value
JTable duplicate values in row
JTable duplicate values in row  JTable duplicate values in row
fetch values from database into text field
fetch values from database into text field  please provide the example for fetching values from database into text field of table as if i am... type=\"text\" name=\"LASTNAME\" value=\"rs.getString(2)"></td>
how to store array values into two different tables in java?
how to store array values into two different tables in java?  I have... and now I want to store these values in two different tables(i.e store 2 array values in one table and remaining two values in another table
How to compare two tables, and insert values which r not in one table to another table?
How to compare two tables, and insert values which r not in one table... insert the values to main_table... here is my sql query insert into Main_Table MT (MT.serialno) values (Select SerialNo Main_Table MT from where not exists
Arraylist from row values
Arraylist from row values  Hello, can anyone please help on how to make an arraylist from the row values of a particular column from a database table? Thanks in advance!   import java.sql.*; import java.util.ArrayList
select tag multiple values
select tag multiple values   I want to insert multiple values in database which i have selected from select tag
Advertisements
 

 

 

DMCA.com