Hi all experts, I would like to seek your advice on how to extract the excel values and group them into 2 different datatables. dt_OpsSystem and dt_InvestSystem. How do i extract them by skipping the row highlighted in blue as well? This excel list is dynamic to keep track employees on and off boarding.
My main goal is to get the list of emails from this excel and compare with the employee list in HR system to do updating once a month.
TIA!
Hii @sophiey
First install the EPPlus library
Use EPPlus to read the Excel file and extract its values
Load the Excel file using EPPlus.
Create two DataTables dt_OpsSystem and dt_InvestSystem
Loop through the rows in the Excel file
Assume that we’re splitting rows based on the value in the first column, but you can adapt this logic to your specific needs.
Cheers…!
Hi,
I think it’s better to use Dictionary
Can you try the following sample? This can handle dynamically even if number of system (datatable) increase.
Sample20231017-3L.zip (9.9 KB)
Regards,
Thanks Yoichi.
If I would like to do it simpler way, could you also advice me how do I extract the rows between the 2 merged blue rows and convert to a dt?
Hi,
How about to modify as the following? (Delete “+1”)
dt.AsEnumerable.Skip(CurrentItem.Value).CopyToDataTable
Regards,
Hi Yoichi, due to my organisation’s policy, im not able to download the zip file. Appreciate if u could share the codes here instead.
thanks!
Hi,
Alright. HIdden expressions are as the following.
dictIdx = dt.AsEnumerable.Where(Function(r) String.IsNullOrEmpty(r("Name").ToString)).ToDictionary(Function(r) r("LOCATION").ToString,Function(r) dt.Rows.IndexOf(r))
dictDt = New Dictionary(Of string,DataTable)
dtTemp = dt.AsEnumerable.Skip(CurrentItem.Value).CopyToDataTable
OR
dtTemp = dt.AsEnumerable.Skip(CurrentItem.Value+1).CopyToDataTable
dt = dt.AsEnumerable.Take(currentItem.Value).CopyToDataTable
type of variable
TypeArgument of ForEach is KeyValuePair<String,Int32>
Hope this helps you.
Regards,
