Combine multiple Excel sheets into one using UiPath

[@softvlogs1360]

Can you please make a video on change position of coloumns

[@autopilotatwork]

No problem. The next video will be about how to change the position of columns in the output file :)

[@softvlogs1360]

@@autopilotatwork Thank you

[@varadarajan277]

Thank You!

[@autopilotatwork]

You're welcome!

[@ooikaisheng3500]

How about multiple sheets into one sheet with all of the sheets has a common column as primary key? Thank You

[@autopilotatwork]

Hi @ooikaisheng3500, the workflow will merge the shared column from all sheets into a single one. For instance, all Excel sheets demonstrated in the video feature a shared column titled "Name," and the resulting output file consolidates data from all "Name" columns into one.

[@ooikaisheng3500]

Hi@@autopilotatwork , thanks for the reply. How about I don't want the name to be duplicated. Example, 'rock' has B in column E, I want 'rock's data in the same row of rock at row 5. Is that possible to be done in UiPath?

[@autopilotatwork]

Hi ​@@ooikaisheng3500, in this case, instead of merging the data tables, you might wanna use another activity called Join Data Tables. This activity will allow you to join the data tables using the common column. You can also specify the join type you want. Hope this helps :)

[@subbu4754]

Hi, Help me How to remove filters in UiPath ??? By using Ctrl+Shft+L

[@autopilotatwork]

In Read range activity, make sure UseFilter property is unchecked. It's unchecked by default, so the filter wont be applied. If you want to use Ctrl+Shft+L, you can try HotKey or invoking VBA

[@owgjunxian7111]

hi at the very last step when im writing cell they say my datatable cannot be converted into string

[@owgjunxian7111]

any way to help?

[@autopilotatwork]

@@owgjunxian7111 please check if variable type of ListOfSheet is List

[@thixuyenle9056]

thank you for this helpful video. Your solution is correct, but iam just wondering why we need condition "if" here, i tried without if-condition, it prints just the last sheet. Can you explan me more detail why if-condition is used there? Thank you.

[@autopilotatwork]

Hi Thi, the if condition is just to initialise the data table dtMain only if it is empty. We only need to initialise the data table once. In this example we initialise by cloning the data structure of first sheet.

[@erbar6119]

hi Michelle, me again. now that i've learned from you how to combine excel files into one, i need now to combine all the multiple worksheets within the newly created file into one final NEW excel worksheet. Before that happens, the multiple worksheets have to be renamed as per their original file names, i.e., files Dance.xlsx, Rest.xlsx and Rejoice.xlsx should have a combined new file with sheets Dance, Rest, Rejoice, respectively. thanks again in anticipation.

[@erbar6119]

When i copied and paste this xaml program, i get a null file but without any error prompt. what could be the issue

[@autopilotatwork]

@@erbar6119 I have checked the workflow. It was running fine for me. Perhaps you could try updating the file location within the workflow.

[@autopilotatwork]

Hi er bar, not sure why you need to rename them if you are going to merge them into one anyway. You could try doing a For each item on Filenames and then try to get just the filename by using path.getfilename(item.tostring).split("."c)(0) before writing to the final file.

[@erbar6119]

@@autopilotatwork tks again, i managed to rerun and got something, only it is only one worksheet's data instead of 7.

[@erbar6119]

i thought of renaming before merging coz :1) the original file names are unique persons' names 2) i wanted these info (person names) extracted as a new column entry to indicate that the database (the other columns/rows) pertains to this particular person and so on and so forth.

[@mikatapio7375]

Can you explain how to combine multiple excel files into one Excel, so that each separate Excel has its own sheet in the new Excel?

[@autopilotatwork]

Hi Mika, hope you are well. Here's a video that explains how to do it kzbin.info/www/bejne/f4TEdmmCmbqsiLc

[@cssubbaraja]

it is creating extra column in ouput

[@autopilotatwork]

Have you tested with the package? Please see the description for the link.

[@jingjing226]

Hi, what happens if we use a get workbook sheet function and use a for each activity to loop it and have multiple sheets to store in the DataTable? Thanks if you could reply fast!!!!

[@autopilotatwork]

Hey Jing Jing, get workbook sheet function will only get you one sheet so it's not gonna work if you try to loop it.

[@kirankiran-bf3ow]

hello i have task so pls do it on video in ths topic Task: Extract DPs into separate excel sheets with respect to Region specified. Please extract all the DPs into separate excel files, where data has been captured (excluding DP codes with decimal values, i.e. only parent DPs required, have attached the DP list ) And in each file data needs to be separated as per Region (List of regions provided in Dp list sheet) For reference have attached an sample output file, please follow the same format(File Name - 746_Data.xlsx) Please find the attached source files on which the extraction needs to be done(Retagged)

[@autopilotatwork]

Hi Kiran, your files are not accessible but I sort of understand the task. I'll do a video based on similar idea.

[@jiawu9893]

why I cannot get sequence, or excel application scope on my uipath app?

[@autopilotatwork]

Hi Jia wu, did you download Uipath studio community edition?