Data Extraction from multiple Excel files

A folder Contains 100or more excel files with each excel file has multiple no of sheets , how can i extract the data from those excel file and then use that data for further task , can it be stored in one dataTable or in multiple DT.

Hope the below steps would help you resolve this issue

  1. First let’s get the files from the folder with this expression

arr_filepath = Directory.GetFiles(“yourfolderpath”)

Where arr_filepath is a variable of type array of string

  1. Now use a for each activity and pass the above variable as Input and change the type argument as string

  2. Inside the loop use a EXCEL APPLICATION scope and pass the filepath as item.ToString
    And get the output from that scope as out_workbook

  3. Inside the same for each loop and within that scope by itself use another for each loop and pass the variable out_workbook.GetSheets as input and change the loop variable name as item_sheets and change the type argument as string

  4. Now use a read range activity and pass the sheetname as item_sheets and get the output as dt

  5. You can merge all the dt with MERGE DATATABLE ACTIVITY but they all should have same column order, column format

Hope this would help you resolve this

Cheers @sunain_chahar

As the files are in huge numbers, you can also have the consolidated file or data using vba

Just pass the argument doe thr folder path.

Sub ConslidateWorkbooks()
Dim FolderPath As String
Dim Filename As String
Dim Sheet As Worksheet
Application.ScreenUpdating = False
FolderPath = Environ("userprofile")
Filename = Dir(FolderPath & "*.xlsx*")
Do While Filename <> ""
 Workbooks.Open Filename:=FolderPath & Filename, ReadOnly:=True
 For Each Sheet In ActiveWorkbook.Sheets
 Sheet.Copy After:=ThisWorkbook.Sheets(1)
 Next Sheet
 Filename = Dir()
Application.ScreenUpdating = True
End Sub

Thanks for the reply but can we store all the excel file details in one Datatable or we have to create different DT please elaborate this step.

Thanks Rahul i’ll try that

1 Like

We can provided if all the excel and its sheet has same number of columns, same order of columns, same type of columns and that can be done with

  1. Before keeping all the activities use a BUILD DATATABLE ACTIVITY and create a datatable with same table structure and name the output as dtfinal
    Then keep all the activity

  2. And right after here

Use a merge datatable activity and mention the source as dt and destination as dtfinal

  1. This will merge all the datatable to dtfinal

Cheers @sunain_chahar

Thank you ill try that