1

I am using OleDbDataReader (C#) to read 93081 rows from an excel file but it doesn't seem to read all the rows. It only reads 27545 out of the 93081 rows. When I read another file which only has 15941 rows, it doesn't pose any problem and retrieves all the rows.

I have included IMEX=1 in connection string as mentioned by many people online, even though the data type is the same throughout the excel file.

What is the maximum limit of the number of rows OleDbDataReader can read from an excel file?

Edit: Added code 

var loc = "C:\\Users\\random\\Desktop\\Test.xlsx";
var myConnection = new OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source=\""+ loc + "\";Extended Properties=\"Excel 8.0;IMEX=1\";");
var myCommand = new OleDbCommand();   

myConnection.Open();
myCommand.Connection = myConnection;
var sql = "Select * from [Sheet2$]";
myCommand.CommandText = sql;
var dataReader = myCommand.ExecuteReader();
var insert = "";
var result = "";

DataTable table = new DataTable();
table.Columns.Add("ID", typeof(int));
table.Columns.Add("Request", typeof(string));
table.Columns.Add("Decoded_Request", typeof(string));

while (dataReader.Read()){
      insert = dataReader["Request"].ToString();
      result = HttpUtility.UrlDecode(insert);
      table.Rows.Add(i, insert, result);
      i++;
      Console.WriteLine(i);
}

Thank you!

user5711693
  • 113
  • 5
  • 17
  • can you load the file into a datatable and see if it loads all of the data..? you would have to write your own parser or use a 3rd party tool to do that also there are plenty of examples on line in regards to exporting excel to a datatable and 27,545 rows sounds like you're doing something wrong can you show any existing code..? – MethodMan Jan 01 '16 at 22:22
  • Can you post your reading code? AFAIK a DBDataReader reads one line at a time so there is no theoretically any limits. However if you have some kind of exception during the read loop ..... – Steve Jan 01 '16 at 22:25
  • @MethodMan Thanks for replying. I am loading data from the file into a data table after reading it using data reader, it seems to be loading fine with small files but not large files - not really sure why this is the case. I am thinking of changing my code and just reading from notepad and formatting file in c# as required and then adding in data table. Was just wondering why datareader method doesn't work. I have added code. – user5711693 Jan 01 '16 at 22:56
  • @Steve Thank you for replying. Yes I was looking online and couldn't find a limit either so thought would post to clarify this before trying alternative way. Btw I have added some sample code. Is it possible that this is happening due to file format .xlsx? – user5711693 Jan 01 '16 at 22:58
  • 1
    _insert = dataReader["Request"].ToString();_ this line could cause problems if there is a null value in your Request column. Check the file if you have any empty cell in this column. Do you have any try/catch around this code? – Steve Jan 01 '16 at 23:01
  • @Steve I have checked by filtering the column - there are no null values in the column. No I don't have any try/catch blocks around this code. – user5711693 Jan 01 '16 at 23:13
  • 1
    Sorry but I can't find anything that should stop this loop until the end of the file. You could try to setup a conditional breakpoint inside the loop. Set it to stop code execution when _i == 27544_ and then proceed step by step until you are able to understand what kicks your code out of the loop – Steve Jan 01 '16 at 23:36
  • @Steve Thank you for your suggestion. Yeah I have been stuck all day, not really sure why my code isn't giving the correct results. I will do what you suggested and will post a solution if I manage to find it. – user5711693 Jan 01 '16 at 23:41
  • 1
    I know this is an old question, but 27545 is exactly 93081-65536. That would be your answer if you took your number of rows and tried to shove it into a 16-bit int. If you understand binary math, then 93081 & 0xFFFF = 27545. Older versions of excel (and Excel 8.0 you've specified in your connection string is certainly old) only supported 65535 rows I believe, so it looks like you've rolled over that number. – Robert McKee Nov 15 '18 at 16:21

1 Answers1

1

For those who come in to find answer ...I think you are using old excel driver . Excel had limitation of 65536 rows .

In this example rows 27546 = 93081 - 65536

Pushpendra
  • 820
  • 7
  • 11