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
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.
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.
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 .
Loop through them within the loop of directories and get the individual file
Use excel application scope and provide the path, and read range
Use string manipulations on the file name to get the sheet name of the excel
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.
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.
So I just quickly created a sample workflow for you to make it more understandable…
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
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.
ashokkarale
MD_Farhan1
Ajay_Mishra
postwick
Dheerendra_vishwakarma
Anil_G
chandreshsinh.jadeja
Gautham_Pattabiraman
vrdabberu
aravindbalineni123