File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes Upload the Excel data in the Oracle Database Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "Upload the Excel data in the Oracle Database" Watch "Upload the Excel data in the Oracle Database" New topic

Upload the Excel data in the Oracle Database

sudha swami
Ranch Hand

Joined: Apr 24, 2007
Posts: 186
I want to upload the excel data into the oracle Table using SQL Script. The table has already the data, when i upload the excel basically it should overwrite the data in the table.
Paul Sturrock

Joined: Apr 14, 2004
Posts: 10336

Are you trying to persist the excel document as a whole, or extract the excel document's contents and upload them?

If its the former you are trying to do, you need to use a BLOB data type. Check Oracle's JDBC documentation which has an example.

If its the later, then your first step is to extract the contents for the excel document. For this you'll need something like Apache POI. Once you have the contents, the JDBC code should be fairly straight forward.

JavaRanch FAQ HowToAskQuestionsOnJavaRanch
Scott Selikoff
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3753

If you're looking for a magic solution that will upload excel sheet into a database in one line of code, you can pretty much stop looking. Keep in mind, databases are far more structured than excel files. For example, excel files can have header lines, footers, and filled with all different calculations on a single sheet. Databases, on the other hand, are organized primarily as field-specified tables with zero headers/footers/calculations (unless you count triggers/SP/views but we'll ignore them for now).

In short, databases are highly structured and that structure needs to be added to the excel file during the conversion. There's no (useful) general tool that will add structure and convert to a database automatically, so the conversions have to be specified by hand (or via some code-assisting tool, for example xml based readers).

So if you have a program that writes the contents of an excel file to System.out.println() for example, you just need to convert the output into sets of SQL statements that correspond to your database structure.
[ October 02, 2007: Message edited by: Scott Selikoff ]

[OCA 8 Book] [Blog]
Michael Ku
Ranch Hand

Joined: Apr 20, 2002
Posts: 510
I have used Jexcel in the past to read in a Excel file on the server and persist the entries to a DB
I agree. Here's the link:
subject: Upload the Excel data in the Oracle Database
jQuery in Action, 3rd edition