How to check multiple columns have data in Excel file

Help Studio
1

Hello all,

Here my question is, I have input Excel file which contains 14 columns and I need to check every column contains value or not.

Please help me on this, thanks in advance

2

Rather than that you can check which column is empty right?
You can use if condition and you can check which column is empty

1 Like
3

@sai6

its better to use for each row in datatable and inside use for each for columns

or

can you share a sample input so that i can help

4

Yes but I have data in multiple rows so we can check which cell have empty value then notify business that particular column have empty value through exception

5

Yes,
The column contains name like,
Customer name, customer number, address,city, postal code, like this way I have 14 columns so I need to check every column contains value or not for each row it should be check if any of the cell don’t have value we can track them

6

@sai6

if you want all the columns index ,which are having null values

use this

Dim index As Int32
Dim booloutput As Boolean
For Each row As datarow In dt1.AsEnumerable
booloutput=row.ItemArray.any(Function(a) String.IsNullOrEmpty(a.ToString))
If booloutput=True Then
index=row.ItemArray.ToList.FindIndex(Function(a) String.IsNullOrEmpty(a.ToString))
list_indexvalues=list_indexvalues.Append(CStr(index)).ToList
End If
Next

in invoke code which give you the index of columns

Sequence4.xaml (7.0 KB)

1 Like
7

Hi Sai

you can use for each loop, in the for each loop the TypeArgument should be DataColumn.
In the for each use if condition, the condition should be currentItem.ColumnName.Trim.Equals(“”)
Hope this helps you

1 Like
8

@sai6

you can do it in two ways by using for each row in datatable activity and invoke code activity

refer the below xaml i have implemented in 2 ways

ComparingallColumnsValues.zip (4.7 KB)

cheers

1 Like
9

Thanks for tha reply @Shiva_Nikhil

1 Like
10

Thanks for the reply @sanjay3

11

as you have mentioned multiple rows, we would recommend to further define the report level

  • row wise
  • data table wise

For data table wise:

Build DataTable Activity

  • Confige an empty datatable with 2 cols: RowIdx | String, ColName | String - dtReport

Assign Activity
dtReport =

(From i in Enumerable.Range(0, dtOrig.Rows.Count)
Let d = dtOrig.Rows(i)
From c in Enumerable.Range(0, dtOrig.Columns.Count)
Where isNothing(d(c)) OrElse String.IsNullOrEmpty(d(c).toString.Trim)
Let ra = new Object(){i.ToString,dtOrig.Columns(c).ColumnName }
Select r = dtReport.Rows.Add(ra)).CopyToDataTable

Handling empty result (e.g. no blanks are detected)
:ambulance: :sos: [FirstAid] Handling of The source contains no DataRows exception - News / Tutorials - UiPath Community Forum