How to add data in datatable columns

[vinjam_likitha]

Hi All,

I have a excel file. read the excel file to datatable. then filter the datatable based on Lockbox Number(Batch) finally for all line items in the Batch one transcation number will be Generated i need to add the Transaction Number in the Transaction column and Posting Status column as Completed and Update the same in Input Excel how to do this using lInq Queries or For each row

Attaching the sample input file
input file.xlsx (9.1 KB)

[Sanjay_Bhat]

Hi @vinjam_likitha

1. Read the input excel using read range to a datatable say dt_data

2. Filter the data to a datatable say dt_result
   dt_result = dt_data.AsEnumerable().Where(Function(row) row("LOCKBOX NBR").ToString.Equals("200")).Select(Function(x) dt_data.Clone.Rows.Add(x.ItemArray.Take(5).Concat({"Completed",transactionNumber}).ToArray) ).CopyToDataTable
   Give the necessary condition in the where clause .

3. Write the dt_result data to a new output sheet using write range

Thank you !
Hope this helps!

[Anil_G (Anil Gorthi)]

@vinjam_likitha

please check this…it is a similar use case…before filtering perform the steps to know row numbers and those can be used to update excel back

How to assign index of original datatable to filtered datatable

@stefi as two datatables are different it might not identify…Also as all the rows have same data also it is difficult with only values as well in this case better to first add a column index and use another loop before filtering and in loop just assign index or row number to each column currentrow("Index") = currentindex.now index will have row numberof each row whcih you can use in further loop dt.Rows(Cint(currentrow("Index")))("edited") = "ignoredrow" cheers

cheers