Excel problem how to add data

i have 2 excel that are the output sheets what i want to do is i want to add 2 columns of data from one to the other

so how can i copy data fromexcel2 i.e column volume and datatype from excel2 to excel1

  1. HOW to add data in excel1 in same sequence as its there in excel2
  2. how to add data as per country name i.e if sequence changes than how to make sure that data is there as per country name country name will field is is unique that will not repeat
    EXCEL1.xlsx (8.6 KB)
    EXCEL2.xlsx (8.6 KB)

Hi @mint

ā†’ Use the Read range workbook activity to read the Excel2 and store in a datatable called dt1.
ā†’ Use another Read range workbook activity to read the Excel1 and store in a datatable called dt2.
ā†’ Take an assign activity,

- Assign -> Output_dt = dt2.Clone()

ā†’ Take another assign activity to write the below linq expression,

- Assign -> Output_dt = (
                        From a In dt1
                        Join b In dt2
                        On a("Country").toString Equals b("Country").toString
                        Select Output_dt.Rows.Add({b("Country"),b("Rank"),b("Namee"),b("address"),b("Price"),a("Vol"),a("datatype"),b("target"),b("review")})
                            ).copytodatatable

ā†’ Use write range workbook activity to write the Output_dt to Excel1.

Hope it helps!!

2 Likes

Check the below workflow for better understanding, @mint
Regex_Practice.xaml (11.9 KB)

Output File -
For your reference I written the output in sheet2 in Excel1,
EXCEL1.xlsx (9.1 KB)
f

Hope it helps!!

@mint

try this in invoke code activity

Dim counter As Int32
For Each r As datarow In dt1.AsEnumerable
	If dt2.AsEnumerable.Any(Function(a) a("Country").ToString.Trim.Equals(r("Country").ToString.Trim)) Then
		counter=dt2.AsEnumerable.ToList.FindIndex(Function(a) a("Country").ToString.Trim.Equals(r("Country").ToString.Trim))
		r("Vol")=dt2.rows(counter).item("Vol").ToString
		r("datatype")=dt2.rows(counter).item("datatype").ToString
		Else
				r("Vol")="Not found"
		r("datatype")="Not found"
	End If
	next

1 Like

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