Java: reading an int from Excel as string return as decimal?

Question

I'm trying to read an integer from an Excel file like this:

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
String myExcel = "jdbc:odbc:Driver={Microsoft Excel Driver (*.xls)};DBQ="";" +
                      "DriverID=22;READONLY=false";
        con = DriverManager.getConnection(myExcel);
        stmt = con.createStatement();
rs = stmt.executeQuery("select * from [users$] where userid=" + userid);
while(rs.next()) {
    System.out.println(rs.getString("phone"));
}

The problem: if we assume the phone in the Excel sheet = 123456, rs.getString("phone") returns the integer with decimal 0, like this "123456.0"

I know I can use rs.getInt("phone") instead, but the phone sometimes have characters like "+","#",etc.

What's the best way to use rs.getString to get integer as it is??

robbbert · Accepted Answer · 2010-11-09 14:34:02Z

1

Try adding

;IMEX=1

to the connection string. This instructs the driver to treat intermixed data (data of different data types) as text.

If it does not work, try adding the IMEX=1 instruction to the SQL statement. If this still doesn't work, there are the following options: Use Apache POI; convert the Excel sheet to CSV; use Office automation to explicitly set the column's data type; rework the value (i.e. by applying some substring operation, etc.).

answered Nov 9, 2010 at 14:34

robbbert

2,19315 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

evilReiko Over a year ago

It didn't work. Anyway, I solved temporary by doing some work around applying the substring

robbbert Over a year ago

Thanks for the feedback. - Working with Excel as a "database" is never fully reliable.

dogbane · Accepted Answer · 2010-11-09 14:15:55Z

0

You would have to read the phone number as a string and then parse it to get rid of unwanted characters. Finally, convert it to an int using Integer.parseInt(phoneNumberString).

answered Nov 9, 2010 at 14:15

dogbane

276k77 gold badges407 silver badges415 bronze badges

Collectives™ on Stack Overflow

Java: reading an int from Excel as string return as decimal?

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related