To use a single excel sheet as two different data tables

[chintham]

Hello,

I have an excel sheet with multiple columns. And for some rows, there are no values for one column. In that case, Bot is wasting time to look over the empty cells too. Even I tried with using Read Range Activity to just read one column and process, even though it is not working. So can someone help me in doing this task.

My main concern is, When reading that column, it should stop reading when it counters empty field. Thanks in advance

[loginerror]

@chintham
Normally when you put a double quote “” as your range it will only take the non-empty fields.

Please see this thread for more reference:

Trying to read a single excel sheet as two different data tables

My excel sheet has 6 columns and I wish to read only one column as one data table and all the remaining rows as another data table. I don’t have any idea how to do that. Can you please give me a procedure how to do that. Thanks in advance.

[ClaytonM]

Hi,

This is how I have approached processing only rows with non-empty values.
After you Read Range of entire data, you can filter it down to an array of rows which you can loop over. This can be done using LINQ like so:

For each row In dt.AsEnumerable.Where(Function(r) r("column").ToString.Trim <> "" ).ToArray
   <perform actions on row>

//This is using the generic For each with Data.DataRow as the TypeArgument.

The .Where() let’s you pull in only rows that you want to process but let’s you still update the original data table. If you were to use .CopyToDataTable instead of .ToArray, then you would be creating a new table which you probably don’t want to do in your case, since you are wanting to process particular rows but keep the table as it is.

If you are wanting to remove the rows with empty fields completely, then use the .CopyToDataTable

I hope this helps answer it.

Regards.