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

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

image

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:

1 Like

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
  1. Drag a for each activity and select type argument as string zand value as arrStr

  2. 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.

  3. And finally in write range give datatable as,

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

Thanks

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

@prasath_S

Can i do the same with filter data table ?

image
image

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.

1 Like

can’t find with filter option

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

@prasath_S

do you have a sample plz ?

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

Workflow…

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

image

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

  2. Filter Data Table.

  1. Write Range

     Workbook path = "output" + row("Group").ToString + ".xlsx"
    
  2. Final Output - 2 files created , one for each unique group…

image

image
image

Hope this helps…

@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

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

"You can use Read Range and Read your excel here)"

@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

image

This is the file “ExcelRempli”
image

When i lunch the bot i have this error : image

@prasath_S
@ppr
@prasath17

Someone can help me with these new information ?