1

I'm reading an Excel file with OLDB Connection using this code

        var connectionString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0; data source={0}; Extended Properties=Excel 8.0;", fileName);

        var fileName = string.Format("{0}\\s23.xls", Directory.GetCurrentDirectory());
        var adapter = new OleDbDataAdapter("SELECT * FROM [TEJ3$]  ", connectionString);
        DataTable dt=new DataTable();
        adapter.Fill(dt, "Table1");

and after runing this code my data table is filled. But I have a column that has many string cells and few empty cells ; in excel file this cells have numeric values.

Someone has an idea?

Mark Canlas
  • 9,385
  • 5
  • 41
  • 63
  • 1
    Can you post some sample data? the OleDb provider, bases data type on the first 20 records. – Mitchel Sellers Dec 21 '09 at 16:33
  • @Mitchel : can we configure this 20 records to some other value like 50 records. My Question is it configurable. I am asking this in reference with http://stackoverflow.com/questions/6913497/unkown-problem-while-exporting-excel-to-system-datatable – Pratik Aug 02 '11 at 14:25

4 Answers4

2

Check the first examples here: http://www.connectionstrings.com/excel

What often goes wrong is that Excel will estimate the type of a column based upon the first X rows. When after that the values don't match, these rows get empty values. I'm afraid that going into the registry is sometime the only way to get the Excel driver to scan all rows first (as described in the connectionstrings.com article).

Play around with the HDR and IMEX settings in your environment. In some cases that will help as well.

Teun D
  • 5,045
  • 1
  • 34
  • 44
1

I had this exact problem and solve it with using IMEX setting. In case others are wondering how to include the IMEX, here is what I have on my connection string

string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties='Excel 8.0;IMEX=1';Data Source={0};";
connectionString = string.Format(connectionString, excelWorkbookPath);
Fadrian Sudaman
  • 6,405
  • 21
  • 29
  • ;IMEX=1'; =>> It gives error : System.Data.OleDb.OleDbException was unhandled by user code Message="Could not find installable ISAM." Source="Microsoft JET Database Engine" ErrorCode=-2147467259 – Pratik Aug 02 '11 at 14:34
  • You do not have the right driver installed. Follow the link from Teun answer and try the connection string with HDR setting – Fadrian Sudaman Aug 02 '11 at 14:36
  • Do I need to install it again. If so then can you please provide the appropriate link. – Pratik Aug 02 '11 at 14:40
  • Also is IMEX settings valid for ASP.NET because I think its not as per this : http://stackoverflow.com/questions/3232281/oledb-mixed-excel-datatypes-missing-data/3232374#3232374 – Pratik Aug 02 '11 at 14:42
  • It used to be installed with older version of Office. YOu will need to google this yourself. Why dont you try my suggestion above to follow Teun link? There is example of using different connection string and provider to do what you need to do. – Fadrian Sudaman Aug 02 '11 at 14:44
  • I cant see anywhere in the link you provide that imex is invalid. If you read carefully, most of the answer suggest that using imex setting solved their problem. – Fadrian Sudaman Aug 02 '11 at 14:46
0

SpreadsheetGear for .NET will let you load Excel workbooks from C# and access the underlying cell values or the formatted values in any order no matter how the workbook is laid out or what the types of the cells are.

You can see live ASP.NET samples here and download the free trial here if you want to try it yourself.

Disclaimer: I own SpreadsheetGear LLC

Joe Erickson
  • 7,077
  • 1
  • 31
  • 31
-1

Just make sure that your excel file is not open. Close your excel application & then start your program.

Sunil

Sunil
  • 1