CODEWITHSUNDEEP
javaexcelgetstring
evilReiko
evilReiko

Reputation: 20503

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

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

Upvotes: 0

Views: 1424

Answers (2)

robbbert
robbbert

Reputation: 2193

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.).

Upvotes: 1

dogbane
dogbane

Reputation: 274828

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).

Upvotes: 0

Related Questions