Reputation: 20503
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
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
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