How to copy the column content in one excel sheet to the other sheet in the same excel

datatable
excel
activities

#1

I am just elaborating with similar scenario of my case. I have an excel in which the sheet 1 contains details across multiple columns. I want to copy the specific column values in sheet 1 to the sheet 2 in same excel. For eg., I want to copy the column 2 in sheet one and paste it in the column 5 of sheet two and copy contents in column 4 in sheet one to column 2 in sheet two and so on. Kindly note the column names in sheet 1 and sheet 2 will be different and all we are sure is the location which we need to copy from and to paste doesn’t change. Since I am new to UiPath it would be great if you can get a solution through available activities in UiPath.
Can you kindly help me here


#2

Hey @Vengatraj

you can just copy your first sheet data into a datatable then remove the unwanted columns from the copied datatable and then by using **set ordinal ** change the indexing of copied datatable as per your needs :slight_smile:

Regards…!!
AKsh


#3

Thanks for the swift response Akshay. I am not sure whether it will suit my requirement though. I have attached here an excel sheet which is actual replica of my scenario. The sheet 1 contains the available data and the sheet two contains the output which we need to accomplish. If you can come up with a logic to resolve the scenario that would be great. If you can give the XAML after solving it that would be great indeed.
As I said kindly try accomplishing though available activities since I am new to UiPath


#4

since I am new user I am not able to attach XLSX files and only one attachment only per conversation , so kindly find the second sheet below.


#5

ahh sorry did not notice. Surely i will give but i guess first dis group people will help you. i Guess

@certified - please help him :slight_smile:

*if you will not be able at the end let me know . Will share the same.

Regards…!!
Aksh


#6

Hi Akshay , I havn’t heard back from the group. Can you kindly check on the solution.


#7

Ohh sorry to hear that… Busy with deadlines :):sweat:

@Susana Su and @Lucas.Pimenta can you look into this? meanwhile?


#8

Hello @Vengatraj have a look into this, it is done by @aksh1yadav


#9

Hello @Vengatraj,

Hope it is not too late and this could help you ExcelColumns.zip (9.6 KB)

Regards,
Susana


#11

Thanks for the response Susana , I am new to UiPath so I am not able to comprehend or act upon the error message which I receive on execution of the XAML provided by you. And also can you give details on what does the Invoke method does with explanation of its properties . Can you kindly guide me here.


#12

Thanks Akshay for the response.


#13

No worries , thanks for the redirection Akshay.


#14

Hello @Vengatraj,

Has the Excel been modified? I think it could be by the name of the column…

Regards,
Susana


#15

No Susana , I am using the Excel that was in the attachment with the XAML file. I hope the rules were applied as per the requirement and I could see the Excel is similar to my Excel as well except for empty cells in second sheet (Which is expected for the output being populated). Kindly find screenshots one after another for the Excel.


#16

Second sheet :


#17

Hi @Vengatraj,

Maybe AddHeaders option is unchecked in Read Range activity… Try this and let me know :slight_smile:

ExcelColumns_v2.zip (9.7 KB)

Regards,
Susana


Add a column from excel to another
#18

Thanks a lot Susana , it works well. Can you kindly explain a bit on the Invoke Method and its properties in simpler terms so that I can understand and implement it for future. And also on what Set ordinal is all about.


#19

Hey @Vengatraj

Changes the ordinal or position of the DataColumn to the specified ordinal or position in a Datatable.

When you change a column’s ordinal, the column is moved to the new position in the collection of columns. Any columns between the previous and new ordinal will be renumbered, to adjust for a column’s new ordinal.

Note- If ordinal is less than 0 or greater than the existing number of columns – 1 (greater than the ordinal of the last column) then an Invalid ArgumentException is thrown.

Regards…!!
Aksh


#20

thanks a lot Akshay for the clear explanation


#21

@aksh1yadav and @Susana , Thanks yet again for your solution to my previous query. I am down to the actual problem statement now , I hope you guys can help me out here.

We will be provided with multiple sheets of same format as mentioned in the Sheet 1 in above example. We have to amend the details of one sheet after other to the Sheet 2 details.

To clarify furthur , we will be provided with excel as mentioned in sheet 1 , we now need to move the details to sheet 2 , which can be easily done by solution provided by Susanna earlier , but now we will be again provided with another excel similiar to sheet 1 now we need to amend the sheet 2 furthur with the details of the new sheet. i.e, one sheet after the other arrives in format of sheet 1 and we need to keep amending the details to the sheet 2 with the details of new sheet. I am attaching the three of excels here for clear representation of the request.

Test.xlsx (9.1 KB)

Test 2.xlsx (9.1 KB)

Test 3.xlsx (9.2 KB)

is this really possible to accomplish even having separate excels is also fine , all we need is keep amending the details to sheet 2 after the change in the column location which was provided in earlier solution. I will try from my end as well , but I hope you people can crack it much faster than me.

Can you kindly help here.