To check if CSV column has any empty or invalid miscellaneous values

Help Studio
, ,
#1

Hello all,

Please help me with the best solution.

Problem: I have a csv file which is having a column named “Emp ID”. Please find the screenshot below. In the “Emp ID” column, there are values(having only digits[0-9]) i.e (99992,99994,…etc). But sometimes we get empty cells and miscellaneous data i.e (09:00-18:00R01 etc) which are having special char as well which is invalid for us. We need Emp id to have only digits[0-9] and there should not be any empty cell in that column.

Requirement : If bot finds any such empty cells and miscellaneous data in the CSV or Datatable column then it needs to send a mail to business and not proceed further with file.

image

#2

@Diva_P

For looking Blanks I can suggest to check below for your reference

Reference

Hope this may help you

Thanks

1 Like
#3

Thank you @Srini84 for the solution… :+1: :+1:

Hi @ppr @Palaniyappan, Can you please help me with the miscellaneous value part in datatable column.

Thanks.

#4

@Diva_P

Check below for the entire empty or Invalid Miscellaneous Values

Let’s suppose your actual datatable is of dt variable

dtNumeric = dt.AsEnumerable().Where(Function(row) IsNumeric(row(column).ToString.Trim)).CopyToDataTable()

This will filter the column which is having the Numeric rows only

Now use If condition and write as below

Cint(dt.Rows.Count) = Cint(dtNumeric.Rows.Count)

So if it matches then you can consider that all the rows are with the correct numbers

Else

There might be some empty or Invalid miscellaneous values

Hope this may help you

Thanks

1 Like
#5

@Diva_P
Lets assume a valid row has following definition

  • EmpID is a digit
  • Joining Date is a valid date

So we can start by following:

Assign Acitvity
LHS: isValid | DataType: Boolean
RHS:

(From d in dtData.AsEnumerable
Let chk1 = Information.IsNumeric(d("Emp ID").toString.Trim)
Let chk2 = DateTime.TryParse(d("Joining date").toString.Trim, nothing)
Where Not {chk1,chk2}.All(Function (x) x)).Count = 0

In case of the dates are different present within the datatable we can adopt the date check as well

#6

Hi @ppr ,

Can you please update the query for checking only in “EMP ID” column Not the “joining date” for empty as well as miscellaneous data.

Thanks in advance.

#7

didnt get you. If Emp ID value is not a number then it is invalid, right. So the check is already done

#8

Actually you have added both chk1 and chk2 in the query. But in my case chk2 is not needed. So can you please modify the query once again with only Chk1.

#9

Are you sure?
grafik
Yellow one is interpretated as an invalid row.

Otherwise go e.g. for:

(From d in dtData.AsEnumerable
Let chk1 = Information.IsNumeric(d("Emp ID").toString.Trim)
Where Not chk1).Count = 0
1 Like
#10

Thank you @ppr for your quick response. It worked for me.

Actually as i mentioned in my first post, the requirement was only for “Emp ID” column. Thanks for your time. :+1 :+1:

#11

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