OLEDB connection does not read data from excel sheet

1
votes

i am getting a weird problem. i am using OLEDB for excel connection with connection string = Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\Execute.xls;Extended Properties=Excel 8.0;");

excel file contains columns with string/integer values. the problem is that sometimes connection read values from sheet absolutly fine but sometimes it missed out some data values and shows them as System.DBNull. the behavior is very inconsistent. please help.

4
c#

4 Answers

1
votes

My first guess would be to check for your regional parameters. Number formats would be different from one regional setting to another, and this could cause the problem. Although Excel is supposed to manage it for you automatically, some times it just doesn't as it is confused or something, then render some strange data like those DBNull values.

0
votes

Here is your problem. IIRC, the driver only reads the first 8 rows of data and determines the data type of the columns based on that.

So let's say, in the first 8 rows of column 1, you only have numbers. The driver will decide that the column is an integer. Then, if it encounters a string in row 9, it will not be able to convert it to an integer and thus return DBNull to you.

There are several things you can do.

  • Pre-process your spreadsheet and convert everything to strings
  • There is a registry entry (the location escapes me at the moment), that allows you to increase the number of rows that the driver uses to determine data type.
  • Use a commercial Excel reader control
  • Open the sheet via the Excel Interop library and read the cells on your own
0
votes

use this code

string pathcpnn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source =" + textBox1.Text + ";Extended Properties=\""Excel 8.0;HDR=Yes;IMEX=1;";";

OleDbConnection con = new OleDbConnection(pathcpnn);

OleDbDataAdapter myDataAdapter = new OleDbDataAdapter("Select * from[" + textBox2.Text + "$]", con);

myDataAdapter.Fill(dt);

DAtagridview1.datasource=dt;