I need to filter the excel file to which i m providing screenshot of it.I have to filter the excel based on date,
meaning to take those rows only which are between 15/7/2019 and last day of month.and then
"Accept"column which contains “Yes”.
In “Reasons” column which should contain only cars, NO VR,and Delivery.
4.Before pasting the data in another excel sheet i hv to add 999 in each value of the “contract No” column and also name of “Station” column for each row. So the output should be in “contract no” for 1st row …241201154999-Sacramento.
–for these three condition in a single command
–hope you have the datatable extracted from excel with read range activity and name the datatable variable as out_dt
–now the assign activity be like out_dt = out_dt.Select("[Date] > ‘07/15/2019’ And '[Date] < # " + now.ToString(“MM/dd/yyyy hh:mm:ss”) + " # " and [Accept] = ‘Yes’ and [Reasons] = ‘cars’ or [Reasons] = ‘NO VR’ or [Reasons] = ‘Delivery’ ").CopyToDatatable()
Then for this
–use a for each row loop and pass the variable outdt as input
and inside the loop use assign activity like this row(“ContractNo”)=row(“ContractNo”).ToString+“999-”+row(“Station”).tostring
Thanks for the workflow…its working f9 now…but can you please tell me how i can filter the date from 15/07/2019 to 31/07/2019 using select method as u hv briefed in d above case… without hardcoding the value of month and year …i can only hardcode starting date…as 15 in ds case…and last day of the month should also not be hardcoded as 30 or 31.
Sorry for the delayed response
Let the 15 be. Same with condition mention
For last day of the month we can mention like DateTime.DaysInMonth(now.ToString(“yyyy”),now.Tostring(“MM”)).ToString(“MM/dd/yyyy”)
This will give the last day of the month
And we can place this in the current one
Excel filter.xaml (11.1 KB) Sheet1 (1).xlsx (135.5 KB) filtered sheet.xlsx (9.2 KB)
A really big thanks for your continuous support, i am not getting how to dynamically put the date as i have already mentioned that i can give the starting date but not the ending date and the last day of the month should also not be hard coded.
i have put my xaml ffile, can yoy please go through it and edit the needful to get the desire output?