Populating Datatable rows using Dictionary Key- value

Help Studio
, ,
1

Hi everyone,

I have a Dictionary(string,string) and datatable where in one column i have in random order same values as Dictionary key-values are. How can i populate column 2 based on key-values in col1? To illustrate my problem, here’s a output i’d like to have.
I tried to convert dictionary to datatable and merge/join, no luck there.

image

3

HI,

How about the following?

image
image862×678 24.1 KB 

dt = dt.AsEnumerable.Select(Function(r) dt.Clone.LoadDataRow({r("Column1").ToString,dict(r("Column1").ToString)},False)).CopyToDataTable()

Sample20230126-6L.zip (4.7 KB)

Regards,

4

Hi @Mikko_S ,

Considering the image provided to be the Output, Check the below Expression :

OutputDT = (From x In parametersDict
Let key = x.Key
Let value = x.Value
Let dictValue = key+", "+value
Select OutputDT.LoadDataRow({dictValue,key,value},False)).CopyToDataTable

Here, parametersDict is the Dictionary variable and OutputDT is a Datatable type variable.

OutputDT is to be prepared with the columns like shown below before the use of the Expression.
image

Visuals from Debugging :
image

5

Thank you for this example. I am sure something of this nature solves my problem. What i forgot to mention is that i have two datatables that i try to merge into one. First one has dict keys/values and another has rest of the data including columns with the same name as in dict dt.

6

May we ask you to share some sample data with us? This would speed up the solution approach finding. Thanks

7

TestInput.xlsx (9.1 KB)
TestInput2.xlsx (9.8 KB)

Sure, Here we have two sample excel- files that we read into datatable. Testinput.xlsx is converted into dictionary, Testinput 2.xlsx B-column should be targeted with dictionary values according to Company name column values. I cannot do this in excel directly. We have to build one datatable variable from these two and after that output to Excel.

8

Hi,

Which column value is related b/w TesstInput.xlsx and TestInput2.xlsx?
Can you share expected output, too?

Regards,

9

Hi,

Sorry for late reply. Problem got sorted like this.
Inner left Join dt1 & dt2 > for each datarow assign correct column > remove columns added from join step.

-Mikko-

10

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.