Get file name and add as column

Help
1

Hello everyone,
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.

2

Hey @jamnanin

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.

Explanation -

  1. Read the all Excel Files from Directory and store it in a String Array.
  2. Use For Each Loop to process that string array which contains all files absolute paths.
  3. 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.
  4. 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.
  5. 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().
  6. Then i have used Merge Datatable Activity.

External Reference - DataColumn Default Value

For more clear understanding check the Attached Sample above :slight_smile:

Regards…!!
Aksh

4 Likes
3

Thanks a lot @aksh1yadav. Exactly what I wanted.

4

Hi…how can we read the excel file with giving the excel path…can you help me with this

5

If your excel path/name is dynamic, you can use a variable and fetch that variable value from the source you have set (mail, csv, excel)

For excel read: