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

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

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

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

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

Thanks
Ashwin.S

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.

and what is column name?

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

Cheers @tomato25

1 Like

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.

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

1 Like

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