Split excel data based on coloumn data

Help Studio
, ,
1

Hi UiPath Community,
I have an excel file in which i need to split the excel data whenever the data (“Sr.No”) shows and write it into another excel sheet i will attach the sample excel file below please help to solve this problem.
sample.xlsx (9.2 KB)

2

Hi @alan.prakash ,
You need split original data table to multiple data table base on value “Sr.No”
that’s right?
regards,

3

Hii @alan.prakash

Use this Link Query

From row in dt.AsEnumerable()group row by row.Field(splitColumn) into g Select new{Category = g.Key,Data = g.CopyToDataTable()};

Cheers…!

4

Yes i need to split data based on value sr.no, i have extracted this value from pdf so whenever the sr.no comes splits the data while writing the data to another sheet it should contain that header also

5

Hi @alan.prakash ,

  1. Read the Excel file to a data table.
  2. for each row the data table
  3. check if your row is value “Sr.No"
    index of that value, than split data table
    I will try with your file
    regars,
6

Can u elaborate it where i need to place sr.no

7

Replace Category
Select new Sl.no = g.Key

8

it is showing error when i given this query

9

Hi @alan.prakash ,

Could you try with the below Steps :

  1. Assuming Read Range of the Excel sheet is performed and the Datatable is received as Output. Lets say the variable name as DT.

  2. Next, we can identify the row indices of the Keyword 'Sr.No' using the below Expression :

indicesArray = DT.AsEnumerable.SelectMany(Function(x,j)if(x(0).ToString.Contains("Sr.No"),{j},{-1})).Where(Function(x)x<>-1).ToArray

Here, indicesArray is of the type Array of Integer.

  1. Next, we can create Collection of Start and End row indices values based on the Keyword using the Zip method like below :
indicesArrOfArray = indicesArray.Zip(indicesArray.Skip(1).Append(DT.RowCount),Function(a,b)new integer(){a,b-a}).ToArray

Here, indicesArrOfArray is of the type Array of Array of Integer

  1. We could then use a For Each activity to loop over the created collection, and populate the Sheets one by one using the below Expression in the Datatable Property of Write Range activity.
DT.AsEnumerable.Skip(currentNumber(0)).Take(currentNumber(1)).CopyToDatatable

The Implementation :
image

Workflow :
Excel_SplitTables.zip (10.0 KB)

Do check and let us know if it works.

10

Thank u so much solution is working

1 Like
11

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.