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


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

Hi @rpa13

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

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

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

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

@Adrian_Star any help please for my problem ?

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

i have the same error even i remove the space


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?

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

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

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

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

yes and the slocation empty in excel sheet

could you share the excel file?

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

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

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

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

1 Like

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

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

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

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