DataTable Manipulation and Merge Data

[Shyam_Pragash]

Hi
i Tried to merge the two data table Columns and valuse in Linq…

i have different types of code like merge dadatable value and columns Name and it incluse multiple columns of row.
dt1 (Input)-- n Numbers of columns
dt2 (details)-- 5 tor 8 columns

fill the data is it empty of null values

code: checking cell value is empty fill value (dt2 is reference of dt1)

(From d1 In dtInput.AsEnumerable
Where d1.ItemArray.Any(Function (x) isNothing(x) OrElse String.IsNullOrEmpty(x.toString.Trim))
Let d2m = dtAllDetails.AsEnumerable.Where(Function (d2) d1(0).toString.Trim.Equals(d2(0).toString.Trim)).FirstOrDefault
Where Not isNothing(d2m)
Let ra = d2m.ItemArray
Select r = dtResult.Rows.Add(ra)).CopyToDataTable

do it on columns name matches with two datatable only. after the values other columns remain constant.

Thanks
Shyam

[ashwin.ashok ((TheCodingTheory))]

Hi @Shyam_Pragash ,

Lets not use LINQ, could you try achieving it using the Merge DataTable Activity?
We can look at the manipulation later on.

Kind Regards,
Ashwin A.K

[Shyam_Pragash]

Hi @ashwin.ashok

All_Details.xlsx (26.7 KB)
Expected_Output.xlsx (14.7 KB)
Input.xlsx (13.8 KB)

Refer the attachment…

check all details in input file if any cell is empty compare with master data in All_Details file fill data if cell is empty on basis of columns name… .

i tried above code… it only give same column name in both datatable as output…

Remaining columns details are omitted…

i need all the details in input file with compared data…

How to do it…

Thanks
Shyam

[supermanPunch (Arpan)]

Hi @Shyam_Pragash ,

I believe I was able to get the Desired Expected Output for the Input files Provided.

Check the Below Workflow :
DT_Compare_Merge.zip (51.4 KB)

We were able to take the Advantage of the Column Names of Both the Input excel files. Since the Column Names in All_Details.xlsx file were the same names already present in the Input.xlsx, we were able to manipulate the Order of the Columns in one Particular format, then Populate the Output Datatable.

1. Firstly, we get Both the Datatables From Read Range Activity, we have the Datatables AllDetailsDT and InputDT. Next, we Retrieve the Column Names from Both the Datatables and Store it in Array of String variables like Shown in the Image Below :
   

2. Next, we find the Common Column Names in Both the Datatables, then the Un-Common Column Names in both the Datatables as shown in the image below. We then order/Arrange the Column Names in Input DT, By Providing the Common Column Names first, then the Un-Common Names. We also Initialise the OutputDT by Providing the Clone of the Newly Arranged InputDT.
   

   

3. Next, We Prepare the OutputDT using the Below Linq Query and Write the Output to Excel :

(From d1 In InputDT.AsEnumerable
Let ColumnsToMatch = CommonColumnNames.Where(Function(x)Not(String.IsNullOrWhiteSpace(d1(x).ToString))).ToArray
Let matchedRow =  AllDetailsDT.AsEnumerable.Where(Function(x)ColumnsToMatch.All(Function(cName)x(cName).ToString.Trim.Equals(d1(cName).ToString.Trim))).FirstOrDefault
Let ra = matchedRow.ItemArray.ToArray.Concat(OtherInputColumnNames.Select(Function(x)d1(x).ToString)).ToArray
Select OutputDT.Rows.Add(ra)).CopyToDataTable

Let us know if there are still issues being faced in this.