Python : Split 1 Excel File into multiple Excel files by rows

Question

For example u have 1 excel file and it consist of 10000 data in it. Later when we import that excel file in pycharm or jupiter notebook. If i run that file i will get an Index range also know as Row labels. my python code should be able to read that ten thousand row labels and should be able to separate / split into 10 different excel sheet files which will have 1000 data in each of the 10 separated sheet. Other example is, if there is 9999 data in 1 sheet/file then my python code should divide 9000 data in 9 sheet and other 999 in other sheet without any mistakes.{This is important Question}

i am asking this because in my data there is not any unique values for my code to split the files using .unique

Answer 1

You could use Pandas to read your file, chunk it then re-write it :

import pandas as pd

df = pd.read_excel("/path/to/excels/file.xlsx")

n_partitions = 3

for i in range(n_partitions):
    sub_df = df.iloc[(i*n_paritions):((i+1)*n_paritions)]
    sub_df.to_excel(f"/output/path/to/test-{i}.xlsx", sheet_name="a")

EDIT: Or if you prefere to set the number of lines per xls files :

import pandas as pd

df = pd.read_excel("/path/to/excels/file.xlsx")

rows_per_file = 4

n_chunks = len(df) // rows_per_file

for i in range(n_chunks):
    start = i*rows_per_file
    stop = (i+1) * rows_per_file
    sub_df = df.iloc[start:stop]
    sub_df.to_excel(f"/output/path/to/test-{i}.xlsx", sheet_name="a")
if stop < len(df):
    sub_df = df.iloc[stop:]
    sub_df.to_excel(f"/output/path/to/test-{i}.xlsx", sheet_name="a")

You'll need openpyxl to read/write Excel files