How to check if any columns are empty in each datatble at the same time

Help Studio
,
1

I have a .xlsx file and it have many sheets , i want to find is there any columns which are NullOrEmty in each sheets and if found one i want to perform some logging and throw

Currently I’m trying to do it with a flowchart inside for each datatble (each sheets) i have did it for two sheets but theere are 7 of them and some of them have more than 10 columns in it and possibly can be added more in the future

Is there any solution? I want to perform logging and a throw for each column which is empty in each sheet

2

Hi @indiedev91
Follow the below steps to achieve your case.

  1. Use the Read Range activity to read the Excel file into a data table.
  2. Use a For Each loop to iterate through each sheet in the file. Inside the loop:
  3. Use the Read Range activity to read the current sheet into a data table.
  4. Use a For Each loop to iterate through each column in the data table. Inside the loop:
    i). Use an If activity to check if all rows in the column are null or empty.
    ii). If the column is empty, use a Log Message activity to log a message and use a Throw activity to throw an exception.

Regards,
Kaviyarasu N

3

Can you give a demo process?.

4

string.isNullorEmpty(currentrow(“ColumnName”).tostring

5

Hi @indiedev91 ,

The Any Function will help you out here.
This is what I was able to come up with:

(From c In Enumerable.Range(0,dt.Columns.Count)
Let colData = String.Join("",Enumerable.Range(0,dt.Rows.Count).Select(Function(r) dt.Rows(r)(c)))
Let containsBlank = IsNothing(colData) OrElse String.IsNullOrEmpty(colData.ToString.Trim)
Select containsBlank).
	Any(Function(a) a)

I bet there is an easier way to solve this, but this is what I came up with off the top of my head.
CheckifColumnsareBlank.xaml (17.5 KB)

Kind Regards,
Ashwin A.K

1 Like
6

Hi @indiedev91

You can use something like

dt_Records.AsEnumerable.Any(Function (x) IsNothing(x("DateColumn")) OrElse String.IsNullOrEmpty(x("DateColumn").toString.Trim)))

inside the function you can further add OrElse statement and add multiple column names accordingly.

Also, approach provided by @ashwin.ashok is also good to follow

Thanks

7

how do i use throw and some other activity , and as im understanding here it is still working on a single datatable , am i right?

8

Hi @indiedev91 ,

Since the output of this code is a boolean value, you can pass this into an If Activity like so:
image

Is there any other specific operation you’d like to perform if the datatable contains empty columns?

Kind Regards
Ashwin A.K

9

Want to get the colmn name and the sheet name to in the throw and the append line

10

Can you explain the code a little

11

Hi @indiedev91 ,

Throwing exceptions is tricky, since the execution stops there.
You can either use a Log Message with the type set to error, or if you want to use a BE, then encase it inside a try catch like so:

image

If you want to get a list of the columns that are empty, then you can use this:

(From c In Enumerable.Range(0,dt.Columns.Count)
Let colData = String.Join("",Enumerable.Range(0,dt.Rows.Count).Select(Function(r) dt.Rows(r)(c)))
Where IsNothing(colData) OrElse String.IsNullOrEmpty(colData.ToString.Trim)
Select dt.Columns(c).ColumnName).ToArray()

CheckifColumnsareBlank.xaml (17.5 KB)
Kind Regards,
Ashwin A.K

1 Like
12 
(From c In Enumerable.Range(0,dt.Columns.Count)
Let colData = String.Join("",Enumerable.Range(0,dt.Rows.Count).Select(Function(r) dt.Rows(r)(c)))
Let containsBlank = IsNothing(colData) OrElse String.IsNullOrEmpty(colData.ToString.Trim)
Select containsBlank).
	Any(Function(a) a)

This consists of nested loops - one which iterates over the columns, while the inner loop iterates over the rows.
The loop is designed to collect data horizontally, and that data is joined together using String.Join.

From c In Enumerable.Range(0,dt.Columns.Count)
Let colData = String.Join("",Enumerable.Range(0,dt.Rows.Count).Select(Function(r) dt.Rows(r)(c)))

This is done to check if there is any data present in the column, which is taken care of in the containsBlank segment.

Let containsBlank = IsNothing(colData) OrElse String.IsNullOrEmpty(colData.ToString.Trim)

Lastly, we check if Any columns in the datatable are blank, and if so then it returns true.

Any(Function(a) a)

If you’d like to learn more on LINQ, I’ve written few posts on it which you can check out from here.

Kind Regards,
Ashwin A.K

13

do i have to use it for each sheet seprately ?

14

Hi @indiedev91 ,

You don’t have to design anything new - I’ve included the logic to loop through each sheet in excel in the xaml I’ve shared here.

Kind Regards,
Ashwin A.K

15

Thanks man its working just as expected , tysm

16

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