How to split excel data using a specific column using Studio

I have an excel file consisting all data. I wanted to split them into different files. for example:
image
In the above case, there should be 3 Files (Output)
Team A.xlsx
Team B.xlsx
Team C.xlsx

Note that the header should be retained.

Hey

please refer from this example
Just add the file creation
Main.xaml (14.9 KB)

Regards

Hi @raisonne ,

Could you Check with the below Steps :

  1. Create and Initialise a Dictionary variable in the below way using an Assign Activity :
TeamDict = new Dictionary(Of String,Datatable)
  1. Get the Team Set as Groups, convert to Datatable and add to Dictionary using an Assign Activity like below
TeamDict = DT.AsEnumerable.GroupBy(Function(x)x("TEAM").ToString).ToDictionary(Function(x)x.First.Item("TEAM").ToString,Function(y)y.CopyToDataTable)
  1. Now, We can loop through the Dictionary TeamDict and Write the Data to separate Excel Files like shown below :
    image

Let us know if you are not able to implement this or facing issues.

2 Likes

image
getting this eeror

Hey

it was just an example how it would work, you have to add the create file activity for reach row and the write range in the created file, you will to change properties as yours needs

Regards

Can’t follow this. . .


Please note that I am a beginner :slight_smile:

@raisonne ,

Selecting the Required Dictionary Key Value Types can be done as shown below :

  1. Click on Browse For Types.
    image

  2. Type Dictionary and Select the Highlighted Type from the List :

  3. Next, Specify the type and click on Ok.
    image

How to set Team as Group?

image

@raisonne ,

Could you let us know if you were able to implement it ? Is there any issue or errors you are facing?

Still trying to understand the instruction. . .getting a lot of errors

@raisonne ,

Let us know the Errors that you receive, so that we can help you further in correcting it.

Additionally, You could also show us the Screenshot of your completed Implementation.

image

@raisonne ,

Could you let us know what is the data type of TeamDict variable that you have used ?

image

@raisonne ,

It seems to be the right data type, Could you maybe try Cut the Assign Activity and Paste it again and Check whether it is able to resolve the errors. Also, assuming that the dtInitial is a Datatable type variable.

Correct, it’s a Datatable. The Value should only be this, right?

dtInitial.AsEnumerable.GroupBy(Function(x)x(“Service Territory 2”).ToString).ToDictionary(Function(x)x.First.Item(“Service Territory 2”).ToString,Function(y)y.CopyToDataTable)

image
it seems like ok already

image