Copy multiple files into 1 file

[85133021]

Hello, I am rather new to RPA and need some help. I have a folder that hold multiple xl sheets, i want to copy data from each file and paste into 1 file. Each file holds the same headers.

[ashokkarale]

@85133021,

I’m not giving you directly the code as you are still in learning phase and you should be coding yourself. It’s pretty much simple use case. Here are the Solution Design level steps.

1. Read the Files: Use the “Directory.GetFiles” activity to get a list of all Excel files in your folder.
2. Loop Through Files: Use a “For Each” loop to iterate through each file.
3. Open Excel File: Inside the loop, use the “Excel Application Scope” activity to open each Excel file.
4. Read Data: Use the “Read Range” activity to read the data from each Excel file.
5. Write Data: Use the “Write Range” activity to write the data into a single Excel file.

Here’s a more detailed breakdown:

1. Read Files

Use the Directory.GetFiles method to get a list of Excel files in your folder. Store this list in an array variable.

2. Loop Through Files

Use a “For Each” loop activity to iterate through each file in the array variable.

3. Open Excel File

Inside the loop, use the “Excel Application Scope” activity to open each Excel file.

4. Read Data

Within the “Excel Application Scope”, use the “Read Range” activity to read the data from each Excel file. Store this data in a DataTable variable.

5. Write Data

After reading data from each file, use another “Excel Application Scope” activity to open the output Excel file where you want to consolidate the data.

Inside this scope, use the “Write Range” activity to write the data from each DataTable into the output Excel file. Make sure to write the data below the previously written data to avoid overwriting. Use Append Range activity for second file onward.

LLM helped me to write this answer

Thanks,
Ashok

[postwick (Paul Ostwick)]

Create sourceDT and finalDT variables as System.Data.Datatable

• For Each File in Folder
  ** Use Excel/Read Range into sourceDT
  ** Merge Data Table sourceDT into finalDT
• //end For Each

Now you have all the data in finalDT and can Write Range it to a new file.

[Anil_G (Anil Gorthi)]

@85133021

1. For each file in folder to loop through all excels
2. Inside loop read range workbook and give path as currentfile.FullName
3. Use merge datatable and merge the read data to finaldt
4. Write the finaldt to a output file as needed

Cheers

[mukesh.singh]

@85133021

1 ) Put all file in one folder
2 ) Read all the file
3 ) Add all files in one data table / or database
4) Write the data table to the files

Note: if file size is huge then use the data base and get good amount of RAM.

[lrtetala (Lakshman Reddy)]

Hi @85133021

Please check this

Copy folder with Excel files with columns together into a new Excel file

Hi @Matthias_Kuntz Welcome to Community!! Can you try the below code to write multiple excels to single excel in different sheets [image] Input: [image] Output: [image] [image] Sequence2.xaml (17.1 KB) or If you want to write multiple excel into single excel in single sheet then try below code [image] Merging Excels.xaml (18.9 KB) Cheers!!

Regards,

[85133021]

Hello,

Thank you for your help so far, what you are saying makes complete sense but for some reason its not working and i don’t know what i’m doing wrong.

Thanks
Vicky
Main.xaml (17.5 KB)

[lrtetala (Lakshman Reddy)]

@85133021

Your workflow looks perfect…Can you please check folder path and files are in folder and debug the process

Regards,

[85133021]

When i click run it opens up GR PLAN file and only 1 of the files in the folder and does noting else.

[lrtetala (Lakshman Reddy)]

@85133021

I have made some changes can you please try below

BlankProcess22.zip (84.6 KB)

Regards,