Excel data type seems strange when use ado.net to select
Recently, we need import some data from excel to Oracle database. A strange thing confused me.
The excel file has a column, for example PhoneNumber, the format is 999999,
here are some example data:
- 123456
- 234561
- 345612
- 456123
- 561234
- 612345
- 000123
- 000231
- 000312
When i use the following code to select data, something is strange.
1: string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + ftpFile.PostedFile.FileName + ";"
2: + "Extended Properties=\"Excel 8.0;IMEX=1\"";
3: DataSet ds = new DataSet();
4: string strSql = "select * from [Sheet1$]";
5: using (OleDbDataAdapter adp = new OleDbDataAdapter(strSql, strConn))
6: {
7: adp.Fill(ds, "Phone");
8: }
view the ds data, you will find this:
- 123456
- 234561
- 345612
- 456123
- 561234
- 612345
if i change the number format to text, i still can not retrieve the number start with 000.
I don’t know why, finally i change every number to string in excel file. for example: 123456---> ‘123456 and 000123--->’000123. it works.
anyone who has a good solution for this?