Excel query data

Hi, I want to ask the simple query,
I have these five folders. In these folder I have three excel files.

I have another folder which is Merged_Data,
Merged the data like in Data_00000025 I have three files so I want to create new excel in Merged_Data folder with merged all files in different sheets,
Like in folder Data_00000025 I have these three files

Note: Sheet name is same as excel name. Like if excel name is InternalParts then sheet name is also InternalParts.

In the Merged_Data folder I want to create new file copy the data of three excels in different - 2 sheets, Like sheet1, sheet2 & sheet3 as I have only three Excels in single folder, same for rest of the folder. In each folder I have three files. Just I want to copy the data of the three excel file in New excel in different - 2 sheets and save in Merged_Data folder.

Same I want to do for all five folder.

I want to five excel files in the Merged_Data folder. Hope you understand

@Palaniyappan @HareeshMR @Shubham_Varshney @Lahiru.Fernando

1 Like

Hi ,

I would suggest you to go with custom activities for this.
You could easily find in manage package else you can write your own. As doing the same using uipath native activities could be tricky way.

For native uipath activity you can truly read range and write range activity.

Write range will create new sheet with sheetname if not exist.

Please let me know if i was able to help you out

Hi @balkishan

String var=Directory.GetFiles(InputFilePath)

Use Read range and pass the item in filepath along with sheetname

Use MergeDatatable

Use Write Range

Thanks
Ashwin S

1 Like

sorry I didn’t get bro can you explain it more. what is InputFilePath.

Bro I have these folder.
In each folder I have three excel file
Like this
(Note. Excel file name is same with sheet name. )
Like for InternalParts sheet name is

So I want to create a single excel for all three excel and copy the data of each file in single sheet.
Like InternalParts data in Sheet1, InternalLabor excel data in Sheet2 and Data_00000025 excel data in sheet3.
The new Excel file will save in a Merged_Data folder.
Hope you understand.

@hk803592
@Palaniyappan Kindly reply

1 Like

Some complex part @balkishan

But easy to follow

  1. As you will be able to get the directories in the mainfolder using directory.getdirectories, loop through the folders inside and for every folder, open the files inside using the same directory.getfiles .
  2. Loop through them within the loop of directories and get the individual file
  3. Use excel application scope and provide the path, and read range
  4. Use string manipulations on the file name to get the sheet name of the excel
  5. Create a new sheet in the merged data and provide the same path so that it will open the same file everytime and mention the sheet name as dynamic in the write range activity. sheet name will be the one you got in the 4 step.

you are done bro

1 Like

Hello @balkishan

Sorry for my late reply. Just saw your post :smiley:

Yes, you can do it.
First you need to to get the list of folders in your parent folder.
Then you need to loop through each folder and get the list of files that resides in each folder.
Next, within the folder loop, we need to loop through the file list to access them and do your processing on those. :slight_smile:

So I just quickly created a sample workflow for you to make it more understandable…

Hope this helps…

GetSubFolders.xaml (7.2 KB)

2 Likes

Fine hope these steps would help you resolve this
—use a sassing activity like this
arr_folderpath = Directory.GetDirectories(“yourfolderpath”)

Where arr_folderpath is a string array variable

—now use a for each loop and pass the above variable as input and change the type argument as string and change the variable name from item to folderpath
—inside the loop use a assign activity like this
arr_filepath = Directory.GetFiles(folderpath,”*.xlsx”)

Where arr_files is a string array variable

—now use another for each loop inside the current loop next to this assign activity and pass input as arr_files and change the type argument as string and the variable name from item to filepath

Inside the loop use excel application scope and and pass the input as filepath
—inside the scope use a read range activity and get the output with a variable of type datatable named dt
—next to this excel application scope use another excel application scope and pass the file path of MERGED FOLDER with the file name like this
Mergedfolder path/“ + Path.GetFilename(filepath)

Now inside this scope use Write range activity and mention the datatable as dt and in the sheetname mention as “Sheet”+counter.ToString
Where counter is a variable of type int32 with default value as 1 defined in the variable panel
So this would write the first file in sheet one

—next to this excel application scope use a assign activity to increment the counter value like this
counter = counter + 1

Hope this would help you
The structure would be like this
—assign. //get folderpath
—for each
—again assign //get filepath
—use another for each
—use excel application scope to read file
— use another excel application scope to write file

Cheers @balkishan

1 Like

Thanks for the replay.

From this String
Files\Data_00000025\Data_00000025.xls

I need only Data_00000025

Note;- I not to use Replace("\Data_00000025.xls", “”).Replace(“Files”, “”).
This method is already used but not working, Folder Name and Files is Changing. So we can’t use the Replace method.
Only Prefered Index method.

@Lahiru.Fernando @HareeshMR

Thanks @Palaniyappan @Lahiru.Fernando @HareeshMR

1 Like

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