Split Excel to multiple Excels

praneeth.v.c · September 19, 2023, 8:54am

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

Nguyen_Van_Luong1 · September 19, 2023, 8:57am

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,

mpraveen1902 · September 19, 2023, 8:59am

can you share the sample data

@praneeth.v.c

praneeth.v.c · September 19, 2023, 9:09am

Hi @Nguyen_Van_Luong1 & @mpraveen1902 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|

Nguyen_Van_Luong1 · September 19, 2023, 9:41am

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

ppr · September 19, 2023, 10:09am

@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

ppr · September 19, 2023, 10:13am

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

ppr · September 19, 2023, 10:15am

For learning purpose:

mpraveen1902 · September 19, 2023, 12:18pm

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 :-
101 : -
102: -

praneeth.v.c · September 19, 2023, 2:12pm

Hi @mpraveen1902 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

Nguyen_Van_Luong1 · September 20, 2023, 1:13am

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,

mpraveen1902 · September 20, 2023, 5:24am

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