Merge multiple files in Excel

Help
1

Hello guys,

I just want to search for the following named files in a folder:
BIM, CIS, Detail

If present should merge all the data into one single excel file with these file names saved as a excel sheet names.

Thanks

2

Hi
this expression would help us to get those excel files alone
arr_filepath = Directory.GetFiles(“yourfolderpath”,“*.xlsx”)
where arr_filepath is array of string variable
–but to merge all those excel files together they must be having same set of columns or same number of columns

Cheers @NiranjanKN

3

This will search for all excel files in the folder. @Palaniyappan
But I want to search for only selected Files, and merge it…

Here, merging means, just copying different files data into different excel sheets.

As depicted above, it may be a .txt file also.
But, the data should be loaded into an excel sheet.

4

Fine
hope these steps would help you resolve this
–use a assign activity and with same expression
arr_filepath = Directory.GetFiles(“yourfolderpath”,“*.xlsx”)
where arr_filepath is a array of string variable
–now use a FOR EACH activity and pass the above variable as input and change the type argument as string in the property panel of for each loop
–inside the loop use a IF condition like this
item.ToString.Contains(“BIM”) or item.ToString.Contains(“CIS”) or item.ToString.Contains(“CVV”) or item.ToString.Contains(“Detail”)
IF the above condition is true it will get into THEN part where use another IF condition like this
item.ToString.Contains(“.txt”)
if true it will go to THEN part where we can use READ TEXT FILE activity and mention the file path as item.ToString and output as str_text a string variable
–followed by that still inside the THEN part use a GENERATE DATATABLE activity where pass the str_text as input and get the output with a variable of type datatable named dt_text
or
if the above fails it will go to ELSE (if its xlsx file) where we can use EXCEL APPLICATION SCOPE and pass the file path as item.ToString and use READ RANGE activity and get the output with a variable of type datatable named dt

–now next to this INNER IF condition (while still being inside the outer IF condition) use a EXCEL APPLICATION SCOPE AND WRITE RANGE ACTIVTY and mention the sheetname as “Sheet”+(counter+1).ToString where counter is a int32 variable with default value as 1 defined in the variable panel
–now outside the outer IF condition and atlast to the for each loop usea assign activity like this
counter = counter +1

so the structure would be like
–assign
–for each
–IF
–THEN
–IF
–THEN
–READ TEXT FILE
–GENERATE DATATABLE ACTVITY

       --ELSE 
       --EXCEL APPLICATION SCOPE
       --READ RANGE

–ELSE (leave it blank with no activities)
–assign with counter = counter +1

kindly try this and let know for any queries or clarification
Cheers @NiranjanKN

1 Like
5

Hello, @Palaniyappan @sarathi125

The bot should search for the folder and it needs to merge only Excel files in that folder and then the Final File should be like this Report.xlsx (12.8 KB).

A new tab called val needs to be created in Report excel and these these image to be extracted from MIC tab.
And if TOTAL Column is greater than 1000, then a value a BOOL column should be placed 1 else a value 0 to be placed.

Thanks

2 Likes
6

@NiranjanKN,

ReadExcelsAndUpdate.xaml (16.5 KB)

Check the attached xaml, tested and working as per my understanding.
If any issues, please try to debug yourself and fix it from your end.
Used very basic activity and logic to complete the requirement.

2 Likes
7

Thanks @sarathi125 I’ll do try it ReadExcelandMerge.xaml (22.6 KB)

8

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