Fill DataSet from OleDbDataAdapter

Question

I'm using OleDbDataAdapter to read the content of an Excel sheet in a dataset. The Excel sheet consists of 20391 rows, the dataset reads the total number of rows when running it on my local machine, but when running the code on an IIS7.5 server it reads only the FIRST 12463!!

My connection string:

switch (strFileType.Trim())
       {
           case ".xls":
               connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strNewPath +
                            ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
               break;
           case ".xlsx":
               connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + strNewPath +
                            ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
               break;
       }

var query = "SELECT * FROM [" + excelSheets[0] + "]";

//Create the connection object
var conn = new OleDbConnection(connString);
//Open connection
if (conn.State == ConnectionState.Closed) conn.Open();
//Create the command object
var cmd = new OleDbCommand(query, conn);
var da = new OleDbDataAdapter(cmd);
var ds = new DataSet();
da.Fill(ds);

Is there a way to divide da.Fill result in multiple datatables?

Why do you think spreading the data to multiple datatables will solve the problem of the missing data? I'm guessing you have a different excel file on the server. — Steve Wellens, Apr 01 '13 at 01:04
can you use a `try-catch` to check if something occurred during the dataset filling? — roybalderama, Apr 01 '13 at 01:38
@SteveWellens I checked the file on the server and its the same. I forgot to say that it reads only the FIRST 12463. — Heba El-Fadly, Apr 01 '13 at 06:59
It is odd that it works locally, but not on the server. However, this could be for many reasons; different version of DAO libraries etc. See http://stackoverflow.com/questions/7321266/oledb-connection-not-reading-all-the-rows-from-excel-file for example. Note that you could also try changing your query to: `"SELECT * FROM [" + excelSheets[0] + "$A1:Z50000]"` - i.e. try to query a big range explicitly to see what happens. — dash, Apr 01 '13 at 10:45
Further, you might want to instead consider http://exceldatareader.codeplex.com/ or http://www.filehelpers.com/ to avoid handling this all yourself. — dash, Apr 01 '13 at 10:50
Another thought....is it possible IIS is setup to do bit throttling which would limit the amount of data transferred? — Steve Wellens, Apr 01 '13 at 14:13
@SteveWellens I think so, but don't know where could I found that setting on the IIS, do u have any idea? — Heba El-Fadly, Apr 01 '13 at 23:08
I have no idea. Bit throttling is probably the wrong term, limiting bandwidth may be a better thing to search for. I would also look in the IIS logs, maybe something is in there explaining what is happening. — Steve Wellens, Apr 02 '13 at 04:19
@dash I turned to exceldatareader.codeplex.com and it works like a charme. Thanks A LOT. — Heba El-Fadly, Apr 02 '13 at 11:45

score 1 · Accepted Answer · answered Apr 02 '13 at 11:56

I did what @dash suggested and used Excel Data Reader and it works correctly.

here is the code

        FileStream stream = File.Open(strNewPath , FileMode.Open, FileAccess.Read);            
        //1. Reading from a binary Excel file ('97-2003 format; *.xls)
        //IExcelDataReader excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
        //...
        //2. Reading from a OpenXml Excel file (2007 format; *.xlsx)
        IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);;                      
        excelReader.IsFirstRowAsColumnNames = true;
        DataSet result = excelReader.AsDataSet();

Fill DataSet from OleDbDataAdapter

1 Answers1

Linked