Data merging from Multiple excel files into one

Help Activities
, ,
1

Hello All,

I have 28 excel files and from each file I only need 3-4 columns of data. How to do this?

2

@ramshiva_reddy after reading the excel file you can use the ā€˜Filter Datatableā€™ activity to only include specific columns and then use the ā€˜Merge Datatableā€™ activity

Using a for each loop you can iterate over a list containing all paths of the 28 excel files. You can use this list as the input to read range inside the for each loop

1 Like
3

Hi @ramshiva_reddy

Use for each file in folder activity to iterate the files from the folder.
Take an assign activity and store the each file path in a variable.
In Read range workbook activity or in Use excel file pass the file path variable.
If you want to keep the 4 columns and remove the other columns. Then use the filter datatable activity to keep the required fields.

Hope it helps!!

4

@ramshiva_reddy

Create an excel file Fill the excel names and column ranges and sheet name in a sheet
Take For each loop
and then write the data to the excel

5

Hello,
If I require 1st,3rd and 7th columns from 1st excel file, 2nd, 5th and 6th columns from 2nd excel file and so on. there is no particular format of columns. How to do this?

6

Hi @ramshiva_reddy

Try this:

  1. Use a ā€œFor Eachā€ loop to iterate through each file in the list.
  2. Use ā€œRead Rangeā€ activity.
  3. Use the ā€œFilter Data Tableā€ activity to filter the DataTable and keep only the specific columns you need.
  4. If you want to combine data from all the files, use the ā€œMerge Data Tableā€ activity to merge the filtered DataTables into a master DataTable.
  5. Continue with the next iteration of the loop until all Excel files have been processed.

Hope it helps.

7

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