Month: October 2023

Huge excel worksheet rowcount. Not all rows are read from OleDBDataAdapter.

I had a problem reading Microsoft Excel Worksheet from C#. My worksheet at the least containts 50,000 rows. I use OleDBDataAdapter class to read every rows. The problem is that only 4,000 rows are being read.

I did scour over the internet and a fix was suggested and it was simply adjusting connection string settings that is being passed to the OleDBConnection class. The fix was to assign [IMEX=1]. My previous settings was [IMEX=2]. Displayed below is the string property that I pass to the OleDBConnection class constructor.

protected virtual string ConnectionString
{
  get
  {
    return
      String.Format(
        "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=1\";", SourcePath);
  }
}

I hope this helps. Happy Coding!