How to count empty rows when reading from Excel

5
votes

I'm using OLEDB to connect and read through data from an Excel spreadsheet. I have IMEX="1" and everything works ok. My problem is the sheets I'm reading from may start with several empty rows and the number of empty rows is important. For example, if I was reading a 5x5 grid like:

- - - - -
- - - - -
2 - 3 3 8
- - - - -
- - 5 2 2

where '-' represents an empty cell. The fact that the first two rows are empty is important. The size of the grid is dynamic. My code appears to be ignoring the first empty rows. But deals with the empty row at line 4 ok.

How can I count the number of empty rows at the start of an Excel sheet using OLEDB?

I'm restricted to using OLEDB, I wouldn't if I didn't have to ;-)

using (var adapter = new OleDbDataAdapter("SELECT * FROM [" + worksheetName + "]", connString)) {
  var ds = new DataSet();
  adapter.Fill(ds, "FareChart");
  table = ds.Tables["FareChart"];
}

Connection string:

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\Windows\\TEMP\\e1842f90-74a7-42f2-a6fa-208396a1072e;Extended Properties=\"Excel 8.0;IMEX=1;HDR=No\""

UPDATE

Specifying '.xls' as the file extension in the connection string fixed this issue and correctly reads the empty rows at the start.

3
c#exceloledb
Also, using '-' or '0' to represent an empty cell is not an option, the cells must be empty. The data is actually more complex than the example I have given and we do not have control over the formatting. – RandomDev
not sure if this helps: stackoverflow.com/questions/1138197/… – Davide Piras
What is your ConnectionString? – NaveenBhat
In your connectionString, you need to specify the excel fileName with extension. Like: myExlFileName.xls – NaveenBhat
@Knvn it was the missing '.xls' in the connection string that was the cause. – RandomDev

3 Answers

1
votes

I think your problem is with your connection string. I tested the below code and it worked for me:

     DataSet Contents = new DataSet();
     using (OleDbDataAdapter adapter = new OleDbDataAdapter("select FirstName,LastName,Email,Mobile from [" + mySheet + "]", connection))
     {
         adapter.Fill(Contents,"MyTable");
     }

     foreach (DataRow content in Contents.Tables["MyTable"].Rows)
     {
         if (content[0].ToString() == "" && content[0].ToString() == "" && content[0].ToString() == "" && content[0].ToString() == "")
         {
             Console.WriteLine("Empty Row");
         }
         else
         {
             Console.WriteLine(content[0] + " | " + content[1] + " | " + content[2] + " | " + content[3]);
         }
     }

My Connection String is:

    string cnnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\"C:\\Untitled 1.xls\";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\"";
1
votes

check below code :It will return the empty rows..

System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0; Data Source = " + strFileName + "; Extended Properties = \"Excel 8.0;HDR=NO;IMEX=1\";"); /*for office 2007 connection*/
                    conn.Open();
string strQuery = "SELECT * FROM [" + Table + "]";
System.Data.OleDb.OleDbDataAdapter adapter = new  System.Data.OleDb.OleDbDataAdapter(strQuery, conn);
System.Data.DataTable ExcelToDataTable = new System.Data.DataTable();
adapter.Fill(ExcelToDataTable);

DT = ExcelToDataTable.Copy();

int count = DT.Rows.Cast<DataRow>().Where(row => row.ItemArray.All(field => field is System.DBNull || string.Compare((field as string).Trim(), string.Empty) == 0)).ToList().Count();
0
votes

As stated by @Knvn

You need to specifiy the file extension .xls with the file name in your connection string.