I have a date column in my excel sheet and i want to read only those rows which date is starting from 15 till 31st of a month.
Please note that the first value in my date column is not a date it has a number of this form written in it and rest of the date format is dd/mm/yyyy
Attaching the screenshot for better understanding. The frst value is not a date
Usually you can use the Select method to filter the rows in a DataTable. Here you can find details about the Select filter syntax.
In your case, you can try to find an expression using the
Substring method to extract only the day part and
Convert method to convert it to Integer data type.
yourDataTable.Select("CONVERT(SUBSTRING(Date, 0, 2), 'System.Int32') > 14"
As regarding the first row, start reading from the second row. Or read everything and then use Remove Data Row activity to remove those you don’t need.
I ma successfully able to remove the first row but as per your query for selecting the date range i am getting an error please see the screenshot below and one more thing after running your query the output should be assigned to another datatable or any other datatype
i fixed that but after running i am getting the below error
Assign : Type mismatch in function argument: Substring(), argument 1, expected System.String.
That’s because the column type is DateTime in the DataTable. To fix this, you need first to convert the Date column to System.String. Also, for the Substring method, the index should start from 1, not 0 as in my example.
This is how the Select should be:
yourDataTable.Select("SUBSTRING(CONVERT(Date, 'System.String'), 4, 2) > '14'")
DateFilter.zip (7.9 KB)
Thanks a lot i will try this and will let you know.
Correct me if i am wrong i tried to convert previously my date which was in datetime format to string but it chagned the date format from dd/mm/yyyy to mm/dd/yyyy is there a way i can convert my date to string and my format should also doesnt change because i tried a lot of ways to do that but i was unsuccessful till now in that.
Please check the links I’ve send you in the first post, you have the explanation there:
The date format is the same as is the result of DateTime.ToString() method for invariant or English culture.
I don’t know a way to specify the date format in the Convert method.
Alternatively, you could loop through rows using a For Each Row activity and implement the conversion and condition using activities.
hey, i am having somewhat similar task. I hope you can help me.
I am having a column with dates from year 2000 to 2019. I want to get only the date with year 2019 and all the columns along with that.
I tried filter datatable activity but no luck. Not recognising dates.Column was in general format initially and I changed to date format manually in excel , no luck
Then I came across the select activity and tried I am havinf trouble sorting it out.
How to achieve this?
It would be helpful if you could attach a sample Excel file. It is hard to help you with the .Select query without knowing the column names or how do the dates look like
If the Excel contains sensitive data, simply create a mockup version of it
date.xlsx (11.3 KB)
Hope this helps. This is a mockup. Need to get only 2019 data. Also when written to excel some dates are showing as number in GENERAL format. When changed to DATE format it will be converted to date. So is there any way to write into excel as date format directly?