I am using OleDbDataAdapter to fill a dataset with data from an Excel document.

The OleDbConnection connection string's extended properties are: Extended Properties=\"Excel 8.0;IMEX=1;HDR=Yes;\""

I have noticed that if a cell contains a number but the cell is formatted as text, that the number returned in the dataset is Exponential.

E.g.  number 90090034 would be returned as 9.0090034+007

If the cell is formatted as a number 0dp, then the dataset contains the correct number.

Does anyone know why this is happening and a solution to resolve this exponential issue?

Regards,

B

hi

this is all to do with the internal data types of excel

From .net use can always cast between data types easily ( until it is permitted )

So y don't try that

Hope this helps

Im having problems with importing Excel too.

Govner: can i see your code? I have made something simular to you, i think, and i don't even get the numbers back from the excel. When i look in a cell, that i know contains a number formated as a number, i get a DBNull returned...

I have to go into Excel and make the cell "Number formated as text", before i can read it. Any i deas?

1    DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.OleDb");
2    
3                try
4                { 
5                    using (DbConnection connection = factory.CreateConnection())
6                    {
7                        connection.ConnectionString = connectionString;
8    
9                        using (DbCommand command = connection.CreateCommand())
10                       {
11                           // Sheet1$ comes from the name of the worksheet
12                           command.CommandText = "SELECT * FROM [Sheet1$]";
13   
14                           connection.Open();
15                           
16                           using (DbDataReader dr = command.ExecuteReader())
17                           {                      
18                               while (dr.Read())
19                               {                              
20                                   //This one returns DbNull
21                                   //I have removed alot of business logic from this while
22                                   dr[5].ToString().Trim();
23                                      
24                                   }                              
25                                   
26                               }
27                           }
28                       }
29                   }
30               }

III found this out my self :)

You need to set the IMEX=1

Like this: