CODEWITHSUNDEEP
javaoracle-databaseexcelms-accessoffline
Adamanusia
Adamanusia

Reputation: 35

How to Store Oracle database and convert it to msaccess/excel

i have a problem,one of my client need

a program that will connect to oracle database server,and download/store the table to a local file(ex; microsoft access) like a conversion after that client can input an excel file and it will be added to that local file

my client want the local file/database can be stored offline,inside program folder They want the program do all the job so they only give me a user&pass to their database with some example of the tables and the excel that will be inputed from the client

and the program will do the rest download the tabel,convert,input,and the last it will print the data

i know that many tutorials come with the idea to connect / update oracle with java (with OLDBC-JDBC) or open&modify msaccess file(with excel conversion)

but to combine them all?how can you do this in java?

I already saw someone do this,but in VB6 and his code is a bit messy

FYI: the data is a BIG one,its use about 600.000 rows and it is updated once per month

Upvotes: 1

Views: 1307

Answers (2)

fvu
fvu

Reputation: 32953

An approach you might want to explore if the desired output is MS Access is Jackcess. One of the examples they give on their webpage is copying an external table into MS Access, and this is all the code you seem to need:

Database.open(new File("my.mdb")).copyTable("Imported", resultSet);

I don't have first-hand experience with that library, but it looks quite nice.

For Excel output I agree with Chris, POi is the way to go. However, there's also a library called jXLS, it's a layer on top of POI and it simplifies the creation of formatted XLS sheets a lot, check out this sample that converts a JDBC resultset (the result of you querying Oracle) into a nicely looking Excel file.

EDIT: based on your comment on Chris' answer, if actually all you get is the db coordinates and user and pass, you'll have to:

Step 1: get the table names using DatabaseMetaData, schematically like this:

Connection c = DriverManager.getConnection (...);
DatabaseMetaData md = c.getMetaData();
ResultSet rs = md.getTables(null, null, "%", null);
while (rs.next()) {
  System.out.println(rs.getString(3));
}

Step 2: iterate over the obtained list of tables and perform the table copy as shown above

Upvotes: 0

Chris
Chris

Reputation: 5654

If only the data is needed, why not download it as excel which can be rendered pretty easily using Apache POI ? Even excel can be served as datasource (just not MS-Access). Is your requirement only to convert it to MS-Access ? Please add more information about your problem statement.

Upvotes: 1

Related Questions