Skip to main content
Solved

Breaking Excel Sheet into multiple files

  • 22 July 2024
  • 3 replies
  • 70 views

Hi everyone,

I currently have a plan that is creating a main task and populating it with sub tasks that each link to individual tables. My issue at the moment is that sometimes this creates tasks with a huge amount of sub tasks that may seem daunting for people to try to work through.

Currently the tables and their attribute ids are all stored in one excel sheet, so I was wondering if it would be possible to break it into separate excel files each containing 20 rows. If this is possible I would then be able to create additional tasks rather than just grouping everything in one.

Thank you for any help!

3 replies

P
Rank
Userlevel 2
Badge +1

Hi apejko,

based on what you described, I think we have a few options to try and see if they fit your scenario.

  1. Creating multiple Excel spreadsheets within one plan dynamically is not doable and you would need to “preallocate” the maximum/target number of spreadsheets you want to populate by creating the plan with the exact number of Excel File Writer steps and some Condition steps splitting/dividing your data flow into the individual Excel files (e.g. using modulo on the row number generated using the sequence(1,1) function or Sequence Generator step). You could also do some row grouping and split the rows based on the group identifiers (e.g. using record descriptor approach). Alternatively you can have a single Excel File Writer step with multiple inputs representing the individual sheets inside the one XLSx file and you’d map the divided data flows to the Excel writer step as individual sheets. See Tutorials project, file “02.03 Write data to excel.plan” in the ONE Desktop.
  2. If you don’t like the previous approach and want to dynamically create output files there is an option to use Text File Writer step to dynamically produce delimited files (e.g. CSV) that can be open in Excel too (no data formatting or templating is allowed since it would be a plain CSV). In this case you can use the dynamic file name creation with the column mask as your file name designator, e.g. “../data/out/02.03_delimited_output_{src_primary_key}.csv” to partition the files based on the “src_primary_key” column values.

Hope this helps you move forward.

ivan.kozlov
Rank
Userlevel 3
Badge +3

Hi @apejko , Not 100% sure if i understood your requirements correctly but I’ve created a plan that would take your excel file as input and will generate a new .csv file for each 20 rows on the list.
Text File Writer steps allow you to use variables in the step so you can have a unique name with a batch number as a part of name “out_{batch_num_string}.csv”.
Batch number would be calculated based on a row sequence number and then transformed to string that can be used as a part of output file name.
I hope this will be useful.
 


Ivan

1 Attachment
A
Rank
Userlevel 1

Hi @apejko , Not 100% sure if i understood your requirements correctly but I’ve created a plan that would take your excel file as input and will generate a new .csv file for each 20 rows on the list.
Text File Writer steps allow you to use variables in the step so you can have a unique name with a batch number as a part of name “out_{batch_num_string}.csv”.
Batch number would be calculated based on a row sequence number and then transformed to string that can be used as a part of output file name.
I hope this will be useful.
 


Ivan

This is exactly what I needed. Thank you very much!!!

Reply


Community Terms of Use & ConditionsCookie settings