Distribute data from one Excel file to another

Help Studio
,
1

Hello everyone!
Please tell me how to distribute the data on the sheets. So, there is an excel file with the extracted data (Consolidated.xlsx) and an excel file into which you need to copy the data (Sample1.xlsx).
The algorithm is something like this:

  1. Read the “№” column in the excel file (Consolidated.xlsx).
    №
  2. Compare the value in the “№” column with the name of the excel sheet of the Sample1.xlsx file. The sheet name of the excel file contains several sheet numbers, if one of them matches, then copy the data into it.
    sample1
    sample1573×800 18.9 KB

    When copying, you must first copy the previous rows (by the newest attribute in the “Date” column) and paste the value from the “Sum” column of the excel file Consolidated.xlsx.
    №
    fin
    fin548×770 13.1 KB

    I would be grateful for any hint, thanks!
    Consolidated.xlsx (8.6 KB) Sample1.xlsx (9.8 KB)
2

Hi @sereganator
I believe you have to use Left Join in this case. Here’s the expression that I prefer.

JoinDt = (from cons in Consolidated.AsEnumerable() join sam in Sample.AsEnumerable() where cons.Field(“No”) == sam.Field(“No”)
into joinedDT.AsEnumerable() from jd in joineddt.AsEnumerable().DefaultIfEmpty()
select new {
Date= cons[“Date”].ToString(),
Sum= jd[“No”].ToString() == null ? “” : sam[“No”].ToString()
}).copyToDatatable