Split One excel File into separate excel files

[priyanka.solanki]

Hi,

I need to separate my raw data by Account wise (Column A) and save to new excel (not tab) for each account.

I have tried it but filter by account column is not working.

[Yoichi]

HI,

The following sample will help you.

dict = dt.AsEnumerable.Where(Function(r) not String.IsNullOrEmpty( r("Account").ToString)).GroupBy(Function(r) r("Account").ToString ).ToDictionary(Function(g) g.Key,Function(g) g.CopyToDataTable)

Sample
Sample20240425-2.zip (9.6 KB)

Regards,

[priyanka.solanki]

thank you very much. In write Range Workbook how to change the path to save output files.
new path-
C:\Users\xbbnkmk\Downloads\Split Files

[Yoichi]

Can you try the following expression in File property?

System.IO.Path.Combine("C:\Users\xbbnkmk\Downloads\Split Files",currentItem.Key+".xlsx")

Regards,

[priyanka.solanki]

it worked.
thank you

[postwick (Paul)]

For Each Row in Data Table and use this expression to get just the unique accounts: yourDT.DefaultView.ToTable(true,“Account column name”)

Inside that loop… Filter Data Table into a tempDT to get the rows that match the account, then Write Range to Excel.

[priyanka.solanki]

Is it possible to have output workbook like attached screenshot?
Merge cell A1:G1 and Account Name should be as per account, for ex. - 367070 = First Trust and so on

[Yoichi]

Hi,

Can you try the following if these value is set as numeric in the sheet?

new object(){"","Total","","",currentItem.Value.AsEnumerable.Sum(Function(r) CDec(r("Units"))),currentItem.Value.AsEnumerable.Sum(Function(r) CDec(r("Amount")))}

Regards,