Hello! I am reading an Excel invoice and I am trying to extract the line item information using Read Range (Range: C24:N43 - 20 rows). When I do LineItem.Rows.Count, it always returns 20, even if the rows are empty. How would I get the correct row count in this situation?
Note: If any column in the rows contain a value, it should be considered a non-empty row
There are various ways to do this, but you could try using a workflow like this:
Processusvierge.zip (14,6 Ko)
If you are using a fixed range it will get the range as it is… if you need to get a table of data, dynamically, consider adding a named range in your excel (can use simple tables) and in read range, pass the name of that range instead, it will only return real data.
It is a fixed range. The format will always be consistent. I tried it @Anthony_Humphries way and it is working at the moment but I am open to other solutions
There are certainly dataview solutions to this as well, but I decided to use the version using activities since this would be easier to interpret at a glance, in my opinion.
Both of the below expressions will return the count of rows where at least one column is populated. Give it a try
dt.AsEnumerable.Where(function(x) Not String.IsNullOrEmpty(String.Join("",x.ItemArray).Trim)).Count
dt.AsEnumerable.Where(function(x) Not x.ItemArray.All(function(y) String.IsNullOrEmpty(y.ToString.Trim))).Count