I need to filter out the blank from datatable which column is datetime type

datatable
studio

#1

My Excel file data show below

image

Then I do read range to keep data into DT1 variable datatable type (with header selected)
And I do read cell to get column name of column 2 which is datetime type, it is 12-May-18. Keep into Header variable string type

I have created the datarow type variable called DTrow1

After that I use Assign activity to do select datatable, propose to remove row 4 and 5 because of it contain blank data.

I assigned DTrow1 in the left side and DT1.Select(Header+" <>‘NULL’") in the right side.

But it shown error that “Cannot add System.DateTime to System.ToString”
How can I solve this, need your professional guy help sir.

Thank you very much.


#2

@ChatchaiSh
Try
DT1.Select(String.Format(“ISNULL(Convert([{0}], ‘System.String’), ‘’) <> ‘’”, Your_Date_Column)


#3

@Madhavi Thank you, I tried but It doesn’t worked. The “Header” is my variable that keep column name which is datetime type. So I replace it at “Your_Date_Column”. I’m not sure that I understand to use the suggest code or not.


#4

Hi @ChatchaiSh,

Pls try the below.

Step1: Covert ur datatable to dataview
dataView1 = new DataView(urdata table);

Step2: use RowFilter property on dataView1(dateCol <> ‘’)
dataView1.RowFilter = “dateCol<>’’”

Step3: Assign the filtered dataview to the datatable2
datatable2=dataView1.ToTable

You will have the datatable2 which will not contain null values in the datecolumn(2nd column)

Hope it helps you.


#5

@ChatchaiSh
It is just missing one ‘)’
DT1.Select(String.Format(“ISNULL(Convert([{0}], ‘System.String’), ‘’) <> ‘’”, Header))


#6

@dschiranjeevi Found the error sir. For more information, In actual work sheet we have more column of date time type. That why I try to specific the date to define which date time column I want to filter.

image

Very grad for your helping.


#7

@Madhavi Found the error below sir.

image


#8

@ChatchaiSh
The error shows that your ‘Header’ variable doesn’t have any value. Please print the Header value before initialization and check the value.


#9

Here is the value of Header that was show by message box

image


#10

Can you print only this and check


#11

Here’s is the string show by message box. May I share what I do in the zip coding.

image

Selec Filter blank out.zip (4.2 KB)


#12

@ChatchaiSh
You might have copied the query i have written from browser and pasted there. Hence " and ’ were not recognised properly. Please find below updated xaml.
Main.xaml (10.6 KB)


#13

@Madhavi It’s worked. I try to compare the previous one with the solved one. But can’t find that how they difference hahaha.