Merging of datatables

Help Studio
1

Hi,

I have 5 excel files in a folder and want to merge data tables. I used “For each file in the folder” activity to pass the files and I used “Excel application scope” to read the “sheets” by using “Get Workbook Sheets” and a “Read Range” activity. Now How can I merge the data tables from the files in the folder. How can I use 2nd “Read Range” activity to read other file to merge as “merge datatable” requires atleast 2 data tables.

Regards,

2

firstly create a BuildDt with the required columns
now in the merge datatble give build datatable output and readrange output(which was generated in loop)

3

I used “Build datatable” as you said and passed the output variable to the “merge datatable” but its giving below error.

Merge Data Table: ‘table’ argument cannot be null. (Parameter ‘table’).

I think the Build datatable is empty as it contains only headers.

Regards,

4

if possible can you share Excel files

5

Hey @raju_alakuntla ,

Check if you have initialized the table variable.

6

Hello @raju_alakuntla ,

To read the 5 files and merge the data into a single data table :

  1. Use an assign activity and assign
    (Array of String) FilePath= Directory.GetFiles(YourFolderPath).ToString
    (If multiple file types exists you can filter to get only excel files)

  2. Use Build Data Table with required columns (Say ResultDT)

  3. Use for each to loop through the files

  4. Inside for each :

  • If you have multiple sheets use get workbook sheets and loop over sheets one by one here. Here you need to use one more for each (Pass Sheet names list variable) and place the below sequence inside this for each :
  • Use Read Range: Pass File Path and Sheet Name (from the loop if multiple sheets exist) Save result in InputDT
  • Use assign activity and assign ResultDT = ResultDT.AsEnumberable().Union(InputDT.AsEnumerbale()).CopyToDataTable()
  1. Outside for loop use write range to write result to excel.

Hope it helps !!

Regards,
Rohith

7

hi @raju_alakuntla

good to see you back

image
image770×692 28.2 KB

try this way its working

cheers

9

What is the second Read Range?

can you explain me more.

Regards.

10

Hi @Shiva_Nikhil ,

Can you explain me What is the New DataTable() which you mentioned here?

Regards,

11

@raju_alakuntla

new datatable is like we are intialization the datatable with empty rows and columns

as you need to merge the datatable you are required to initialize the destination datatable

cheers

12

Hello @raju_alakuntla ,

That was a typo. There’s no 2nd read range

4. Inside for each :

* If you have multiple sheets use get workbook sheets and loop over sheets one by one here. Here you need to use one more for each (Pass Sheet names list variable) and place the below sequence inside this for each :
* Use Read Range: Pass File Path and Sheet Name (from the loop if multiple sheets exist) **Save result in InputDT**
* Use assign activity and assign ResultDT = ResultDT.AsEnumberable().Union(InputDT.AsEnumerbale()).CopyToDataTable()
13

Hi @raju_alakuntla ,
You can use merge data table
They are same format and number of column name?
you can send file to me , I will test them
input have more than 5 files ?
you can share input and expect output?
if can not public, you can send message to me, or create demo file input and expect output?
I will test them
regards,
LNV

14

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