Read Excel from JDBC.

[Vishwanath Bannur]

Hi,
I am reading excel file from jdbc as follows.

code:
--------------------------------------------------------------------------------

try{Class.forName( "sun.jdbc.odbc.JdbcOdbcDriver" );con = DriverManager.getConnection("jdbc dbc river={Microsoft Excel Driver (*.xls)};DBQ=" + filename);System.out.println("Get connection");//con = DriverManager.getConnection("jdbc dbc:ExcelTest","","");stmnt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);rst=con.getMetaData().getTables(null, null, "%", null);rst.next();String SheetName = rst.getString(3);String query = "Select * from ["+SheetName+"]" ;rs = stmnt.executeQuery( query );ResultSetMetaData rsmd = rs.getMetaData();int ColCount = rsmd.getColumnCount(); if (rs!= null){rs.last();totalRecords= rs.getRow();rs.beforeFirst();System.out.println("Total Records from Excel Spreadsheet" + totalRecords);if(totalRecords > 0){PONum = new String[totalRecords];POVersion = new String[totalRecords];BillingTelNum = new String[totalRecords];while (rs.next()){String pon = rs.getString(1);if(null!=pon){PONum[i] = pon;}elsePONum[i] = null;String pov = rs.getString(2);if(null!=pov){POVersion[i] = pov;}elsePOVersion[i] = null; String telnum = rs.getString(3);if(null!=telnum){BillingTelNum[i] = telnum.substring(0,telnum.indexOf("."));}elseBillingTelNum[i] = null;}}}catch(SQLException sqle){sqle.getMessage();}catch(Exception e){System.out.println(""+e.toString());}finally{try{rst.close();rs.close();stmnt.close();con.close();}catch(Exception e){System.err.println(e);}

--------------------------------------------------------------------------------



My excel sheet contain 3 cols namely Number, Version, Telephone Num.As shown below
Number version tele num.
1 1234 12345
2 abcd 123788
3 1223 17889898

my java code is reading fine when the data of data type are same in cols.

But its giving nullpointerexception when the data of datatype is different in cols.

For example in version cols contain 2 number values and one string values.
that time its not reading the version values.
Its only working fine version cols contain either all are string values or integer values.But i need to enter both string as well as integer.

Please help me if you know the solution.

Thanks
Vishwanath

[Ulf Dittmer]

The code is just about impossible to read and comprehend as it is. Please post it properly formatted, and UseCodeTags so that the formatting stays intact. Also, indicate the line in which the exception occurs.

[Vishwanath Bannur]

Originally posted by Ulf Dittmer:
The code is just about impossible to read and comprehend as it is. Please post it properly formatted, and UseCodeTags so that the formatting stays intact. Also, indicate the line in which the exception occurs.

Here is my code with well format.

 try
{
Class.forName( "sun.jdbc.odbc.JdbcOdbcDriver" );
con = DriverManager.getConnection("jdbc:odbc:Driver={Microsoft Excel Driver (*.xls)};DBQ=" + filename);
System.out.println("Get connection");
//con = DriverManager.getConnection("jdbc:odbc:ExcelTest","","");
stmnt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);

rst=con.getMetaData().getTables(null, null, "%", null);
rst.next();
String SheetName = rst.getString(3);
String query = "Select * from ["+SheetName+"]"  ;
rs = stmnt.executeQuery( query );
ResultSetMetaData rsmd = rs.getMetaData();
int ColCount = rsmd.getColumnCount();
    
if (rs!= null)
{
rs.last();
totalRecords= rs.getRow();
rs.beforeFirst();
System.out.println("Total Records from Excel Spreadsheet" + totalRecords);

if(totalRecords > 0)
{
PONum = new String[totalRecords];
POVersion = new String[totalRecords];
BillingTelNum = new String[totalRecords];
while (rs.next())
{
String pon = rs.getString(1);
if(null!=pon)
{
PONum[i] = pon;
}
else
PONum[i] = null;
String pov = rs.getString(2);
if(null!=pov)
{
POVersion[i] = pov;
}
else
POVersion[i] = null; 
String telnum = rs.getString(3);
if(null!=telnum)
{
BillingTelNum[i] = telnum.substring(0,telnum.indexOf("."));
}
else
BillingTelNum[i] = null;
}
}
}
catch(SQLException sqle)
{
sqle.getMessage();
}
catch(Exception e)
{
System.out.println(""+e.toString());
}
finally
{
try
{
rst.close();
rs.close();
stmnt.close();
con.close();
}
catch(Exception e)
{
System.err.println(e);
}   

[Ulf Dittmer]

The code seems to check for null values - in which line are you getting an exception?

catch(SQLException sqle)
{
sqle.getMessage();
}
catch(Exception e)
{
System.out.println(""+e.toString());
}

This is not good exception handling. The first exception is ignored completely, while the second does not print a useful error message. Much better to print a stack trace so that you know in which line an exception occurred, and during which method call.

[Vishwanath Bannur]

Originally posted by Ulf Dittmer:
The code seems to check for null values - in which line are you getting an exception?

catch(SQLException sqle)
{
sqle.getMessage();
}
catch(Exception e)
{
System.out.println(""+e.toString());
}

This is not good exception handling. The first exception is ignored completely, while the second does not print a useful error message. Much better to print a stack trace so that you know in which line an exception occurred, and during which method call.

Hi,
I am getting "[Microsoft][ODBC Excel Driver] Numeric field overflow"
When a cols contain both string and integer value in excel sheet.

[Ulf Dittmer]

We still don't know in which line of code this happens, and during which operation...

By the way, is the variable "i" incremented for each record read from the DB?
[ January 07, 2008: Message edited by: Ulf Dittmer ]

[Vishwanath Bannur]

Originally posted by Ulf Dittmer:
We still don't know in which line of code this happens, and during which operation...

I have excel sheet as shown below.

Numberversiontele num.
1abcd12345
21234123788
3abcd17889898

When i tried to access the 2nd column of 2nd row data its throwing error:
"[Microsoft][ODBC Excel Driver] Numeric field overflow"

(When a cols contain both string and integer value in excel sheet.)

For accessing data am using following code:

  ResultSet.getString(2);  

Please let me know if are you not yet clear.
But please try to find out the solution.

-Vishwanath

[Vishwanath Bannur]

Originally posted by Ulf Dittmer:
We still don't know in which line of code this happens, and during which operation...

By the way, is the variable "i" incremented for each record read from the DB?

[ January 07, 2008: Message edited by: Ulf Dittmer ]

Yes the variable "i" will increament every once a loop.