Reading the excel file,filtering it and putting in different excel file

Help
1

Hi Guys,
I need to filter the excel file to which i m providing screenshot of it.I have to filter the excel based on date,

  1. meaning to take those rows only which are between 15/7/2019 and last day of month.and then

  2. "Accept"column which contains “Yes”.

  3. 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.

    Screenshot_20190724-105329
    Screenshot_20190724-1053291280×720 138 KB

2

First use read range then

use filter datatable activity in that write (in first cond :

Now.ToString(“MM/”)+DateTime.DaysInMonth(cINT(Date.Now.ToString(“yyyy”)),CINT(Date.Now.ToString(“MM”))).ToString()+Now.ToString(“/yyyy”))

image

after that use for each row
in that use Row(“ContractNo”)=Row(“ContractNo”).tostring+“999-”+row(“Station”).tostring

After that use write range

3

Hi
Fine
–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

Hope this would help you
Cheers @Ani008

1 Like
4

Okk…Impratham one thing that i forgot to mention that without using the filter data table i have to do this.

So can you please suggest me some ways…??

Thanks in advance.

5

Hi palaniyappan,
I m getting error in Assign activity so can you please make sure dt wtvr u hv written in assign activity is minutely correct??

Thanks in advance.

1 Like
6

Then by using select method as @Palaniyappan already mentioned!

1 Like
7

May I know what was the error getting displayed
Cheers @Ani008

8

There is some syntax error in assign activity as whenever i m trying to enter the whole expression …it is showing error.

20190724_163822
20190724_1638222918×2189 3.06 MB

9

Hi
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.

1 Like
10

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

Hope this would help you
Cheers @Ani008

11

Excel filter.xaml (11.1 KB) Sheet1 (1).xlsx (135.5 KB)
filtered sheet.xlsx (9.2 KB)
Hi ,
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?

Thanks in Advance.