Split Excel to multiple Excels

Hi,
I want to split my excel data based on Fund number and output has to be created on its own with that fund number.

For example, if i have list of funds in Excel A ie.

Fund No. 100,101,102,103…

I need an excel output to be created. with that fund numbers.

Thanks

Hi @praneeth.v.c ,
You can read original excel file to get data table call is dtOriginal
You filter get 1 value of no , eg:100
each value have 1 data table
then write each data to a file
my way
Split.xaml (6.8 KB)

can you share your file?
regards,

can you share the sample data

@praneeth.v.c

Hi @Nguyen_Van_Luong1 & @Praveen_Mudhiraj cant share the file but it goes like this.

I need all 100 Fnd data to be segregated and saved in new excel file.

|Fund Number|Name|Region|
|100|XYZ Inc.|USA|
|100|ABC.Inc|USA|
|100|LYM.Inc|USA|
|100|REF.Inc|USA|
|100|TRC.Inc|USA|
|101|Agency.Inc|America|
|101|Group.Inc|America|
|101|Beaware.Inc|America|
|101|Gopal.Inc|America|
|102|Google.Inc|India|
|102|Amaazon.Inc|India|
|102|XYZ.Inc|India|
|102|ABC.Inc|India|

Hi @praneeth.v.c ,
You can use filter data table

@praneeth.v.c

We can model it as a GroupBy Case

LINQ Approach:
Assign Activity:
TableList | List(Of DataTable) =

(From d in YourDataTable.AsEnumerable()
Group d by k=d("Fund").toString.Trim into grp=Group
Select t = t.CopyToDataTable).toList

The we loop over the TableList and write it back to Excel:

For each Activity: item in TableList | TypeArgument: DataTable

  • write range: DataTable=item, SheetName: “Sheet_” & item.Rows(0)(“Fund”).toString.Trim

Non-LINQ Approach

For each Row Activity: row in YourDataTableVar.AsEnumerable.DefaultView.ToTable(true, {“Fund”})

  • Filter DataTable: in: YourDataTable,out: dtFiltered, Condition: “Fund” = row(“Fund”).toString
  • write range: DataTable=dtFiltered, SheetName: “Sheet_” & row(“Fund”).toString.Trim

For learning purpose:

1 Like

HI @praneeth.v.c

you can try this xaml

xaml : - Saparate the data tables.zip (2.0 KB)

it will return all 100 ,101 and 102 in separate data table

for the reference you can see the output

100 :- image
101 : - image
102: - image

Hi @Praveen_Mudhiraj Thanks for the help in this case it is copying into same excel with different sheet names but i want it to move to different excels based on fund numbers

Hi @praneeth.v.c
my input and output


my 2 ways
by LINQ and activity Filter data table
Filter.zip (40.6 KB)

hope it help,

1 Like

Hi @praneeth.v.c

i was attaching the xaml it will copy the 100,101 and 102 to different excel sheets with based on the fund numbers
xaml :- Saparate the data tables.zip (1.8 KB)

for the reference you can see the excepted output all separately withe fund numbers

it will write separately all 100,101 and 102
100 :- image

101 :- image

102 :- image

Let me know its working or not for you

1 Like

Thnaks @Nguyen_Van_Luong1 & @Praveen_Mudhiraj it worked

1 Like

Cheer @praneeth.v.c
happy automation

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