Results 1 to 3 of 3
  1. #1
    nehakuls is offline Member
    Join Date
    Nov 2009
    Posts
    8
    Rep Power
    0

    Default How to fetch integer data from excel

    Hi All,

    I have used following query to fetch data from excel file:

    Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
    Connection con = DriverManager.getConnection( "jdbc:odbc:Driver={Microsoft Excel Driver (*.xls)};DBQ=" + "C:\\file1.xls" +";"+ "DriverID=22;READONLY=false" );
    Statement st = con.createStatement();
    ResultSet rs = st.executeQuery( "Select * from [Sheet1$]" );
    ResultSetMetaData rsmd = rs.getMetaData();
    int numberOfColumns = rsmd.getColumnCount();
    while (rs.next()) {
    for (int i = 1; i <= numberOfColumns; i++) {
    if (i > 1) System.out.print(", ");
    String columnValue = rs.getString(i);
    System.out.print(columnValue);
    }
    System.out.println("");
    }

    Its retrieving data from excel. But the problem is it returns decimal data even if u hv integer data in excel file.
    For example I have Year as 2001 in my excel file and it returns 2001.0.

    I tried to type cast it into integer but at run I can not get whether column has ineteger data or deciamal or alphanumeric data. the column id for year may change.

    plz help me.

  2. #2
    travishein's Avatar
    travishein is offline Senior Member
    Join Date
    Sep 2009
    Location
    Canada
    Posts
    684
    Rep Power
    6

    Default

    I think the internal data storage for number types in excel is always a decimal data, even if using POI or jExcel. to get an integer, you would need to know to expect that field as to be an integer before hand and run a converter to truncate the decimal places from it.

    since this jdbc read string, you could try some heuristics,
    Java Code:
    // psudo code
     if field parses as double {
       if  field contain a fractional value {
         field is a double
       }
       {
          field is an integer
        }
    }
    else {
      field is a string
    }

  3. #3
    nehakuls is offline Member
    Join Date
    Nov 2009
    Posts
    8
    Rep Power
    0

    Default

    I am using jxl itself...but as we have to apply some filter criteria at run time....its quite a long process...if we have a choice using sql queries then we can do it very easily.....

Similar Threads

  1. To populate the data processed into a .csv or excel file
    By jaiminparikh in forum Advanced Java
    Replies: 2
    Last Post: 02-26-2009, 11:56 PM
  2. how to fetch 20,000 records from excel sheet & insert into access database using Java
    By santosh_tamse in forum JavaServer Pages (JSP) and JSTL
    Replies: 0
    Last Post: 02-22-2009, 10:24 AM
  3. Recovering data from Excel spreadsheet
    By Anubis in forum SWT / JFace
    Replies: 0
    Last Post: 12-17-2008, 08:28 PM
  4. How to read data from excel and store it in db
    By saran123 in forum New To Java
    Replies: 5
    Last Post: 10-03-2008, 10:19 AM
  5. Save data from Excel sheet to servlet
    By chandu.v in forum Java Servlet
    Replies: 2
    Last Post: 04-10-2008, 01:51 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •