Split excel sheet into multiple excel files using column filter

[jaffer_sadiq]

Hi Everyone,

Am having a concern here, I would like to split the below excel sheet into multiple excel files using a column filter as per (Supplier Code).

Ex- Required Separate files based on the supplier code.

The Excel format will be the same and rows 1 & 2 required on all the files.

Could you please anyone guide me to handle this task?

[jack.chan]

@jaffer_sadiq can you upload the excel here

[Rounak_Kumar1 (Rounak Kumar)]

Hey,

you can do
1.Read Range Activity
2.For Each row Activity- Iteration row by row
3.In For EAch row Create Build Data Table- With Same Headers which is mentioned in your excel Sheet
4. Store all the values In Assign Activities
like
ItemNumber = CurrentRow(“Item Number”).toString
QuantityOrdered=CurrentRow(“Quantity Ordered”).toString
You should Store all the Values so on…

then use
Add data row
in Arrayrow={ItemNumber,QuantityOrdered,…}
then
Use Write Range Activity with DataTAble which you Got the output of Build Data table
You can save the excel file with the Supplier code

Thanks

[jaffer_sadiq]

Dear Jack,
PFA

Blanket Order.xlsx (26.0 KB)

Thanks

[jaffer_sadiq]

Dear Rounak,

Thanks, Let me try and update you.

Regards,
Jaffer

[jack.chan]

use this xaml @jaffer_sadiq
TEST.xaml (12.9 KB)

result - CREATE 1 FILE PER SUPPLIER CODE

Example Blanket Order-300455.xlsx

logic:

1. get unique supplier codes
2. for each code,
   a) copy Blanket Order.xlsx to Blanket Order-{code}.xlsx
   b) read table, filter supplier code = code, and write range using filtered table

[jaffer_sadiq]

Hi Jack,

Thank you so much. am facing an issue while open the xaml.

[jack.chan]

Your package version issue. Can you send your project as a .zip file. Ill use your package version and send new version in an hour. Am busy now

[jaffer_sadiq]

Hi Jack,

Please find the below project.

CS_Project.zip (4.8 MB)

Please Note: I may required these process should done before “Blanket Order Creation”

Thanks for your support.

Regards,
Jaffer

[jack.chan]

here @jaffer_sadiq
CS_Project_2.zip (4.8 MB)
i added my workflow here as blanket_order_creation,

you can drag it to your main workflow, but you should test it by itself first

you might have to change the filepath here as well

[jaffer_sadiq]

Ok Jack, Noted. Let me check and confirm you.

[jaffer_sadiq]

Hi Jack,

files are not splitting as expected. all the files data’s are the same size. attached sample result for your review.
Blanket Order-300201.xlsx (25.8 KB)
Blanket Order-300169.xlsx (25.9 KB)

Could you please check?

Regards,
Jaffer

[jack.chan]

use this, it will work. @jaffer_sadiq
remember to change the filename again if needed
blacket_Order_Creation.xaml (13.9 KB)

[jaffer_sadiq]

Dear Jack,

Thanks for your great help.

Regards,
Jaffer