Error opening an excel file

[carpe2]

Hi,

I have a problem reading an excel file in .net

I have something like this:

 

string strConn = string.Empty;

strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +

"Data Source=D:\\ICRs.xls;" +

"Extended Properties=Excel 8.0;";

 

OleDbDataAdapter myCommand = new OleDbDataAdapter("SELECT * FROM [sheet1$]", strConn);

 

In the excel file I have a column with data like these, and the format is fixed, I can´t change it:

1256

1256

1256A

 

When it finds the row with 12356A it retrieves a System.DbNull, instead of 1256A.

Can somebody tell me how can I read the data???

 

Thanks in advance.

[VagabondSW]

I haven't run any tests on this yet, and it has been almost a year since I messed with Excel. However, I think you are running into a data type issue. If no type is specified, JET may use the first 20 rows to determine the data type for a particular column of data. In the example you give, if the first 20 or so rows are all Integer types, then any String value found after that may be read as DbNull.

 

Having said that, I remember having some exceptions thrown in that case, but I could be remembering it wrong or you could masking the exception with an incomplete Try-Catch block.

 

Sorry I couldn't be more help at this time, but at least we can take care of the easy stuff first.

[carpe2]

I solved the problem writing something like this in the connection string:

"Excel 8.0; HDR=NO; IMEX=1;"

 

But another problem appears, in some rows it retrieves 3.45678 e7, and if I convert the data into integer, I lose precision.

 

Does anybody know how to solve it??

Thanks.