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…
Read the excel file using read range activity
Group the datatable based on policy number
Write each group into a seperate excel sheet
- Loop through the DataTable: Use a “For Each Row” activity to iterate through each row of the DataTable.
- 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.
- 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.
- 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
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?
read excel output as Dt
write the query
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
Hey ! I have attached the workflow here ,Hope it might help You.
Create Excel.xaml (10.8 KB)
for this variable type is Dictionary(String, datatable). How to convert it into datatable
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
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
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
what if there are large number of columns, for example 45 columns. How to include them?
This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.