Split Excel File/Data table based on specific criteria

Hi,

This is regarding splitting excel data into different worksheets.

  1. Eg . A worksheet has 100 rows
  2. There are two parameters/variables Morning and Evening whose values are in percentage(lets say morning = 30% and evening=70%)
  3. The need is to split the data in worksheet into two worksheet.
  4. The first worksheet should have 30% of the total rows and second should have the 70%.

Request ideas and suggestion to implement this scenario in uipath.

Thanks
Satish

@satish5.prasad

  1. Use Read Range activity to read the data from excel file and will give you output as DataTable and say ‘DT’. And then try below select queries to filter the data.

      morningDT = DT.Select("ColumnName = 'morning'").CopyToDataTable
    
      eveningDT = DT.Select("ColumnName = 'Evening'").CopyToDataTable
    

And then use Write Range activity and pass that DataTable to write into Excel file.

Example.xlsx (8.9 KB)

Hello Laxman,

Thanks for prompt response, however, attached is the excel file as an example.
The master sheet is the input and the next two sheets are the outputs needed.

The split is not basis the Column name but the number of rows.
From what ever number of rows are available in master sheet.
X percent should be copied in one sheet and Y percent should be in another sheet.

Thanks
Satish

@satish5.prasad

Try like this:

Assume that you have 100 records in the DataTable. It has to split by 30 and 70. Like below you can able to assign to a datatable.

dataTable.AsEnumerable.Take(30).CopyToDataTable()

dataTable.AsEnumerable.Skip(30).Take(70).CopyToDataTable()
1 Like

See how this would be in an example:

Hi @satish5.prasad,
You can achieve it by following approach

  1. Read the excel file
  2. Get the number of row value morning sheet
    Morning=Convert.ToInt32(Math.Round((30 * OutputData.Rows.Count) / 100))

3 Get the numbaer of row value in Evening Sheet
Evening = (OutputData.Rows.Count) - Morning

4, Write the datatable using write range activity
OutputData.AsEnumerable.Take(Morning).CopyToDataTable
OutputData.AsEnumerable.Skip(Morning).Take(Evening).CopyToDataTable

Please go through the attached file if you are facing any issue.
ExcelDemoPercantage.xaml (6.8 KB)

Regards.

1 Like

Dear Friends,

The flow is working, Many thanks for your valuable inputs.

Regards
Satish Prasad

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