How to split data and save in excel files

Hi,
I have this excel file.

There are 6 stores in this file, and I need to split data by each store and save in 6 different excel files.
For example,
File 1 should be Header + Whole rows for Store A + SUM
File 2 should be Header + Whole rows for Store B + SUM
File 3 should be Header + Whole rows for Store C + SUM

Can anyone help me solving this?
Thanks in advance!

1 Like

use “Filter Table” activity

you can filter your table by “Store” column.

Hi @Lea
FirstVariableDT= DtExcel.Select([“Store” =‘A’]).copyDatatable

Then save it to excel.

Then do it to another store.

cheers :smiley:

Happy learning :smiley:

1 Like

@Lea

Welcome to the uipath community.

  1. First use Read Range activity to read the data and will give you output as DataTable and say ‘DT’.

  2. And then use below query to find all unique stores.

         storeDT = DT.DefaultView.ToTable(true,"Store").CopyToDataTable
    
  3. And then use ForEach Row activity to iterate one by one store and find all rows.

           ForEach Row in storeDT
               filterDT = DT.Select("Store = '"+row("Store").Tostring+"'").CopyToDataTable
    

And then write into Excel file using Write Range activity.

Range: “Sheet”+flag.Tostring
DataTable: filterDT

Note: where flag is Integer variable and intialize to 1 and increment it one by one.

1 Like

Thanks! It was helpful.

I’m new to RPA (UiPath) and I still do not follow your solution even though I tried…
Do you mind if I ask to share the workflow? (Sorry, I can’t attach the excel file cause I’m not allowed to…)
Thanks again!

I’m new to RPA and having a hard time learning.
Thanks for your help!

1 Like

No worries @Lea

Happy to help here just post your inquiry here and surely somebody will address it :smiley:

cheers :smiley:

Happy learning :smiley:

2 Likes

@Lea

May I know what error are you getting here ?

@lakshman
storeDT = DT.DefaultView.ToTable(True,“Store”).CopyToDataTable
–> It says ‘CopyToDataTable’ is not a member of ‘System.Data.DataTable’.

@Lea

Check below thread and follow the steps to resolve this issue.

AsEnumerable is not a member of 'System.Data.Datatable'