I have file Detail as below.
Datail Send data.xlsx (13.4 KB)
I want read column D for day round soon.
Example now 6/1/2022 —> next round = 12/1/2022
and read data in column E for filter data (filter date in column issue Date by condition date<25 Dec [red text] in file input)
File Input =
input.xlsx (9.8 KB)
Condition filter in file input :
- Column Status = Complete
- Column Count >= 15
- Column Issue Date <= 25/12/2021 (In B.E. 25641225)
after filter data in file input —> copy data to column in same sheet name.
File output = output.xlsx (19.5 KB)
*remark : Real data have about 30,000 rows
Please guide me about it.
Hi @fairymemay ,
Can we know what is the Column "Issue Date" in input.xlsx file.
As we have to Compare the Date i.e according to your input example 25 Dec with 25641225
What is '25641225' ? Date ? Is it some other format ? Binary ?
@supermanPunch
25641225 mean 2564 = year (in Buddhist era) , 12 = month , 25 = date
Buddhist era = Christian era + 543 (2564 = 2021 + 543)
1 Like
@supermanPunch
If I want fix column name because In future if change column , I don’t want change code So I will create file config for set number column as below.
Config.xlsx (9.6 KB)
Please guide me for apply it.
Hi @fairymemay ,
I was able to Develop a workflow to generate the Output Required. However, there are a Few things to Consider/Give Importance to.
- The
input.xlsx file contains a Duplicate Column Name, we won’t be able to read it Diectly. Hence, I have used a Snippet that Changes the name of the Duplicate Column.
The Name of the Second Status Column will be Changed to Status (1) by the Snippet.
-
The Config file is modified and used Separately, Not along with other Configurations. This Config file should only be used to Read the Column Names of Input Excel and Output Excel.
(Not sure whether this is the Expected Format that was desired)
-
We perform the Filter operation on the InputDT based on the Conditions Provided with a Linq expression as below :
InputDT.AsEnumerable.Where(Function(x)x("Status (1)").ToString.ToLower.Equals("complete") and Cint(x("Count")) >= 15 and DateTime.ParseExact(x("Issue Date").ToString.Trim,"yyyyMMdd",System.Globalization.CultureInfo.InvariantCulture).AddYears(-543)<= ApproveDate).ToArray
The AddYears(-543) is done to get the Current Era year.
-
The Output Excel Template is maintained in the Template folder. So that each time, the Output Template is copied to maintain the Output format.
-
To Generate the Output Data, we Iterate over the Filtered Rows that Satisfies the Condition Specified and add it to the Output Datatable which is Created using the Column names in Config.
Below is the workflow. Please do check and let us know if is not the Expected Solution.
Filter Excel.zip (66.2 KB)
1 Like
@supermanPunch File output not correct.
Now output Show as below.
But that true should show 2 rows (Mary, Jack) Because Issue Date > 25641225
And condition , If I don’t fix month=12 But I want check month now.
Remark : Real Data input have 30000 transaction , If I run it work slowly or not?
Thank you for your suggest.
Hi @fairymemay ,
According to your Initial Comparison logic, It should have been Issue Date <= Approve Date . Hence, the Output Result did not match.
We can Update the logic inside the Linq Expression as follows :
InputDT.AsEnumerable.Where(Function(x)x("Status (1)").ToString.ToLower.Equals("complete") and Cint(x("Count")) >= 15 and DateTime.ParseExact(x("Issue Date").ToString.Trim,"yyyyMMdd",System.Globalization.CultureInfo.InvariantCulture).AddYears(-543)>= ApproveDate).ToArray
It is Just a Change from <= to >= as highlighted below.
Yes, I couldn’t understand the Logic Straight away to decide which year it is to be taken in that Column. Hence, I made my own logic. We can Change it to Check by Current Month and Update the Year Accordingly.
Now, as far as I have understood, The Changes for that if Condition is as below :
Let me know if you’re unable to make these changes aand get the right output or if there is any incorrect logic.
1 Like
@supermanPunch
- If I want read Lot No. that match , How to read value ?
Please guide me about it.
- If I config file I want fix column name but I want fix number column in excel can it be possible?
Because I want use config same pattern (In organize).
- Sometimes file input have more 1 sheets , I want read every sheet (except sheet name Extra , sheet that hide) that match condition from above —> I want create sheet name follow input file.
Example :
input.xlsx (14.5 KB)
Output.xlsx (28.5 KB)
Please guide me about it.
Thank you for your suggestion.
Hi @fairymemay ,
Can we know what is the Lot No. value to Compare with , and How to Compare it if the format is a different way?
Can you Explain in detail what do you mean by Fix Column Name in Number Column?
Do you want the Position of the Columns to be Kept as Specified in the Config File ? Eg : Campaign Period Column as 1st Column ?
@supermanPunch
-
Lot No. = row that match date follow logic previous (same row Approve Date)
I want keep Lot No in variable(string).
-
Yes, Campaign Period Column as 1st Column , Lot send Column as 2rd Column , …
Thank you for your support me.
@supermanPunch Additional : I want keep lot No. that match in variable1 and Date Approve that match from Data ( Ex: 25641225 in variable2)
Please guide me more about it.
Hi @fairymemay ,
In the Config Excel file, I have added another Column which will be determining the Position of the Column in Excel.
Before Writing the Output Datatable, I am arranging the Columns using the Posiiton as shown below :
Also, I have Stored the Approve Date and Lot No. in variables.
Please do Check on the method and the Config file formats, and let us know if this is the Expected Output that you required. Below is the Updated Workflow.
Filter Excel.zip (66.8 KB)
@supermanPunch You miss 1 required and I addtional 2 as below.
- Sometimes file input have more 1 sheets , I want read every sheet (except sheet name Extra , sheet that hide) that match condition from above —> I want create sheet name follow input sheet name.
From File excel as above : I have 5 sheet (invisible 2 sheets , visible 3 sheets )
But I want read visible sheet only (except Extra Sheet)
- In file output I want map data with another file.
From File report as above : I want map column Out_ColNo from file output with column Report_ColIDSeller and I update file config as below.
and last request I want to combine all sheet in output file to new sheet name Summary Report by copy template from template file (Sheet Summary) , In column Lot —> I want read value in file config sheet LotNumber and write in column Lot and extend(sequence) in file output as below.
File as attached
Template.xlsx (44.3 KB)
Config.xlsx (10.1 KB)
input.xlsx (15.2 KB)
Report.xlsx (17.8 KB)
Datail Send data.xlsx (13.3 KB)
I want output as below.
Output.xlsx (49.9 KB)
Please guide me about it.
Thank you for your support.
@supermanPunch I will waiting for your suggestion above requirements.
Hi @fairymemay ,
Apologies for the very late response, as I was busy in completing some tasks of mine.
I have updated the Workflow to meet your Expected Output.
I had to use an External Package to read only the Visible Sheets in Input Excel.
Also, the Logic remains the same, it’s just we need to loop through all the Visible Sheets to generate the correct output.
Also, The Lot Number is included in the Same Config Sheet. You can Keep it a Different Sheet if required and read the value from there.
Check below the Workflow and Let us know if it is not the Expected solution.
Filter Excel.zip (84.0 KB)
1 Like
@supermanPunch I have question , If I create folder for file input but file input > 1 ( loop read file input in folder input).
How to read loop file in folder input & write data in same file output ?
Example : In folder input have 2 file.
-
input1 have 3 sheet (visible) Extra , Q1 , ConSE.
-
input2 have 4 sheet (visible) Extra , E1 , E2 , ConSE , ConQR.
-
read all file that visible except sheet Extra , Start sheet name with Con
I want to output have 5 sheet ( Summary , Output , Q1, E1 , E2 )
Please guide me more about it.
@supermanPunch I understand you busy. But if you have freetime please guide me more about it.
Hi @fairymemay ,
Is it possible to provide the input data and Expected output files ?
There are a lot of changes happening as we progress, may be give us a final format output that you require, so we can quickly assist you on Completing the task.
If possible we should be able to develop a Workflow and provide it to you.
@supermanPunch
From old requirement I want to add new requirement as below.
-
Loop file input in folder for read file.
-
filter file input
Condition filter in file input :
- Column Status = Complete —> old requirement
- Column Count >= 15 —> old requirement
- Column Issue Date <= 25/12/2021 (In B.E. 25641225) —> old requirement
New condition filter :
- Column Status = null
- If Column Type = Cash
write data in file Output T&C in sheet Summary and sheet name follow file input
(but if input file column Type Normal = Yes → Write with sheet name input in file Output T&C
else Type Special = Yes → Write with sheet name and add text Spe input in file Output T&C
write data in file Output Voucher and write data in sheet name follow file input
(but if input file column Type Normal = Yes → Write with sheet name input in file Output Voucher
else Type Special = Yes → Write with sheet name and add text Spe input in file Output Voucher
File input , output as attached below.
Data.zip (138.6 KB)
Thank you for help me for solve it.
@supermanPunch If you freetime please guide me more for solve it.
Thank you
ashokkarale
MD_Farhan1
Ajay_Mishra
postwick
Dheerendra_vishwakarma
Anil_G
chandreshsinh.jadeja
Gautham_Pattabiraman
vrdabberu
aravindbalineni123