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

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.



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


Hope this may help you


1 Like

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

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



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


There might be some empty or Invalid miscellaneous values

Hope this may help you


1 Like

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

(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

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.

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

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.

Are you sure?
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

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:

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