Spliting the data within excel with regards policy number

Help Activities
, ,
1

Hello All,
I have an excel file with huge data, in that within column for each policy number there will be data around 500. So I need to separate that data to new excel file. Like for one policy number data in one excel file for another policy number data in another file. Please help me with this…

2

  1. Read the excel file using read range activity

  2. Group the datatable based on policy number

  3. Write each group into a seperate excel sheet

3

Hey @ISBS_ROBOT

  1. Loop through the DataTable: Use a “For Each Row” activity to iterate through each row of the DataTable.
  2. Check Policy Number: Inside the loop, use an “If” activity to check if the current row’s policy number matches the previously processed policy number. You’ll need to keep track of the last processed policy number to determine if you need to create a new file.
  3. Write to Excel File: If the policy number has changed or it’s the first row, create a new Excel file using the “Excel Application Scope” and the “Write Range” activity. Write the row’s data to the new Excel file.
  4. Repeat for Each Policy Number: Continue looping through the rows and creating new Excel files for each unique policy number.

Hope it helps you out

4

Hi @ISBS_ROBOT ,
you can try this step
-read original file to get data table
-base data table on policy number
-with each data table write range each file excel
you can share your file?
regards,
LNV

5

read excel output as Dt
write the query

DT.defaultview.ToTable(False,(“Policy Number”))

7

@ISBS_ROBOT

You can group the data like this

Dt.AsEnumerable.GroupBy(function(x) x("Policy Number").ToString).ToDictionary(function(x) x.Key,function(x) x.CopyToDataTable)

This can be used in in argument of for loop

And inside the loop currentitem.key will give you the policy number can be used for filename

And currentItem.Value will give the related data which can be used in write range datatable field

Hope this helps

Cheers

8

Hey ! I have attached the workflow here ,Hope it might help You.
Create Excel.xaml (10.8 KB)

2 Likes
9

for this variable type is Dictionary(String, datatable). How to convert it into datatable

10

There are 63 columns I gave in build data table and add data row. But here I’m getting an error like column does not belongs to data table

11

@ISBS_ROBOT

This dictionary will contain all the datatbles

So you can loop through keys Dictionary.keys will give you array of all the Policy numbers which are unique

use in the loop and inside loop dict(currentItem) will give each datatble separately

Or as mentioned earlier…use .ToArray and loop through each dictionary item currentitem.value and currentitem.datatable

cheers

12

Make sure that the column name that you write in add data row should be similar to the column name that is present in your excel

1 Like
13

For Example:

image
image

1 Like
14

what if there are large number of columns, for example 45 columns. How to include them?

15

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