Validating Excel

Help
, ,
1

All,

I need to have two validations with respect to Excels.

  1. I wanted to validate the Input Excel header alone (if it is matching the template of other excel which has no values )
  2. I want to validate
    a. Each cell has the value (mandatory) present in the respective column.
    b. The format of each and every input in that excel (like integer/proper date format with respect to every column).

Any simple way to do it? Also is there a way to make it a kind of re-usable utility for more projects?

2 Likes
2

Hi @Pradeep.Robot

If you want to validate each excel cell you need to iterate in the the datatable and make your desired condition about he format theres a lot of way how to validate it,
Example:The Regex format validation or simply parsing it and check for the format of it.

cheers :smiley:

Happy learning :smiley:

2 Likes
3

Hi
Hope these steps would help you resolve this
For this

—use a excel application scope and pass the file path of first excel file as input
—inside the scope use READ RANGE activity and get the output with a variable of type datatable named dt1
—now use another excel application scope and pass the file path of the template you have
—inside the scope use a read range and get the output with a variable of type datatable named dt2
—now use a While loop and mention the condition like this
counter < dt.Columns.Count
Where counter is a variable of type int32 with default value 0 defined in the variable panel
—inside the loop use a IF condition like this
dt1.Columns(counter).ColumnName.ToString.Equals(dt2.Columns(counter).ColumnName.ToString)

If this condition is true it will go to THEN apart where we can mention as ”column “ +(counter+1).ToString+” matches”
Or it will go to ELSE part where we can mention as
”column “ +(counter+1).ToString+” doesn’t match” in writelineactivity

And for this

Next to the above WHILE loop use FOR EACH ROW activity where pass the respective datatable variable either dt1 or dt2 that you want to validate as input
—inside the loop use a IF condition like this
NOT String.IsNullOrEmpty(row(“yourcolumnname”).ToString

—if this condition is true it will go to THEN part where we can use a write line activity and mention like this
“Row “ +(Yourdatatable.Rows.IndexOf(row)+1).ToString+” has value in it”
or it will go to ELSE part where we can use a write line activity and mention like this
“Row “ +(Yourdatatable.Rows.IndexOf(row)+1).ToString+” has no value in it”

And finally for this

I hope we don’t have any specific activity to do that as the datatable when read with READ RANGE will give us all columns in string datatype columns

Kindly try this and let know for any queries or clarification
Cheers @Pradeep.Robot

2 Likes
4

Thanks much for your time Palaniyappan. I will try this out and let you know.

1 Like
5

Sure
Cheers @Pradeep.Robot

6

Hi Palaniyappan,

Thanks for the solution. It works

1 Like
7

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