How to split excel data using a specific column using Studio

Help Studio
, ,
1

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.

2

Hey

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

Regards

3

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
4

image
getting this eeror

5

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

6

Can’t follow this. . .

7

image
image953×328 27.2 KB

Please note that I am a beginner :slight_smile:

8

@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 :

    image
    image1022×517 18.4 KB

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

9

How to set Team as Group?

10

image

11

@raisonne ,

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

12

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

13

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

14

image

15

@raisonne ,

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

16

image

17

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

18

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)

19

image
it seems like ok already

20

image