Can i make look up with separator and return value by same sequance

Help Studio
1

ex_separator
ex_separator819×245 16.6 KB

i need to lookup between sheet1 and sheet2 i need to look sku coloum and return slocation
example 500009893 ,500010000 … 1530,1526 i need to return this value
i already use look up but only return value for first row which has one sku but row which has more than one sku return nothing ?? any help please for this i will be so pleased

2

Hi @rpa13

This is the first data table (lets assume your first excel file)

image
image925×343 11 KB

This is the second data table (lets assume your second excel file)

image
image924×339 11.3 KB

this is the code used inside invoke code activity select c# in invoke code property,

foreach (System.Data.DataRow dtRow in dt1.Rows)
{
	string temp = "";
	foreach (string item in dtRow.ItemArray[0].ToString().Split(','))
	{	    
	     temp = temp + dt2.Select("sku = '" + item + "' ")[0]["sLocation"].ToString() + ",";
	}
	dtRow["sLocation"] = temp.Substring(0,temp.Length-1);
}

image

This is the arguments passed in invoke code activity,

image

and finally this is the output.

image

let me know if you need any help

Thanks

3

i got this error
Invoke code: Exception has been thrown by the target of an invocation.

4

@Adrian_Star any help please for my problem ?

5

Hi,
you have a code execution error because your cells are separated by a comma (,) and a space.

If you remove the space, the problem is gone.

image

The code must be adapted to your data.

Code for you:

foreach (System.Data.DataRow dtRow in DT1.Rows)
{
string temp = "";
foreach (string item in dtRow.ItemArray[0].ToString().Split(','))
{	    
     temp = temp + DT2.Select("sku = '" + item.Trim() + "' ")[0]["sLocation"].ToString() + ",";
}
dtRow["sLocation"] = temp.Substring(0,temp.Length-1);
}

image
image

6

i have the same error even i remove the space

7

scn1
scn11046×385 23.3 KB

the red circle the target that i need i try to use look up activity but it return only the value of colom sku which has only one sku
@Adrian_Star @prasath_S any help please?

8

You have errors, because the code divides sku by (,) and you put pipe (|).

9

Hi @rpa13

Please try this and let me know any error occurs

foreach (System.Data.DataRow dtRow in dt1.Rows)
{
	string temp = "";
	foreach (string item in dtRow["sku"].ToString().Split(','))
	{	    
			temp = temp + dt2.Select("sku = '" + item.Trim() + "' ")[0]["sLocation"].ToString() + ",";
		
	}
	dtRow["sLocation"] = temp.Substring(0,temp.Length-1);
}

@Adrian_Star thanks for the suggestion, i think the other problem is I gave column index instead of column name.

Thanks

10

foreach (System.Data.DataRow dtRow in dt1.Rows)
{
string temp = “”;
foreach (string item in dtRow.ItemArray[0].ToString().Split(‘|’))
{
temp = temp + dt2.Select(“sku = '” + item.Trim() + "’ ")[0][“sLocation”].ToString() + “|”;
}
dtRow[“sLocation”] = temp.Substring(0,temp.Length-1);
}
i change (,) into (|) in the code no errors found but no thing found in slocation colom in excel sheet how to enter the value to

11

Have you tried my latest code with pipe symbol (if your excel has pipe symbol instead of comma)

12

yes and the slocation empty in excel sheet

13

could you share the excel file?

and have you use write range activity to write the data in the excel after the invoke code.

14

Main.xaml (8.1 KB) there is the work flow
order_details.xlsx (10.3 KB)
there is the excel sheet

15

you have to use write range after the invoke code and write your datatable to excel

16

@prasath_S @Adrian_Star thanks its working well … my pleasure :slight_smile:

1 Like
17

Happy to hear please mark it as solution and also close the other duplicate thread with the same topic.thanks

18

can i change the language of invoke code into vb not c# as my whole process support vb
@prasath_S

19

only invoke uses c# your other process can use vb syntax.

20

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