Hi Expert,
I have an excel file which contains Remarks columns with values FALSE and TRUE. I wish to furnish the BR code column remain same code against FALSE values (1001) and Location column values in the same file will be placed against TRUE values in the BR code column . Also, i wish to separate the values of FLASE and TRUE with given below one row space in the same file.
How can this achieved by LINQ Query as it is huge number of line items?
Attached the Input file and same expected output details in the other sheet for your reference. Input.xlsx (10.7 KB) Input.xlsx (10.7 KB)
we can use the LINQ Expressions for this, check the below flow,
→ Use the Read range workbook activity to read the excel and store in a datatable called DT_Input.
→ Then use the Assign activity to split the data rows which has True in Remarks column, use below expression in assign activity,
- Assign -> DT_True = (From row In Input_dt.AsEnumerable
Where row("Remarks").Equals("TRUE")
Select Input_dt.Clone.Rows.Add({row("POS IN").ToString, row("Location").ToString, row("INVOICE DATE").ToString, row("Location").ToString, row("Remarks")})
).CopyToDataTable()
→ Then use the Assign activity to split the data rows which has False in Remarks column, use below expression in assign activity,
- Assign -> DT_False = (From row In Input_dt.AsEnumerable
Where row("Remarks").ToString.Equals("FALSE")
Select row
).CopyToDataTable()
→ Then use the Add data row activity and give the null values to add empty row to DT_False to maintain empty row between DT_False and DT_True.
→ Then use the Merge datatable activity to merge the DT_True with DT_False, source is DT_True and Destination is DT_False.
→ Then use the write range workbook activity to write the DT_False to the Output excel sheet.
Check the below workflow for better understanding, Sequence2.xaml (12.8 KB)
