Read and split datatable

Help Studio
1

Hi there,

The image displays a table containing financial information organized by deposit type, banking type, account number, cost center, and amount. (Captioned by AI)
The image displays a table containing financial information organized by deposit type, banking type, account number, cost center, and amount. (Captioned by AI)1064Ă—550 80 KB

I want to split the datatable. There 3 deposits in the deposit column. I want to split them into individual datatable. For example first deposit and the first 4 rows are one deposit like wise the rest followbthe same. How can i split the datatable.

Forum Engagement Daily Reports
2

Hey @mark_rajkumar1 can you try with this approaches.

  1. Read Input Data
    • Use Read Range → dtInput = Read Range from Excel.
    2. Extract Unique Deposit Values
    • Use Assign Activity:

cheers

dtUniqueDeposits = dtInput.DefaultView.ToTable(True, “deposit”)

This will store unique deposit values in dtUniqueDeposits.

  1. Iterate Through Each Unique Deposit
    • Use For Each Row in DataTable → dtUniqueDeposits
    • Inside the loop, assign:

currentDeposit = row(“deposit”).ToString
dtFiltered = dtInput.Select(“[deposit] = '” + currentDeposit + “'”).CopyToDataTable()

• Now, dtFiltered contains rows matching the current deposit value.
4. Save or Process Split DataTables
• Use Write Range (inside the loop) to store each dtFiltered DataTable into different sheets or files:
• For Excel Output:
• Sheet Name: currentDeposit
• Write Range: dtFiltered
• For Separate Files:
• Filename: “Split_” + currentDeposit + “.xlsx”
• Write Range → Save to a different file.

Alternative Approach: Using LINQ (Faster)

Instead of using Select(), you can use LINQ:

dtFiltered = dtInput.AsEnumerable().
Where(Function(row) row(“deposit”).ToString = currentDeposit).
CopyToDataTable()

3

@mark_rajkumar1,

Try this approach:

  1. Read the file into a datatable
  2. Use For Each Data Row in Datatable activity to iterate through all the rows
  3. Outside for each, declare a variable to store deposit number
  4. Inside for each check if deposit variable is empty or not. If empty, assign current row deposit value. If already have value in deposit variable, check if current row deposit is empty, use Add Data row activity to add this row into a separate datatable.
    If the current row deposit have some value and not matching with outer deposit variable, replace deposit variable with current row deposit value and add data row to new datatable
4

Hi,

The following sample may help you.

image
image1314Ă—844 49.2 KB

Sample
Sample20250403-2.zip (10.2 KB)

Regards,

2 Likes
5

Thanks all but if after spliting the datatable where can i store them?

6

In my sample, they are stored in each sheet named content of deposit of result.xlsx.

7

@Yoichi can share screenshot as i m unable to unzip it

8

The screenshot is in my previous post.
I’ll also share original files.

Main.xaml (10.0 KB)
project.json (1.6 KB)
data.xlsx (9.5 KB)

Regards,

9

@yochi_ITD i tried yours but however i see the wholedatatable is being copied over. I wan the deposit id which contains 4 rows to be one dt and then follow by the rest.

10

@ashokkarale can give me a workflow.

11

@singh_sumit when u mention linq approach how to differentiate with the above. I prefer LINQ method.

12

Hey @mark_rajkumar1 sending you the zip file .
Group.zip (2.0 KB)

cheers

13

HI @singh_sumit i cannot extract zip file. Possible to screenshot workflow.

14

Hey @mark_rajkumar1
follow the screenshots.

image
image354Ă—118 4.21 KB

image
image563Ă—110 7.62 KB

The image shows a workflow process in UiPath where each row in the data table "dtUniqueData" is assigned the value from the "deposit" column to the variable "currentDeposit". (Captioned by AI)
The image shows a workflow process in UiPath where each row in the data table "dtUniqueData" is assigned the value from the "deposit" column to the variable "currentDeposit". (Captioned by AI)504Ă—254 9.96 KB

image
image560Ă—130 8.29 KB

A screenshot of UiPath's Expression Editor displaying the code snippet to filter a DataTable based on a specific deposit value. (Captioned by AI)
A screenshot of UiPath's Expression Editor displaying the code snippet to filter a DataTable based on a specific deposit value. (Captioned by AI)713Ă—180 13.8 KB

A workflow step labeled "Write Range Workbook" with parameters to create and save a filtered data set into an Excel file named dynamically using the variable "currentDeposit". (Captioned by AI)
A workflow step labeled "Write Range Workbook" with parameters to create and save a filtered data set into an Excel file named dynamically using the variable "currentDeposit". (Captioned by AI)536Ă—211 8.18 KB

Variables

A table displaying variable names, types, scopes, and default values in a programming interface. (Captioned by AI)
A table displaying variable names, types, scopes, and default values in a programming interface. (Captioned by AI)621Ă—249 9.44 KB

full workflow…

This image shows a flowchart of operations in a data processing script, including reading, assigning, looping through data rows, filtering, and writing data to a workbook. (Captioned by AI)
This image shows a flowchart of operations in a data processing script, including reading, assigning, looping through data rows, filtering, and writing data to a workbook. (Captioned by AI)605Ă—464 23.9 KB

if this resolve your query mark this as solution..
cheers

15

@singh_sumit it works but it copies only first row for that unique deposit but the rest of rows which is tag to that deposit is not being copied over. Help me with this can?

16

@mark_rajkumar1 basically this logic will split your data into the deposit basic like first it will take the 1 deposit and write into the excel and when 2 deposit row found it will separate it and write into the next excel.

17

@singh_sumit yes it works as what u explain. However i want for example the deposit from row 2 should take from 2 to 5 as dt and seperate and likewise for the rest too. How to achieve it?

18

Then go with @Yoichi San approach ..

19

Sending you a zip.
Group.xaml (14.0 KB)

cheers

20

Hi @singh_sumit this method read the whole table and port over. The deposit wuththe rows are not being grouped together individually.