I am trying to merge 20 files (“file1.xlsx”,“file2.xlsx”,etc).
Now, after merging I want to add a column stating which file does each row belong.
How do i read the filenames for all the files and write it corresponding to each row in the new column.
Please find the attached sample for your problem - Excel File name with Merge.xaml (11.2 KB)
Note- just replace the path with your directory from where you have to read the 20 excel files.
- Read the all Excel Files from Directory and store it in a String Array.
- Use For Each Loop to process that string array which contains all files absolute paths.
- Inside For each loop use Excel Application Scope and pass the file object to pass an absolute file path to the Excel application scope + Read range activity inside the Excel Application scope.
- To make it more dynamic i have created Excel Application scope object and passed the first sheet name to read range so it will read every Xlsx files first sheet and return a datatable.
- Then i have used Add Data Row activity of Datatable and used the Very Interesting property of Datacolum Default Value and passed the file name by using Path.GetFileName().
- Then i have used Merge Datatable Activity.
External Reference - DataColumn Default Value
For more clear understanding check the Attached Sample above