How to check if a row in a DataTable is made up of all empty cells?

Help
1

I have an Excel sheet with lots of data, and it has 6 different sections or blocks, all separated by a row with no data. For example, there is a table from row 1 through row 10, and this is block 1. Then there is an empty row on row 11, and block 2 starts on row 12, and so on.

Even though there is only one sheet, it has 6 blocks, and I have to process them separately. So I want to use the empty row as a delimeter. How should I do this?

Right now, I am using the following logic, but this is kind of too much work, and I wonder if there is a smarter way to do this.
Note: We know what is the last column of the data (say “M”)

  1. Declare an integer array to store empty row indices: arrEmptyRowIndices = new Int32(){}
  2. Read Range activity - Read all data from this sheet and get dt_Data
  3. Assign: j = 0 (Of Type32)
  4. ** For Each Row ** activity
    For Each row in dt_Data
    {
    …Assign: i = 0
    …While (i <= 11) { /* 0=columnA, 1=columnB, 2=columnC, … 11=columnM*/
    … … If (NOT string.IsNullOrEmpty(row.Item(i).ToString) Or row.Item(i).ToString IS NOT “”) {
    … … … Break /*A cell at this index i is not empty, so skip to the next row. Break out of While /
    … …}
    … … else i = i + 1
    …}
    … … Assign: arrEmptyRow( j ) = ThisRowIndex /     Didn’t break out of While = this is an empty row index */
    … … Assign: j = j +1
    }

This is kind of messy, and I don’t even know this is the right logic, but is there a smarter, or simpler logic to get the empty row numbers? That way, I can use it later like:

Read Range - “A0:M” + arrayEmptyRow(0).ToString for the data in first block
Read Range - “A” + (arrayEmptyRow(0) + 1).ToString + “:” + “M” + arrayEmptyRow(1).ToString for the data in the second block

This seems like too much work…

1 Like
2

Hi @tomato25

Check this

DataTable name->DataTableName
Use assign activity
DataTableName=DataTableName.Rows.Cast(Of DataRow)().Where(Function(row) Not row.ItemArray.All(Function(field) field Is DBNull.Value Or field.Equals(""))).CopyToDataTable()

Thanks
Ashwin.S

2 Likes
3

Thank you. Can you explain what the resultant DataTableName is? I am a little confused… and how exactly to use it?

4

Hi @tomato25

Use assign activity newdt=that query and give the column name

Thanks
Ashwin.S

5

I am sorry but I am lost…

For Each row in dt_Data{
… Assign: newDT = dt_Data.Rows.Cast(Of DataRow)().Where(Function(row) Not row.ItemArray.All(Function(field) field Is DBNull.Value Or field.Equals(“”))).CopyToDataTable()

… /For testing/
… Write Range: newDT
}

I tried this in debug mode to check what the resulting Excel file contains, and every iteration, it is just pasting the whole sheet data (all 6 sections) as if it were copy-pasting the original file over and over each iteration.

6

and what is column name?

7

Kindly have a view on this buddy hope this would help you

Cheers @tomato25

1 Like
8

I guess I might have confused you guys…
My goal is NOT to get rid of all empty rows… Since all 6 blocks have completely different structure in the table, I cannot just remove all empty rows and combine them…

That is why I need to find which row is actually empty, so I can know from where to where is the first section, from where to where is the second section, and so on, so I can process each block separately.

9

temp.zip (11.9 KB)
check this sample work flow design.

2 Likes
10

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.