Help needed in Excel Split with more than 2 Lakh records.

KNIME Analytics Platform
,
1

I’m working on a below scenario in knime. Need help either in form of workflow or python code.

  1. Read input excel file from src_folder flow variable.
  2. Read all the sheets (3 Visible and 2 hidden) available in the excel.
  3. Check if the excel file has more than 50000 records in sheet next to sheet named Definition Template.
  4. Create a copy of the excel to number of instances (Divide the total records row from source excel file by 50000 to take number of instances for splitting the file)
    by keeping all the active and hidden sheets as is and clear data only from sheet next to Definition Template from row 6 in new files.
  5. Copy the data from source excel to the new excel files each by 50000 rows from row 6 till it completes the number of instance need for split
  6. Once all the row data copied to destination excel files, delete the source file from split folder.
  7. Move the files to dest_folder flow variable.
2

Hi @AvnImpln,

To understand what you want to achieve, can you provide an example source file and the final output file with few dummy data.

Best,
Keerthan

1 Like
3

How about this one here?

First I list all files in the source folder,
then I loop over every file in the source,

  • reading all sheets and create a large table
  • split the data, as you don’t want to rearrange the first sheet
  • rearrange the remaining sheets with a given maximum number of rows
  • write all sheets in a file in the destination_folder

Hope this helps

2 Likes
4

@AvnImpln I tried to create a logic for this requirement, here i used 2 rows to split each instance instad of 50000 rows.

Hope this helps.

Best,
Keerthan

1 Like