Merging random data from two excels to create a new excel


#1

Hi,
I have to add data of a particular column from one excel under specific column of other excel thus creating a new excel all together.
Like “employee ID” from excel 1 goes under “E-ID” of excel 2,
“Task Assigned” in excel 1 goes under “Activity” in Excel 2 etc
The sequence of columns to be merged in excel 1 and 2 is not the same, as in 1st column of excel 1 is 3rd column in excel 2 etc.
Can some one please guide me as to how should i go about to create this new excel. It will really be a great help! Thank you in advance!


#3

@Shatakshi_Mishra

Suppose if you want merge all the columns from excel sheet 1 to excel sheet 2 then
re arrange the columns in such a way that you want merge the columns of excel sheet 1 with sheet 2

For example
excel sheet 1 having columns abc, bcd, cde let us take it dt1
excel sheet 2 having columns hfg, ijh, mno let us take it dt2

so you want to merge bcd to hfg, cde to ijh, abc to mno.

then create the array of columns in the order arr A={bcd,cde,abc}

then use dt3 = dt1.Select().CopyToDataTable().DefaultView.ToTable(False,arr A)

Now use Merge DataTable Activity
give source as dt3 and destination as dt2

Regards,
Mahesh


#4

Thank you for your help!
But 1st excel has 8 columns and 2nd has 24 so it’s like Column1 from 1st excel is to be merged into Column3 of 2nd excel and column2 of 1st is to be merged with column2 of 2nd and so on… what i mean to say is the order of columns to be merged is not uniform… so how do i go about??


#5

@Shatakshi_Mishra

Run for each row for excel sheet 1
Add Data row activity to add a new row for excel sheet 2

and add the values of respective columns.
For Example
excel sheet 1 have 3 columns and excel sheet 2 having 4 columns

You want to add ist column of 1st excel sheet to 3rd column of 2nd excel sheet
then Add data row
{"","",row(“Column 3”).ToString,""}

Like this
Regards,
Mahesh


#6

Thank you so much!


#7

Hey! sorry to bother you again, I am actually quite new to Uipath so can you please help?
Is there any way i can access the individual elements of a cell under a particular column?
Because i have to merge the 2 excels using the employee ids from the two excel sheets. so is there any way i can access the value stored, one cell at a time, under employee id and check their equality?


#8

@Shatakshi_Mishra

You can access if you know the column name and row index

Regards,
Mahesh


#9

can you please elaborate a bit?


#10

@Shatakshi_Mishra

You are having a datatable dt
dt having 6 columns and 20 rows.
You can access 6th row and 5th column value like this

string a= dt(5)(4).ToString or dt(5)(“Column Name”).ToString

Regards,
Mahesh


#11

Okay Thank you!!


#12

Hello Mahesh,

I followed your points and done one work flow, But I’m getting some error on

dt3 = dt1.Select(“Employee Name”).CopyToDataTable().DefaultView.ToTable(False,arr A)

Error is: Assign : Syntax error: Missing operand after ‘Name’ operator.

Please request to solve this

Regards,
Ganesh