Read Excel Until 1st Row Empty


I have a process which need to read from excel and store the data into database.

My issue here, customer is putting unrelated data at 1 of the column at the last rows:1,048,576 but the required information is only until rows 20. It takes a very long time to read all the rows (Read Range Activity). Is there anyway that we can read the Excel until the first row empty?

Current temporary solution is I limit to 200, but it was not a good solution because it will be different in the future.

*Update: Long time at Read Range Activity

Hi @muhammad.khairulanam

Please try this,

dtTable - datatable

dtTable.AsEnumerable.TakeWhile(Function(row) row.ItemArray.Any(function(col) not string.IsNullOrEmpty(col.ToString))).CopyToDataTable

Original Post similar to this one,


Hi @prasath_S,

I don’t think this will work because the issue is at the Read Range activity which it takes long time. This solution is after we retrieve data from the Excel and put into datatable.

Hi @muhammad.khairulanam ,

You can try below:

  1. Drag do while loop.
  2. Put read range inside the loop and read first N(consider 50) rows and store in DT1 variable.
  3. Filter DT1 to remove empty rows and store resulting value in DT2. Create an additional datatable variable DT which is accessible outside do while and store DT2 data to it.
  4. Have this condition in do while loop: DT2.rows.count = N.

Once bot comes out from the loop, you can have required data in DT datatable.

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