aspose file tools*
The moose likes JSP and the fly likes uploading the content of a .csv file in database Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Java » JSP
Bookmark "uploading the content of a .csv file in database" Watch "uploading the content of a .csv file in database" New topic
Author

uploading the content of a .csv file in database

premchand Singh
Greenhorn

Joined: Mar 12, 2012
Posts: 28
Hi i am trying to upload the content of a csv file to the oracle data base . below is the code i am trying...
page_2.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_2.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>

-----------------------------
upload_page_2.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("oracle.jdbc.driver.OracleDriver");
con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE","unisys","unisys");
pst = con.createStatement();
int val = pst.executeUpdate("insert into file1(file_data) values('"+value+"')");

} catch (Exception e) {
}
}
%>

</html>



The code is running successfully but i am not able to see the data in the oracle that i am inserting in the oracle database. Can any body please help me out.

Thanks in advance:
Premchand Singh
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 42602
    
  65
I'll refrain from pointing out that is considered bad design to do DB stuff (or any processing, really) in a JSP -well, I guess I didn't after all-, let's just suffice to say you should never do this:

} catch (Exception e) {
}

You simply must handle exceptions properly. At the very least, log them to somewhere where you' ll see them. Another issue is that you're not closing the DB connection.

Are you certain that "value" contains no single quotes? I'm asking because the insert statement will fail if it does. And instead of creating SQL by string concatenation you should use a PreparedStatement instead. As it is, the code is open to SQL injection attacks.

Also, "new String(dataBytes)" is a dangerous thing to do, as it is platform-dependent; you should specify the encoding.

And lastly, I'd use one of the available CSV processing libraries to do the parsing; there's more to CSV than your code handles. The AccessingFileFormats page lists several such libraries. And you should use a library like Apache Commons FileUpload to handle the file upload; there's no need to write HTTP parsing code.

And on a final note, please UseCodeTags any time you post code of a substantial length; it makes reading it so much easier.


Ping & DNS - my free Android networking tools app
 
Don't get me started about those stupid light bulbs.
 
subject: uploading the content of a .csv file in database