How to split an excel file into 2 files based on a list,

[Soudios (Sou)]

how to split an excel file into 2 files based on a list,
Name 1 / 2 /3 = group 1 = create excel file 1 with name 1, 2 and 3
Name 4/5 = group 2 = create excel file 2 with name 4 and 5

[ppr (Peter Preuss)]

Split DataTable based on two unique values

Hi, I have a datatable as below [image] I want to split this datatable based on Unique values from both column A and Column B as below [image] [image] [image] and so on. Sample.xlsx (8.9 KB) The input file might have more input than what shown in sample file. Please let me know how to do this.

In your case we do groupBy by 1 key

(From d in YourDataTableVar.AsEnumerable
Group d by k1=d(“Group”).toString.Trim grp=Group
Select t=grp.CopyToDataTable).toList

Also Have A look here:

[HowTo] Overview on different options for grouping data and processing the groups

This HowTo introduces on the different options for grouping data from a datatable in order to process the grouped data. Introduction Grouping data and processing the grouped data is a common scenario e.g. when the grouped data is to aggregate like summing up, find maximum, get the average or concatening items. Lets have a look on following data: [grafik] A possible scenario could be: Create a report containing following information: the region code the sum of CaseCount per RegionCode t…

[prasath_S]

Hi @Soudios

Please try below steps,

1. Read the excel sheet data in the datatable variable name dt.

2. Create a array of string variable named arrStr, system.string in variables panel.

3. In assign activity,assign arrStr to

(From dtRow in dt.asenumerable
group dtRow by k=dtRow("Group").tostring.trim into grp= group
Select cstr(grp(0)("Group"))).toarray

4. Drag a for each activity and select type argument as string zand value as arrStr

5. Inside for each use write range activity and give sheet name as “Group” + item.tostring it want to create a new sheet for each group,else give “Group” + item.tostring + “.xlsx” in file name if want to create a new file.

6. And finally in write range give datatable as,

(From dtRow in dt.asenumerable
Where dtRow("Group").tostring.equals(item.tostring)
Select dtRow).copytodatatable

Thanks

[Soudios (Sou)]

i tried with filter data

if B equals 1 then write it on excel 1 even write it on excel 2 but the problem is that the bot erase the first data with the second data

[Soudios (Sou)]

@prasath_S

Can i do the same with filter data table ?

[ppr (Peter Preuss)]

@Soudios

tried with filter data

if B equals 1 then write it on excel 1 even write it on excel 2 but the problem is that the bot erase the first data with the second data

see there are many options to do it. Also in the HowTo Alternates were described including Non LINQ ones working with filter data. Feel free to select one your choices and implement it as described.

[Soudios (Sou)]

can’t find with filter option

[prasath_S]

Yes we can do, create a datatable called dtTemp in variables panel and type in default value as new datatable

Please follow the steps I mentioned and before write range use filter datatable and in input datatable give dt and output datatable give dtTemp,open filter wilzard select keep rows, in column give “Group” ,in operation select equals to , and in value give item.tostring

In write range instead of query I gave , type dtTemp.

Thanks

[ppr (Peter Preuss)]

[Soudios (Sou)]

@prasath_S

do you have a sample plz ?

[prasath17 (Guru)]

@Soudios - here you go…Please try as shown below…

Workflow…

1. Build Data table (You can use Read Range and Read your excel here)

2. For Each Row Code = Dt.DefaultView.ToTable(True,“Group”)

3. Filter Data Table.

4. Write Range

    Workbook path = "output" + row("Group").ToString + ".xlsx"
   

5. Final Output - 2 files created , one for each unique group…

Hope this helps…

[Soudios (Sou)]

@prasath17

Thank you for you answer but for names its just an exemple i have more than 10000 names i can’t build a data table

[prasath17 (Guru)]

@Soudios

but for names its just an exemple i have more than 10000 names i can’t build a data table

That was just an example. Please read my post again. I have mentioned as

“You can use Read Range and Read your excel here)”

[Soudios (Sou)]

@prasath17
@prasath_S
@ppr

the file where there is the list is in another excel file, so the robot has to compare the list of names in file 1 with the list in the 2nd excel file. What I did is that the robot makes a loop on the list

This is the file “ExcelRempli”

When i lunch the bot i have this error :

[Soudios (Sou)]

@prasath_S
@ppr
@prasath17

Someone can help me with these new information ?