Different sheet of excel

how to create a one excel sheet from different files

@guptasweb

You mean want to read data from different excel files and write into new sheet ?

correct…how to do it??

@guptasweb

Is all excel files have same headers ?

If yes then follow below steps.

  1. Create one DataTable using Build DataTable activity with all the columns.

  2. Use ForEach loop activity to loop one by one excel file.

2.1. Inside loop, use Read Range activity to read the data from Excel file and it will give you output as DataTable.

2.2 Use Merge DataTable activity to merge the DataTables.

  1. At the end, use Write Range activity to write the data into Excel file.

Hi ,
could anyone please help me how to create different sheets as per the Department column , Sort Salary Colum in ascending order and employees whose salary is less than 50000 add status as yes else keep the status as No .

By using linq query or using any activities
Employee_Data (1).xlsx (10.6 KB)

why merge data table is used?

You don’t have to do this step. Read Range builds it for you based on what’s in the spreadsheet.

1 Like

@postwick

If we won’t create empty DataTable with Build DataTable activity then Merge DataTable will throw error as per my above mentioned steps.

We need to skip the Merge DataTable in the first iteration to avoid that error then.

in which step append method is used?

if there are 50 files then how to do it??

for each activity is used or for each files in folder activity is used?

Oh, for the final destination DT you’re right!

@guptasweb

Yes we can use Append Range activity also here but Bot will open Excel file each time to write the data. It will impact performance of the Bot. That’s the reason I suggested to use Merge DataTable activity.

You can use either of the one here.

If you are using For Each activity then you need to pass below value to it.

      Directory.GetFiles("FolderPath","*.xlsx")

If you are using For Each files in Folder activity then you can pass folder path directly and other options as required.

if i use for each activity ,it includes all the excel files and how to save in the new folder??

@guptasweb

You want read all the excel files and write the data into new excel sheet right. If yes then you need to follow this approach only.

Sorry I didn’t get you. What you want to save in new folder ?

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