How to check blank colums in an excel?

Help Studio
, ,
1

Id,Name,Salary
1,Rakesh,8000
,Somesh,9000
3, ,5000
4,Mahesh, I wanted the output as -id is missing at row no 2, name is missing for row no 3,salary is missing for row no 4 …how to check this?

2

We assume Excel Data is read in with read range - dtData

Build DataTable Activity: Configure the columns: RowIndex, ColumnName - dtStatistic

Assign Activity
dtStatistic =

(From ri in Enumerable.Range(0, dtData.Rows.Count)
From ci = Enumerable.Range(0, dtData.Columns.Count)
Let v = dtData.Rows(ri)(ci)
Let chk = isNothing(v) OrElse String.IsNullOrEmpty(v.ToString().Trim())
Where  chk
Let ra = new Object(){ri, dtData.Columns(ci).ColumnName}
Select r = dtStatistic.Rows.Add(ra)).CopyToDataTable

Handling empty result we do:
:ambulance: :sos: [FirstAid] Handling of The source contains no DataRows exception - News / Tutorials - UiPath Community Forum

3

Hi @Aparna_30 ,
You can try this step
1.read range to get data table
2.for each row data
3.check if blank
you can see my input/output

image
image810×546 18.2 KB

image
image

my file input(sheet2)
test.xlsx (10.5 KB)

my code
sequence.xaml (15.6 KB)
you can try it
regards,
LNV

4

Hi @Aparna_30

=> Read Range (Read your CSV/Excel into a DataTable)
=> Use For Each Row in DataTable to loop through each row in the DataTable)

Assign rowNumber = DataTable.Rows.IndexOf(row) + 2      (DataType: System.Int32)
Assign missingFields = ""                               (DataType: System.String)

=> Use Else If condition

 String.IsNullOrEmpty(CurrentRow("ID").ToString)
    Assign missingFields = missingFields + "ID is missing at row no " + rowNumber.toString
Else if 
String.IsNullOrEmpty(CurrentRow("Name").ToString)
    Assign missingFields = missingFields + "name is missing at row no " + rowNumber.toString
Else if
 String.IsNullOrEmpty(CurrentRow("Salary").ToString)
    Assign missingFields = missingFields + "Salary is missing at row no " + rowNumber.toString
Else
    Assign missingFields = missingFields + "All the data is present row no " + rowNumber.ToString

=> Use assign activity and give the below syntax:

    Assign outputMessage = "For row no " + rowNumber + ", " + missingFields

=> Print the outputMessage variable in message box.

Refer the image for better understanding
image

image
image900×2202 100 KB

Hope it helps!!

5

Hello @Aparna_30

  1. Read Excel Data:
  • Use “Excel Read Range” to read data into a DataTable.
  1. Loop through Rows:
  • Iterate through each row in the DataTable.
  1. Check and Output Missing Values:
  • For each row, use conditional checks (String.IsNullOrEmpty()) to identify missing values in columns (Id, Name, Salary).
  • Output a message indicating missing values and row number.

Thanks & Cheers!!!