I came across the problem of reading from an excel sheet while competing for MUPHORIA organized by Mu Sigma Inc. . I used following procedure to do so. Although this jdbc code reads data efficiently, it has a limitation. The ResultSet object can read data upto 256 fields irrespective of number of rows as I experienced .
Follow these steps :
Step 1: create an excel sheet, rename it as per your choice( I named it student ) and save it at any location of your choice.
Step 2: Now create a DSN (Data Source Name) for the concerned excel sheet.
Start->Control Panel->Administrative Tools->Data Sources (ODBC)>”System Dsn” tab->
Click on Add Button–>Select “Microsoft Excel Driver (*.xls,*.xlsx,*.xlsm,*.xlsb)”–>
Enter "Data Source Name" >finish
Step 3: Now compile following jdbc code. (change the name "vikku" in line 13 with your Data Source Name and replace "student" in line 20 with your own excel table name.
1 //ConnTest.java 2 3 /** 4 * 5 * @author VIK 6 */ 7 import java.sql.*; 8 9 public class ConnTest { 10 11 public static void main(String... args) throws Exception { 12 Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); 13 Connection con = DriverManager.getConnection("jdbc:odbc:vikku", "", ""); 14 if (con == null) { 15 System.out.println("Access denied..."); 16 } else { 17 System.out.println("Connection Successful..."); 18 } 19 Statement st = con.createStatement(); 20 ResultSet rs = st.executeQuery("select * from [student$]"); 21 ResultSetMetaData rsmd = rs.getMetaData(); 22 int colcount = rsmd.getColumnCount(); 23 while (rs.next()) { 24 System.out.println(""); 25 for (int i = 1; i < colcount; i++) { 26 System.out.print("\t" + rs.getString(i)); 27 } 28 } 29 rs.close(); 30 st.close(); 31 con.close(); 32 } 33 } 34
I didn't use the above jdbc code for reading data from excel. I saved the document as .csv( comma delimited) and then read that CSV file which I will explain in another post.
No comments :
Post a Comment