To upload an excel file as a clob

Question

Could some one give an idea about how to convert an excel file into a CLOB in oracle through JDBC. I would like to know how to covert excel file into a String using API available as part of JDK and the conversion from string to clob should be straight forward. Thanks in advance. If a similar question was already raised, kindly provide me the link.

Answer 1

You will need a library out side of the standard java api to read a binary excel document.

You could use the JExcelApi to read in the Excel document then create a CSV String as you read in the document.

If you don't want to use an outside library then I would change the field type to blob and use the setBlob method of java.sql.PreparedStatment passing it a FileInputStream.

Answer 2

When you need to parse the data (read the contents) you can use a library like Apache POI

If you want to use the only standard API, then save the excel as CSV file and process the file as a comma seperated value list. That can easily be done with readline to read each line and split that line to values using the split method on a String.

If you do not need to parse data you should stream the data directly from file to a CLOB and not first transform the excel to a String An example can be found here: http://www.oracle.com/technology/sample_code/tech/java/sqlj_jdbc/files/advanced/LOBSample/LOBSample.java.html