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??

score 1 · Answer 1 · answered Nov 09 '10 at 14:34

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 09 '10 at 14:34

robbbert

2,183
15
15

It didn't work. Anyway, I solved temporary by doing some work around applying the substring – evilReiko Nov 20 '10 at 07:32
Thanks for the feedback. - Working with Excel as a "database" is never fully reliable. – robbbert Nov 20 '10 at 09:30

score 0 · Accepted Answer · answered Nov 09 '10 at 14:15

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 09 '10 at 14:15

dogbane

266,786
75
396
414

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

2 Answers2