Home Jsp To Upload and insert the CSV file into Database
Questions:Ask|Latest

 
 

Share on Google+Share on Google+

To Upload and insert the CSV file into Database

Advertisement
In this tutorial, you will learn how to upload a CSV file through JSP and insert it into the database.

To Upload and insert the CSV file into Database

     

In this tutorial, you will learn how to upload a CSV file through JSP and insert it into the database. For this, we have created two jsp pages page.jsp and upload_page.jsp. The page.jsp is created for presentation where a file component is created to let the user select the file to be uploaded and a button to submit the request. The action is performed on upload_page.jsp. Before proceeding further, we need table in database. We created table named 'file' for our example.

Table structure for file

create table file (
  'file_id' int ,
  'file_data' text
);

Table "file" is created.

Here is the code of page.jsp

<%@ page language="java" %>
<HTml>
<HEAD><TITLE>Display file upload form to the user</TITLE></HEAD> 

<BODY>
<FORM ENCTYPE="multipart/form-data" ACTION=
"upload_page.jsp" METHOD=POST>
<br><br><br>
<center><table border="2" >
<tr><center><td colspan="2"><p align=
"center"><B>UPLOAD THE FILE</B><center></td></tr>
<tr><td><b>Choose the file To Upload:</b>
</td>
<td><INPUT NAME="file" TYPE="file"></td></tr>
<tr><td colspan="2">
<p align="right"><INPUT TYPE="submit" VALUE="Send File" ></p></td></tr>
<table>
</center> 
</FORM>
</BODY>
</HTML>

The action attribute of the <form> element specifies the name of the page which will be requested next on clicking the submit button. By using <input type="file">, you can browse the file and get the appropriate file for upload. Action is performed on clicking the "Send File" button. On clicking the button upload_page.jsp page is called.

Here is the code of upload_page.jsp

<%@ page import="java.io.*,java.sql.*" %>
<html>
<%
String contentType = request.getContentType();
if ((contentType != null) && (contentType.indexOf("multipart/form-data") >= 0)) {
DataInputStream in = new DataInputStream(request.getInputStream());
int formDataLength = request.getContentLength();
byte dataBytes[] = new byte[formDataLength];
int byteRead = 0;
int totalBytesRead = 0;

while (totalBytesRead < formDataLength) {
byteRead = in.read(dataBytes, totalBytesRead, formDataLength);
totalBytesRead += byteRead;
}
String file = new String(dataBytes);
String saveFile = file.substring(file.indexOf("filename=\"") + 10);
System.out.println("saveFile=" + saveFile);
saveFile = saveFile.substring(saveFile.lastIndexOf("\\")+ 1,saveFile.indexOf("\""));
System.out.println("saveFile" + saveFile);
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;

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;

FileOutputStream fileOut = new FileOutputStream(saveFile);
fileOut.write(dataBytes, startPos, (endPos - startPos));
%>
<b>File <% out.println(saveFile); %> has been uploaded and inserted into Database.</b>
<%Connection con=null;
Statement pst=null;
String line = null;
String value=null;
try{
StringBuilder contents = new StringBuilder();
BufferedReader input = new BufferedReader(new FileReader(saveFile));
while (( line = input.readLine()) != null){
contents.append(line);
}
value = contents.toString();
System.out.println("Value:"+value);
Class.forName("com.mysql.jdbc.Driver");
con=DriverManager.getConnection
   ("jdbc:mysql://192.168.10.59/application?user=root&password=root"); 
pst=con.createStatement();
int val = pst.executeUpdate("insert into file(file_data) values('"+value+"')");
}
catch(Exception e)
{}
}
%>
</html>

The method request.getContentType() provides the content type information from the jsp header. Then check the two conditions ((contentType != null) && (contentType.indexOf("multipart/form-data") >= 0)). If the condition is true, create an object of DataInputStream and pass the request.getInputstream() method of Inputstream class to its object. A DataInputstream read primitive Java data types from an underlying input stream.
 
To get the length of the content type, the method request.getContentLength() is used. Convert the uploaded file into the byte by using while loop and pass the array of dataBytes to the variable file. To save file name, we are using substring() method. After creating sub-string of string, extract the index of file. Then create a new file of same name and write the content in the file using FileOutputStream.

The file to be inserted is to be read using buffered reader. A buffer content is created using StringBuilder contents = new StringBuilder(). The method contents.toString() is defined to convert the whole content of file into String.

To insert this file into the database, write the following:
1) Class.forName("com.mysql.jdbc.Driver") for loading driver for database.
2) con=DriverManager.getConnection("jdbc:mysql://192.168.10.59/application?user=root&password=root") for getting connection to the database and
3)
Query to insert data into the database.

Output will be displayed as:

After selecting the file, click the "Send File" button

A message has been displayed on the browser.

The file data.csv is inserted into the database.

Download Source Code

Advertisements

If you enjoyed this post then why not add us on Google+? Add us to your Circles



Liked it!  Share this Tutorial


Follow us on Twitter, or add us on Facebook or Google Plus to keep you updated with the recent trends of Java and other open source platforms.

Posted on: July 15, 2008

Related Tutorials

Ask Questions?    Discuss: To Upload and insert the CSV file into Database   View All Comments

Post your Comment


Your Name (*) :
Your Email :
Subject (*):
Your Comment (*):
  Reload Image
 
 
Comments
ben
September 9, 2011
query to insert file into the database

Hey guys need help.what is the query that i should use to insert the file into the database ? thks , I am new to web applications
susi
September 20, 2011
if somenone can help me

i have created upload_page.jsp but it doesn't work. after i click "Send File" the next page is blank. Why?
yung soul
March 21, 2013
csv file to sql database

thanks first of all..so what if i have a table Entite with these values(Entite,type_Entite,numero(which increments auotmatically) what do i need to change/add in this code...please cause i tried but it aint working
Sanjeev Sajjan
March 28, 2012
The *.csv file in which format ?

Hello, Whatever the code you providing this for which .csv file.
Sanjeev Sajjan
March 28, 2012
Inserting in the database is in only one row

Above code help me to insert the .csv file in the database but All the data its come in only one row is there any other way through that i can get All the data in different-2 column. Please provide me the code as soon as. Thanks: Sam
cezanne khan
June 6, 2012
database

thanks, but i have another question that i have transfer that data into database, but how i will be able to check that? I mean how can i check whether that table is stored or not? Also i wanted to store the given file into mysql database. How can i do that? Please help me
Premila Devi
July 2, 2013
Pls Help

I had done this : int val = pst.executeUpdate("Insert into file values(file_id,file_data)"); but my file_id is returning null..everytime i insert the data.csv.......pls help mw
gouda
October 23, 2013
Inserting in the database is in only one row

Above code help me to insert the .csv file in the database but All the data its come in only one row is there any other way through that i can get All the data in different-2 column. Please provide me the code as soon as.
DMCA.com