extract data from .xls file

Question

I want to extract data from a Excel file and store them into a access database, how to do this?

Answer 1

The Apache POI library provides access to Microsoft Office formats, including Excel.

To insert into a database, you'll need JDBC (and possibly additional frameworks if you want an ORM). Here's a tutorial on JDBC to get you started. You may want to check out Hibernate as a useful Java ORM.

Answer 2

You can use the Java Excel API to extract the data. You can find the javadoc here. There is also a programmer's guide on how to use JExcel

Answer 3

First, do you really need Java for this? The MSAccess software itself provides functionality to import data from an Excel file. It's much easier to do so.

If you really need Java for this, then you need to realize that this is in fact a two-step task:

1. Extract data from Excel into Java objects (e.g. List<List<String>> or List<Data>).
2. Save data from those Java objects into MSAccess.

For step 1 you need a Java API which is capable to extract data from an Excel file. Which one to use depends on the actual file format.

If it is a .xls file, you have in general two options: the aforementioned Apache POI HSSF and JExcelAPI. The POI HSSF is known to be memory hogging and its API is a bit opaque in use. Andy Khan's JExcelAPI is the better choice.

If it is a .xlsx file (the last x indicates that it's in OpenXML format instead of binary format), then you have next to the aforementioned Apache POI XSSF also the choice to to use OpenXML4J, which is -again- generally a better choice than the POI XSSF for the same reasons as the POI HSSF.

Now, for step 2 you need a Java API which can save data into a MSAccess file. You can use the JDBC API for this in combination with the builtin JDBC-ODBC bridge driver. A good starting point is the JDBC tutorial.