Split one excel files into multiple files based on 100 rows per file

Help
21

Methods of Datatable, can you please explain me in detail.

22

@sushmitha.e Try this :

  1. Read the Whole Excel File using Read Range and Get the Datatable Output say DT.

  2. Use this Expression in an Assign Statement to make a Copy of the Original Datatable Schema.
    DT_100 = DT.Clone

  3. Use this Expression in an Assign Statement :
    DT_100 = DT.AsEnumerable.Skip(100).Take(100).CopyToDataTable

The Above Statement will Skip the First 100 rows and Take the next 100 rows only as a Datatable.

You might need to use a For Each to increment the counter as to adapt to take Each Hundred rows.

1 Like
23

Hey I have tried this logic, but in excel I am getting empty output . Can you please tell why I am not able to get in output.

Main.xaml (12.9 KB)

24

@sushmitha.e I don’t think you have followed the logic which I have Provided :sweat_smile: What does the Invoke Code do ?

25

Hey I have tried mine, where gets 100 records and write into new excel.

26

@sushmitha.e Ok. So you were able to get the Solution ?

27

Yeah but after the invoke code, I am getting rows as zero.

28

@sushmitha.e Can you tell me What is the Output you need so that we can direct you towards getting that Output only. The problem can be solved faster in that way :sweat_smile:

29

Yeah, from the given input excel above which have 1000 records, I need to get some 10 excels. 100 records of each should be.

30

@sushmitha.e So you have to Create Ten different Excel Files From the given Input or Ten Sheets in the same Excel File?
Also Can you tell me Why you wanted to not include the first 100 rows ?

31

Ten Separate Excels! yeah we can include.

32

Hi @anon62075255,

Thanks for the code and I tried to split the data to multiple excel files and it is working without any issues.
Could you please help me, I want the headers as well for each excel file whenever we are splitting the data.

Thanks,
Uday

33

I got the solution, before write range I had taken the build data table activity and added the Headers and stored in a newly created variable and Added a Add data row activity and again taken a write range activity and provided the newly created variable and it worked.