Simple way for Applications to read Excel Workbooks
QUERY DATA FROM EXCEL FILE
-Jayanth Raj
If you want to retrieve or upload data from an Excel file having more than one sheet and large number of data elements, it is generally a complicated process to import such data elements into an oracle database.
The following approach is one possible way of simplifying the process of reading data from an excel workbook with multiple sheets.
We can retrieve data by writing a simple query like,
SELECT [column1.name], [column2.name], [column3.name] FROM
[Sheet1$]
Or
SELECT [column1.name], [column2.name], [column3.name] FROM
[Sheet1$] WHERE [column1.name] = ‘TestData’
Here I have explained how we can achieve this using a simple java code.
1. Create a DSN for Excel file
- Add new DSN
- Select Microsoft Excel Driver – Driver do Microsoft Excel [*.xls]
- Provide a Data Source Name
- Select Workbook – Specifying the Excel file path
2. Write a java class to retrieve the data from the Excel file
- Load JDBC driver class – use Jdbc Odbc Driver, Eg.
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
- Create a Connection using Excel DSN URL , Eg.
Connection conn = DriverManager.getConnection("jdbc:odbc:<DSN name>”)
- Create a statement, Eg.
Statement stmt = con.createStatement();
- Data is retrieved from the Excel sheet using a query mechanism
Statement stmt = conn.createStatement();
try {
ResultSet rs = stmt.executeQuery( "SELECT [column1.name], [column2.name], [column3.name] FROM [Sheet1$] WHERE [column1.name] = ‘Test’ " );
try {
while ( rs.next() ) { // Retrieve data }
} finally { rs.close();
}
} finally { stmt.close();
}
(The author Jayanth Raj is a Software Engineer at Binary Spectrum).